Re: [HACKERS] pg_dump roles support
=?ISO-8859-1?Q?Benedek_L=E1szl=F3?= l...@benedekl.tvnetwork.hu writes: Here is an updated patch, which deals with 's in the rolename. Committed with revisions as per subsequent discussion: pg_restore has its own switch and there's no change in archive contents. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] lazy_truncate_heap()
Logically, xmin horizon conflicts could be flexible/soft. That is, if we implemented the idea to store a lastCleanedLSN for each buffer then xmin horizon conflicts would be able to continue executing until they see a buffer with buffer.lastCleanedLSN conflictLSN. I think the trouble is, that HOT can put extremely recent lastCleanedLSN's on pages. It would need some knobs to avoid this, that most likely reduce efficiency of HOT. What about using the page LSN after max_standby_delay ? Using the page LSN cancels queries earlier than the lastCleanedLSN, but probably in many cases later than an immediate cancel after max_standby_delay. Of course that only helps when reading static parts of tables :-( Instead of a cancel message, the replay would need to send (set in shmem) the first LSN applied after max_standby_delay to the relevant backend for it's LSN checks (if buffer.LSN = received_max_delay_lsn cancel). Andreas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] QuickLZ compression algorithm (Re: Inclusion in the PostgreSQL backend for toasting rows)
On Mon, Jan 5, 2009 at 3:18 AM, Stephen R. van den Berg s...@cuci.nl wrote: I'm not speaking for Lasse, merely providing food for thought, but it sounds feasible to me (and conforming to Lasse's spirit of his intended license) to put something like the following license on his code, which would allow inclusion into the PostgreSQL codebase and not restrict usage in any of the derived works: Grant license to use the code in question without cost, provided that the code is being linked to at least 50% of the PostgreSQL code it is being distributed alongside with. This should allow commercial reuse in derived products without undesirable sideeffects. I think Postgres becomes non-DFSG-free if this is done. All of a sudden one can't pull arbitrary pieces of code out of PG and use them in other projects (which I'd argue is the intent if not the letter of the DFSG). Have we ever allowed code in on these terms before? Are we willing to be dropped from Debian and possibly Red Hat if this is the case? -Doug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] QuickLZ compression algorithm (Re: Inclusion in the PostgreSQL backend for toasting rows)
Douglas McNaught wrote: On Mon, Jan 5, 2009 at 3:18 AM, Stephen R. van den Berg s...@cuci.nl wrote: I'm not speaking for Lasse, merely providing food for thought, but it sounds feasible to me (and conforming to Lasse's spirit of his intended license) to put something like the following license on his code, which would allow inclusion into the PostgreSQL codebase and not restrict usage in any of the derived works: Grant license to use the code in question without cost, provided that the code is being linked to at least 50% of the PostgreSQL code it is being distributed alongside with. This should allow commercial reuse in derived products without undesirable sideeffects. I think Postgres becomes non-DFSG-free if this is done. All of a sudden one can't pull arbitrary pieces of code out of PG and use them in other projects (which I'd argue is the intent if not the letter of the DFSG). Have we ever allowed code in on these terms before? Are we willing to be dropped from Debian and possibly Red Hat if this is the case? Presumably a clean room implementation of this algorithm would get us over these hurdles, if anyone wants to undertake it. I certainly agree that we don't want arbitrary bits of our code to be encumbered or licensed differently from the rest. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] QuickLZ compression algorithm (Re: Inclusion in the PostgreSQL backend for toasting rows)
Andrew Dunstan wrote: Douglas McNaught wrote: On Mon, Jan 5, 2009 at 3:18 AM, Stephen R. van den Berg s...@cuci.nl wrote: I'm not speaking for Lasse, merely providing food for thought, but it sounds feasible to me (and conforming to Lasse's spirit of his intended license) to put something like the following license on his code, which would allow inclusion into the PostgreSQL codebase and not restrict usage in any of the derived works: Grant license to use the code in question without cost, provided that the code is being linked to at least 50% of the PostgreSQL code it is being distributed alongside with. This should allow commercial reuse in derived products without undesirable sideeffects. I think Postgres becomes non-DFSG-free if this is done. All of a sudden one can't pull arbitrary pieces of code out of PG and use them in other projects (which I'd argue is the intent if not the letter of the DFSG). Have we ever allowed code in on these terms before? Are we willing to be dropped from Debian and possibly Red Hat if this is the case? Presumably a clean room implementation of this algorithm would get us over these hurdles, if anyone wants to undertake it. I certainly agree that we don't want arbitrary bits of our code to be encumbered or licensed differently from the rest. do we actually have any numbers that quicklz is actually faster and/or compresses better than what we have now? Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Significantly larger toast tables on 8.4?
Peter Eisentraut escribió: James Mansion wrote: Peter Eisentraut wrote: c. Are there any well-known pitfalls/objections which would prevent me from changing the algorithm to something more efficient (read: IO-bound)? copyright licenses and patents Would it be possible to have a plugin facility? Well, before we consider that, we'd probably want to see proof about the effectiveness of other compression methods. I did some measurements months ago, and it was very clear that libz compression was a lot tighter than the PGLZ code. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] version() output vs. 32/64 bits
Zdenek Kotala wrote: Alvaro Herrera p??e v st 31. 12. 2008 v 12:54 -0300: Maybe we could have a separate function which returned the info in various columns (OUT params). Maybe it would be useful to normalize the info as reported the buildfarm, which right now is a bit ad-hoc. +1 it sounds good. So what do we want to do for 8.4? Add 32/64-bit version() indicator and add OUT parameters to the TODO list? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] log output of vxid
Hi, Attached is a simple patch to unify the log output of 'vxid' between csvlog and stderr/syslog. Currently, in csvlog, vxid of an auxiliary process isn't displayed. On the other hand, in stderr/syslog, invalid vxid (-1/0) of that is displayed. I'm not sure why we need to change a display of vxid according to log_destination. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center ? GNUmakefile ? config.log ? config.status ? contrib/pgbench/pgbench ? src/Makefile.global ? src/backend/postgres ? src/backend/catalog/postgres.bki ? src/backend/catalog/postgres.description ? src/backend/catalog/postgres.shdescription ? src/backend/postmaster/walreceiver.c ? src/backend/postmaster/walsender.c ? src/backend/snowball/snowball_create.sql ? src/backend/utils/probes.h ? src/backend/utils/mb/conversion_procs/conversion_create.sql ? src/bin/initdb/initdb ? src/bin/pg_config/pg_config ? src/bin/pg_controldata/pg_controldata ? src/bin/pg_ctl/pg_ctl ? src/bin/pg_dump/pg_dump ? src/bin/pg_dump/pg_dumpall ? src/bin/pg_dump/pg_restore ? src/bin/pg_resetxlog/pg_resetxlog ? src/bin/psql/psql ? src/bin/scripts/clusterdb ? src/bin/scripts/createdb ? src/bin/scripts/createlang ? src/bin/scripts/createuser ? src/bin/scripts/dropdb ? src/bin/scripts/droplang ? src/bin/scripts/dropuser ? src/bin/scripts/reindexdb ? src/bin/scripts/vacuumdb ? src/include/pg_config.h ? src/include/stamp-h ? src/include/postmaster/walreceiver.h ? src/include/postmaster/walsender.h ? src/interfaces/ecpg/compatlib/exports.list ? src/interfaces/ecpg/compatlib/libecpg_compat.so.3.1 ? src/interfaces/ecpg/ecpglib/exports.list ? src/interfaces/ecpg/ecpglib/libecpg.so.6.1 ? src/interfaces/ecpg/include/ecpg_config.h ? src/interfaces/ecpg/pgtypeslib/exports.list ? src/interfaces/ecpg/pgtypeslib/libpgtypes.so.3.1 ? src/interfaces/ecpg/preproc/ecpg ? src/interfaces/libpq/exports.list ? src/interfaces/libpq/libpq.so.5.2 ? src/port/pg_config_paths.h ? src/test/regress/log ? src/test/regress/pg_regress ? src/test/regress/results ? src/test/regress/testtablespace ? src/test/regress/tmp_check ? src/test/regress/expected/constraints.out ? src/test/regress/expected/copy.out ? src/test/regress/expected/create_function_1.out ? src/test/regress/expected/create_function_2.out ? src/test/regress/expected/largeobject.out ? src/test/regress/expected/largeobject_1.out ? src/test/regress/expected/misc.out ? src/test/regress/expected/tablespace.out ? src/test/regress/sql/constraints.sql ? src/test/regress/sql/copy.sql ? src/test/regress/sql/create_function_1.sql ? src/test/regress/sql/create_function_2.sql ? src/test/regress/sql/largeobject.sql ? src/test/regress/sql/misc.sql ? src/test/regress/sql/tablespace.sql ? src/timezone/zic Index: src/backend/utils/error/elog.c === RCS file: /projects/cvsroot/pgsql/src/backend/utils/error/elog.c,v retrieving revision 1.210 diff -c -r1.210 elog.c *** src/backend/utils/error/elog.c 1 Jan 2009 17:23:51 - 1.210 --- src/backend/utils/error/elog.c 5 Jan 2009 07:48:43 - *** *** 1693,1699 break; case 'v': /* keep VXID format in sync with lockfuncs.c */ ! if (MyProc != NULL) appendStringInfo(buf, %d/%u, MyProc-backendId, MyProc-lxid); break; --- 1693,1699 break; case 'v': /* keep VXID format in sync with lockfuncs.c */ ! if (MyProc != NULL MyProc-backendId != InvalidBackendId) appendStringInfo(buf, %d/%u, MyProc-backendId, MyProc-lxid); break; -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] QuickLZ compression algorithm (Re: Inclusion in the PostgreSQL backend for toasting rows)
Are we willing to be dropped from Debian and possibly Red Hat if this is the case? No. I frankly think this discussion is a dead end. The whole thing got started because Alex Hunsaker pointed out that his database got a lot bigger because we disabled compression on columns 1MB. It seems like the obvious thing to do is turn it back on again. The only objection to that is that it will hurt performance, especially on substring operations. That lead to a discussion of alternative compression algorithms, which is only relevant if we believe that there are people out there who want to do substring extractions on huge columns AND want those columns to be compressed. At least on this thread, we have zero requests for that feature combination. What we do have is a suggestion from several people that the database shouldn't be in the business of compressing data AT ALL. If we want to implement that suggestion, then we could change the default column storage type. Regardless of whether we do that or not, no one has offered any justification of the arbitrary decision not to compress columns 1MB, and at least one person (Peter) has suggested that it is exactly backwards. I think he's right, and this part should be backed out. That will leave us back in the sensible place where people who want compression can get it (which is currently false) and people who don't want it can get rid of it (which has always been true). If there is a demand for alternative compression algorithms, then someone can submit a patch for that for 8.5. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Updates of SE-PostgreSQL 8.4devel patches (r1386)
On Mon, Jan 5, 2009 at 4:44 AM, KaiGai Kohei kai...@ak.jp.nec.com wrote: BTW, what is the currect status of my patches? I guess you have many comments in the five patches, because they are a bit large to be commited obviously. Could you tell me, even if these are not comprehensive ones. I haven't looked at the patches, but one thing I'm concerned about is the fact that it seems we still don't have a working implementation of non-SEPostgresql column-level privileges. Apparently, the latest patch set from Stephen Frost doesn't handle those permissions when joins are involved, which presumably means that it is not committable. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] version() output vs. 32/64 bits
Bruce Momjian br...@momjian.us writes: So what do we want to do for 8.4? Add 32/64-bit version() indicator and add OUT parameters to the TODO list? +1. There seems a good case for making the 32/64bit distinction visible somewhere, and the text version string is as good as anyplace. I think the business about splitting up the string is mostly lily-gilding, but if someone wants it enough to do it ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED compatible connection manager
Martin Pihlak wrote: I would call it something like pg_postgresql_fdw_options_string(server, user) returns text Hmm, it is probably a good idea to avoid the fdw abbreviation -- the term foreign data wrapper is already confusing enough. My suggestion: pg_foreign_server_conninfo(server) pg_foreign_server_conninfo(server,user) If there are no objections, I'll whack those functions out, and bring the dblink patch up to date. Sure, propose some code. (Note that you can use parameter default values now.) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Function with defval returns wrong result
Hi All, Following test returns wrong result .. Testcase ( on 8.4 cvs head ) === CREATE OR REPLACE FUNCTION f1(retval VARCHAR DEFAULT 'Argument') RETURNS VARCHAR as $$ BEGIN return retval; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION f2(p1 IN int, p2 IN VARCHAR DEFAULT f1()) RETURNS VARCHAR AS $$ BEGIN RETURN p2; END; $$ LANGUAGE plpgsql; postgres=# select f2(10); f2 (1 row) When we run the f2() it should return the output as the defvalue of f1() function, but its returning null. Thanks, Rushabh Lathia www.EnterpriseDB.com
Re: [HACKERS] QuickLZ compression algorithm (Re: Inclusion in the PostgreSQL backend for toasting rows)
Douglas McNaught wrote: Grant license to use the code in question without cost, provided that the code is being linked to at least 50% of the PostgreSQL code it is being distributed alongside with. This should allow commercial reuse in derived products without undesirable sideeffects. I think Postgres becomes non-DFSG-free if this is done. All of a sudden one can't pull arbitrary pieces of code out of PG and use them in other projects (which I'd argue is the intent if not the letter of the DFSG). Have we ever allowed code in on these terms before? Are we willing to be dropped from Debian and possibly Red Hat if this is the case? Upon reading the DFSG, it seems you have a point... However... QuickLZ is dual licensed: a. Royalty-free-perpetuous-use as part of the PostgreSQL backend or any derived works of PostgreSQL which link in *at least* 50% of the original PostgreSQL codebase. b. GPL if a) does not apply for some reason. I.e. for all intents and purposes, it fits the bill for both: 1. PostgreSQL-derived products (existing and future). 2. Debian/RedHat, since the source can be used under GPL. In essence, it would be kind of a GPL license on steroids; it grants Berkeley-style rights as long as the source is part of PostgreSQL (or a derived work thereof), and it falls back to GPL if extracted. -- Sincerely, Stephen R. van den Berg. Well, if we're going to make a party of it, let's nibble Nobby's nuts! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Time to finalize patches for 8.4 beta
On Sat, 2009-01-03 at 22:34 -0500, Bruce Momjian wrote: o Recovery, Replication, Hot Standby: We need a _final_ version of any patches that are targeted for 8.4. There is so much activity in this area I am unclear what is ready for 8.4. What do we mean by _final_? This a serious, polite question. Hot Standby hasn't had any serious code review yet by anybody, AFAIK. Hot Standby is not a WIP patch. 96-8% of the patch is identical to the way it was on Nov 1: ready for some review comments and testing/usability feedback. The later a review happens the less time we have to change anything within the patch in response to comments. If anybody is deliberately holding back on review, they need to talk to me, directly, soon. The main problem seems to be that all qualified developers are allocated elsewhere, or working on their own patches. If we want this in 8.4, then other developers or companies need to allocate some time to assist with its inclusion. All help greatly appreciated. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] QuickLZ compression algorithm (Re: Inclusion in the PostgreSQL backend for toasting rows)
Douglas McNaught d...@mcnaught.org writes: I think Postgres becomes non-DFSG-free if this is done. All of a sudden one can't pull arbitrary pieces of code out of PG and use them in other projects (which I'd argue is the intent if not the letter of the DFSG). Have we ever allowed code in on these terms before? No, and we aren't starting now. Any submission that's not BSD-equivalent license will be rejected. Count on it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] lazy_truncate_heap()
On Mon, 2009-01-05 at 18:24 +0100, Zeugswetter Andreas OSB sIT wrote: Logically, xmin horizon conflicts could be flexible/soft. That is, if we implemented the idea to store a lastCleanedLSN for each buffer then xmin horizon conflicts would be able to continue executing until they see a buffer with buffer.lastCleanedLSN conflictLSN. I think the trouble is, that HOT can put extremely recent lastCleanedLSN's on pages. It would need some knobs to avoid this, that most likely reduce efficiency of HOT. What about using the page LSN after max_standby_delay ? Using the page LSN cancels queries earlier than the lastCleanedLSN, but probably in many cases later than an immediate cancel after max_standby_delay. Of course that only helps when reading static parts of tables :-( Instead of a cancel message, the replay would need to send (set in shmem) the first LSN applied after max_standby_delay to the relevant backend for it's LSN checks (if buffer.LSN = received_max_delay_lsn cancel). I like your train of thought there: If HOT is the problem then lastCleanedLSN approx= LSN on HOT blocks, so having lastCleanedLSN doesn't help much. OK, so I'll skip that idea and go with what you suggest. Design: When conflict occurs we set a RecoveryConflictLSN on the Proc of the backend to be cancelled. Every time we read a block in recovery we check MyProc.RecoveryConflictLSN against the LSN on the block and backend will commit suicide (ERROR) if block LSN is equal or greater. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Many loaded library logs by preload libraries
Tom Lane t...@sss.pgh.pa.us wrote:
ITAGAKI Takahiro itagaki.takah...@oss.ntt.co.jp writes:
If we set shared_preload_libraries or local_preload_libraries to
load some modules, loaded library messages are logged in server
log every new connections and autovacuum workers.
Yeah, I was noticing that myself while testing pg_stat_statements.
I agree that we should fix it to reduce the message level for reloads
occurring in child processes. I'd suggest using DEBUG2 if
(IsUnderPostmaster process_shared_preload_libraries_in_progress).
I'm not so enthused about eliminating messaging for
local_preload_libraries, though.
Here is a patch to do it.
Index: src/backend/utils/init/miscinit.c
===
--- src/backend/utils/init/miscinit.c (HEAD)
+++ src/backend/utils/init/miscinit.c (fixed)
@@ -1146,6 +1146,7 @@
char *rawstring;
List *elemlist;
ListCell *l;
+ int elevel;
if (libraries == NULL || libraries[0] == '\0')
return; /* nothing to do */
@@ -1166,6 +1167,11 @@
return;
}
+ if (IsUnderPostmaster process_shared_preload_libraries_in_progress)
+ elevel = DEBUG2;
+ else
+ elevel = LOG;
+
foreach(l, elemlist)
{
char *tok = (char *) lfirst(l);
@@ -1185,7 +1191,7 @@
filename = expanded;
}
load_file(filename, restricted);
- ereport(LOG,
+ ereport(elevel,
(errmsg(loaded library \%s\, filename)));
pfree(filename);
}
Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] dblink vs SQL/MED - security and implementation details
Joe Conway wrote: I'm mainly concerned about re-opening security holes that we spent a lot of time debating and subsequently closing. I suspect if we assume that any FDW-derived connect string can bypass the checks we put in place, we will regret it later. But I'm open to arguments on both sides... Can you elaborate what security issues you are concerned about? It seems to me that get_connect_string() (or whatever we decide to call it), is very libpq specific, and therefore belongs with postgresql_fdw.c rather than foreign.c. But if we can't reach a consensus there is no harm in leaving it as a dblink.c specific static function either. postgresql_fdw.c is a module with a defined API. I don't see what you would achieve by putting an ad hoc function in there. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] generic reloptions improvement
KaiGai Kohei wrote: If the caller allocates a surplus area to store string on tail of the StdRdOptions (or others), the string option can be represented as an offset value and should be accessed via macros, like: Excellent. I thought about storing an offset but I didn't know how to make it work for accessors -- I was missing the macro idea :-) Thanks, I'll see about this. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] QuickLZ compression algorithm (Re: Inclusion in the PostgreSQL backend for toasting rows)
Robert Haas wrote: What we do have is a suggestion from several people that the database shouldn't be in the business of compressing data AT ALL. If we want +1 IMHO, this is a job for the application. I also think the current implementation is a little odd in that it only compresses data objects under a meg. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] an idea, language SPI
Hello, I am thinking about reimplementation PL/pgPSM, where code should be shared with PL/pgSQL. I have idea of some middle language, that should be used for both languages. This language could be based on SPI interface with some procedural elements (if, jmp, return). sample create or replace function sum2(int, int) returns int as $$ spi_connect() qry = spi_prepare(select $1 + $2, 2, [23,23]) spi_execute_plan(qry, [$1,$2],N[$1,$2], true, 1) RETURN sp_result[0,0] spi_finish() EXIT $$ language spi; what do you thing about it? regards Pavel Stehule -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Updates of SE-PostgreSQL 8.4devel patches (r1386)
I updates patch set of SE-PostgreSQL and related stuff (r1386). [1/5] http://sepgsql.googlecode.com/files/sepostgresql-sepgsql-8.4devel-3-r1386.patch [2/5] http://sepgsql.googlecode.com/files/sepostgresql-utils-8.4devel-3-r1386.patch [3/5] http://sepgsql.googlecode.com/files/sepostgresql-policy-8.4devel-3-r1386.patch [4/5] http://sepgsql.googlecode.com/files/sepostgresql-docs-8.4devel-3-r1386.patch [5/5] http://sepgsql.googlecode.com/files/sepostgresql-tests-8.4devel-3-r1386.patch List of updates: - The patches are rebased to the latest CVS HEAD. (Previous one (r1368) conflicts to newly added features.) - The default_row_acl reloption is removed temporary, because Alvaro Herrera is now under reworking to generic reloptions framework. - Copyrights are updated from 2008 to 2009. Most of them are unchanged. Please don't mind it, if you are under reviewing at r1368. BTW, what is the currect status of my patches? I guess you have many comments in the five patches, because they are a bit large to be commited obviously. Could you tell me, even if these are not comprehensive ones. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] QuickLZ compression algorithm (Re: Inclusion in the PostgreSQL backend for toasting rows)
On Jan 5, 2009, at 1:16 PM, Stephen R. van den Berg wrote: Upon reading the DFSG, it seems you have a point... However... QuickLZ is dual licensed: a. Royalty-free-perpetuous-use as part of the PostgreSQL backend or any derived works of PostgreSQL which link in *at least* 50% of the original PostgreSQL codebase. How does one even define 50% of the original PostgreSQL codebase? What nonsense. -M -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] QuickLZ compression algorithm (Re: Inclusion in the PostgreSQL backend for toasting rows)
Robert Haas robertmh...@gmail.com writes: Regardless of whether we do that or not, no one has offered any justification of the arbitrary decision not to compress columns 1MB, Er, yes, there was discussion before the change, for instance: http://archives.postgresql.org/pgsql-hackers/2007-08/msg00082.php And do you have any response to this point? I think the right value for this setting is going to depend on the environment. If the system is starved for cpu cycles then you won't want to compress large data. If it's starved for i/o bandwidth but has spare cpu cycles then you will. http://archives.postgresql.org/pgsql-hackers/2009-01/msg00074.php and at least one person (Peter) has suggested that it is exactly backwards. I think he's right, and this part should be backed out. Well the original code had a threshold above which we *always* compresed even if it saved only a single byte. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] QuickLZ compression algorithm (Re: Inclusion in the PostgreSQL backend for toasting rows)
Robert Haas robertmh...@gmail.com writes: The whole thing got started because Alex Hunsaker pointed out that his database got a lot bigger because we disabled compression on columns 1MB. It seems like the obvious thing to do is turn it back on again. I suggest that before we make any knee-jerk responses, we need to go back and reread the prior discussion. The current 8.4 code was proposed here: http://archives.postgresql.org/pgsql-patches/2008-02/msg00053.php and that message links to several older threads that were complaining about the 8.3 behavior. In particular the notion of an upper limit on what we should attempt to compress was discussed in this thread: http://archives.postgresql.org/pgsql-general/2007-08/msg01129.php After poking around in those threads a bit, I think that the current threshold of 1MB was something I just made up on the fly (I did note that it needed tuning...). Perhaps something like 10MB would be a better default. Another possibility is to have different minimum compression rates for small and large datums. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [SPAM] Re: [HACKERS] posix_fadvise v22
Gregory Stark wrote: Peter Eisentraut pete...@gmx.net writes: On Friday 02 January 2009 06:49:57 Greg Stark wrote: The guc run-time check is checking for known-buggy versions of glibc using sysconf to check what version of glibc you have. Could you please mention the bug number in the relevant source code comments? It's Debian bug# 312406 which was fixed in Debian release 2.3.5-3. So it's probably one of these but searching for posix_fadvise doesn't find anything in their bug tracker: The way I read this is that this was a temporary kernel/libc mismatch in a development version of Debian 3 years ago that was fixed within 2 months of being reported and was never released to the general public. So it would be on the same level as any of a million temporary breakages in Linux distributions under development. Unless there are other reports of this problem, I wouldn't bother testing or working around this at all. If people are running PostgreSQL 8.4+ on Debian unstable June 2005 with kernel 2.4, they cannot be helped. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] EmitWarningsOnPlaceholders is too quiet
Tom Lane wrote: I notice that EmitWarningsOnPlaceholders produces its warning messages at elog level INFO, which makes it nearly useless for bogus custom variable settings for a preloaded module: the only place such a module can warn is in the postmaster log, but INFO is too low to go into the postmaster log by default. Perhaps we should use WARNING instead? (I'm actually kind of wondering what the INFO elog level is good for, as this is just about the only use and it seems wrong.) +1 for one less logging level. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] contrib/pg_stat_statements 1226
Tom Lane t...@sss.pgh.pa.us writes: 2. I'm unconvinced by the proposed changes to accumulate backend-local I/O counters, too. The fact of the matter is that those counters are left over from Berkeley days, a time when PG hackers tended to do their performance measurements in standalone backends (!). They're obviously not the full story now on write measurements, and I don't have any confidence in them as read measurements either, particularly seeing that the wave of the future is likely to be asynchronous read operations (with the posix_fadvise patch and foreseeable follow-on work). I think those counters should more likely be done away with than institutionalized in EXPLAIN ANALYZE output. You can get more reliable information about what's happening from the existing pgstats system-wide I/O counts. It's clear that these counters are not the whole picture. But I think that just means we need more counters with more sources of data, not that we need to get rid of the ones we have. There's no reason we shouldn't have counters for advised buffers along with reads. I'm also picturing using dtrace to find out how many reads were satisfied from cache and how many required physical reads for example. The system-wide stats satisfy a very different need from this. The sysadmin or DBA might want to know about system-wide stats but a programmer or a DBA analyzing a specific query needs to know what that query is doing. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] dblink vs SQL/MED - security and implementation details
Joe Conway wrote: I'm mainly concerned about re-opening security holes that we spent a lot of time debating and subsequently closing. I suspect if we assume that any FDW-derived connect string can bypass the checks we put in place, we will regret it later. But I'm open to arguments on both sides... In order to create a foreign server, the user needs USAGE on the foreign data wrapper. Creating user mappings requires the user to be the owner of the server. Both need explicit grants or alters by the superuser. This is a bit more relaxed than the current superuser check, but still only trusted users can define arbitrary connections. Also, allowing passwordless user mappings adds some flexibility for defining connections - storing passwords in .pgpass, pgservice or not using a password at all (pg_hba trust etc.). regards, Martin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] version() output vs. 32/64 bits
Alvaro Herrera píše v st 31. 12. 2008 v 12:54 -0300: Maybe we could have a separate function which returned the info in various columns (OUT params). Maybe it would be useful to normalize the info as reported the buildfarm, which right now is a bit ad-hoc. +1 it sounds good. Zdenek -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Filtering WAL records in recovery
There's a long standing requirement for filtering WAL records during recovery, allowing us to select databases, tables etc. My approach to this has been to submit a patch for rmgr plugins. The patch has other good uses too, most especially being able to decode WAL records by having access to a continuously maintained catalog. That patch may not have been understood by many and a recent private conversation indicated it may be vetoed, though not really sure why. If we are going to reject the patch on rmgr plugins, then I'd like to get that overwith as quickly as possible so I can add other code to allow filtering for Hot Standby by some other route for 8.4. Thanks, -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Updates of SE-PostgreSQL 8.4devel patches (r1386)
Robert Haas robertmh...@gmail.com writes: I haven't looked at the patches, but one thing I'm concerned about is the fact that it seems we still don't have a working implementation of non-SEPostgresql column-level privileges. Apparently, the latest patch set from Stephen Frost doesn't handle those permissions when joins are involved, which presumably means that it is not committable. No, but it might be fixable. I plan to look at that patch next after I have my immediate to-do items cleared off ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] EmitWarningsOnPlaceholders is too quiet
Bruce Momjian br...@momjian.us writes: Tom Lane wrote: (I'm actually kind of wondering what the INFO elog level is good for, as this is just about the only use and it seems wrong.) +1 for one less logging level. I rememebered what INFO is for: it's the elevel that VACUUM VERBOSE uses to ensure that its output gets seen at the client. (I missed that in my first grep because vacuum's elog/ereport calls don't use INFO as a hard-wired elevel.) So we probably can't get rid of it. But that makes it even more clear IMHO that EmitWarningsOnPlaceholders shouldn't be using it. It's not intended for error/warning types of uses, but for verbose logging. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Several tags around PostgreSQL 7.1 broken
* Peter Eisentraut pete...@gmx.net [090102 18:33]: On Friday 02 January 2009 23:33:34 Aidan Van Dyk wrote: Has this gone anywhere? Is the CVS repo safe yet? Nothing has been done about this. So, what's the concensus. Are we(you,everybody?) going to leave the CVS repo alone, or is someone going to muck around in it?If someone's planning to muck around in it, are we all going to get a day or two of warning? a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] Significantly larger toast tables on 8.4?
James Mansion wrote: Peter Eisentraut wrote: c. Are there any well-known pitfalls/objections which would prevent me from changing the algorithm to something more efficient (read: IO-bound)? copyright licenses and patents Would it be possible to have a plugin facility? Well, before we consider that, we'd probably want to see proof about the effectiveness of other compression methods. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Updates of SE-PostgreSQL 8.4devel patches (r1386)
Robert Haas wrote: On Mon, Jan 5, 2009 at 4:44 AM, KaiGai Kohei kai...@ak.jp.nec.com wrote: BTW, what is the currect status of my patches? I guess you have many comments in the five patches, because they are a bit large to be commited obviously. Could you tell me, even if these are not comprehensive ones. I haven't looked at the patches, but one thing I'm concerned about is the fact that it seems we still don't have a working implementation of non-SEPostgresql column-level privileges. Apparently, the latest patch set from Stephen Frost doesn't handle those permissions when joins are involved, which presumably means that it is not committable. Yes, this certainly is going to be an issue. The SE-PostgreSQL patch gives us SQL and SE-level row permissions, but only SE-level column permissions (because SQL-level is coming from another patch). -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Significantly larger toast tables on 8.4?
On Sat, Jan 3, 2009 at 1:32 AM, Alex Hunsaker bada...@gmail.com wrote: On Fri, Jan 2, 2009 at 18:46, Tom Lane t...@sss.pgh.pa.us wrote: It would be fairly easy, I think, to add some reloption fields that would let these parameters be controlled on a per-table level. +1 Or something easier that just lets you use PGLZ_Strategy strategy_always_data. ALTER TABLE SET STORAGE ALWAYS_COMPRESS; Or is that way to stop gap? Per-column would be much more painful; do we really need that? Personally, I think the whole built-in compressor framework should be deprecated (it's confusing and unintuitive) and replaced with a pluggable compression family in the vein of pg_crypto. This solves a number of issues simultaneously...we no longer have to 'guess' if the user wants a bytea compressed or not, everything is controlled from the client, and we can make it easier for users to use non license compatible libraries (like lzo) in a standard way. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] EmitWarningsOnPlaceholders is too quiet
I notice that EmitWarningsOnPlaceholders produces its warning messages at elog level INFO, which makes it nearly useless for bogus custom variable settings for a preloaded module: the only place such a module can warn is in the postmaster log, but INFO is too low to go into the postmaster log by default. Perhaps we should use WARNING instead? (I'm actually kind of wondering what the INFO elog level is good for, as this is just about the only use and it seems wrong.) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] version() output vs. 32/64 bits
Bruce Momjian píše v st 31. 12. 2008 v 11:22 -0500: Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: On Wednesday 31 December 2008 04:45:01 Bruce Momjian wrote: PostgreSQL 8.4devel on i386-pc-bsdi4.3.1, compiled by GCC 2.95.3, 32-bit Maybe we should separate all that, e.g., SELECT version(); = 'PostgreSQL 8.4devel' SELECT pg_host_os(); = 'bsdi4.3.1' SELECT pg_host_cpu(); = 'i386' (although this is still faulty, as per my original argument; needs some thought) SELECT pg_compiler(); = 'GCC 2.95.3' SELECT pg_pointer_size(); = 4 (or 32) (this could also be a SHOW variable) Seems like serious overkill. No one has asked for access to individual components of the version string, other than the PG version number itself, which we already dealt with. I didn't actually see a user request for finding out the pointer width, either, but if there is one then Bruce's proposal seems fine. It is true no one asked for this information except Peter (I assume for just academic reasons), and I don't think we care from a bug reporting perspective, so I will just keep the patch around in case we ever want it. I'm sorry for late response, I was offline for last week. Current Solaris packages use 32/64bit information. See following output: postgres=# select version(); version -- PostgreSQL 8.3.5 64-bit on i386-pc-solaris2.11, compiled by /opt/SUNWspro.40/SS12/bin/cc -Xa The information about 32/64bit is important, because both versions are available, but for some reason they not have same features enabled (e.g. PL/pgPerl is not in 64bit version). These difference are described in the special man page and users need to easily determine which version is running. Zdenek -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] QuickLZ compression algorithm (Re: Inclusion in the PostgreSQL backend for toasting rows)
On Mon, Jan 5, 2009 at 2:02 PM, Gregory Stark st...@enterprisedb.com wrote: Regardless of whether we do that or not, no one has offered any justification of the arbitrary decision not to compress columns 1MB, Er, yes, there was discussion before the change, for instance: http://archives.postgresql.org/pgsql-hackers/2007-08/msg00082.php OK, maybe I'm missing something, but I don't see anywhere in that email where it suggests NEVER compressing anything above 1MB. It suggests some more nuanced things which are quite different. And do you have any response to this point? I think the right value for this setting is going to depend on the environment. If the system is starved for cpu cycles then you won't want to compress large data. If it's starved for i/o bandwidth but has spare cpu cycles then you will. http://archives.postgresql.org/pgsql-hackers/2009-01/msg00074.php I think it is a good point, to the extent that compression is an option that people choose in order to improve performance. I'm not really convinced that this is the case, but I haven't seen much evidence on either side of the question. Well the original code had a threshold above which we *always* compresed even if it saved only a single byte. I certainly don't think that's right either. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Time to finalize patches for 8.4 beta
Simon Riggs wrote: On Sat, 2009-01-03 at 22:34 -0500, Bruce Momjian wrote: o Recovery, Replication, Hot Standby: We need a _final_ version of any patches that are targeted for 8.4. There is so much activity in this area I am unclear what is ready for 8.4. What do we mean by _final_? This a serious, polite question. Hot Standby hasn't had any serious code review yet by anybody, AFAIK. Hot Standby is not a WIP patch. 96-8% of the patch is identical to the way it was on Nov 1: ready for some review comments and testing/usability feedback. The later a review happens the less time we have to change anything within the patch in response to comments. If anybody is deliberately holding back on review, they need to talk to me, directly, soon. The main problem seems to be that all qualified developers are allocated elsewhere, or working on their own patches. If we want this in 8.4, then other developers or companies need to allocate some time to assist with its inclusion. All help greatly appreciated. I assume hot standby is ready for review/commit. The commit fest wiki has: o Infrastructure changes for recovery o Hot Standby - queries during archive recovery o rmgr hooks and contrib/rmgr_hook o Synchronous log-shipping replication Are these all ready for review/commit? I assume all are except I don't know the answer for the last one. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] QuickLZ compression algorithm (Re: Inclusion in the PostgreSQL backend for toasting rows)
On Mon, 05 Jan 2009 13:44:57 -0500, Andrew Chernow wrote: Robert Haas wrote: What we do have is a suggestion from several people that the database shouldn't be in the business of compressing data AT ALL. If we want DB2 users generally seem very happy with the built-in compression. IMHO, this is a job for the application. Changing applications is several times more expensive and often simply not possible. -h -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Significantly larger toast tables on 8.4?
On Mon, Jan 5, 2009 at 11:45 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Peter Eisentraut escribió: James Mansion wrote: Peter Eisentraut wrote: c. Are there any well-known pitfalls/objections which would prevent me from changing the algorithm to something more efficient (read: IO-bound)? copyright licenses and patents Would it be possible to have a plugin facility? Well, before we consider that, we'd probably want to see proof about the effectiveness of other compression methods. I did some measurements months ago, and it was very clear that libz compression was a lot tighter than the PGLZ code. we have seen amazing results with lzo compression...2-3x faster compression times with only 10-15% less compression: There are tons of supporting examples online, for example: http://mail.jabber.org/pipermail/standards/2005-October/008768.html I think, if the database is automatically compressing things (which, IMO, it shouldn't), a low cpu overhead algorithm should be favored. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] incoherent view of serializable transactions
Peter Eisentraut pete...@gmx.net wrote: A language lawyer might also point out that the note that contains the explicitness isn't actually part of the formal standard. The only thing that the standard formally defines are the excluded phenomena. Previously quoted, from the standard: The execution of concurrent SQL-transactions at isolation level SERIALIZABLE is guaranteed to be serializable. A serializable execution is defined to be an execution of the operations of concurrently executing SQL-transactions that produces the same effect as some serial execution of those same SQL-transactions. A serial execution is one in which each SQL-transaction executes to completion before the next SQL-transaction begins. More to the point, think about how a user might want to think about these issues. The standard also requires that serializable transactions behave as though [...] --- User: The standard requires it, but is it also implemented? (Apparently not, but that is explained somewhere else.) That's something I thought about, but failed to find a good way to incorporate at that point, and I thought the discussion in the following sections covered it. Perhaps a reference to those following sections at the point of definition? is a natural consequence of the fact --- There is nothing natural about any of this. Why is it a consequence and how? How could you possibly get any of those phenomena if there are no concurrent transactions? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] an idea, language SPI
Pavel Stehule pavel.steh...@gmail.com writes: I am thinking about reimplementation PL/pgPSM, where code should be shared with PL/pgSQL. I have idea of some middle language, that should be used for both languages. This language could be based on SPI interface with some procedural elements (if, jmp, return). You mean exposed to the user? Why would anyone want that? By the time you had added enough features to it to be usable, you'd have plpgsql or equivalent. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Filtering WAL records in recovery
On Mon, 2009-01-05 at 19:27 +, Simon Riggs wrote: There's a long standing requirement for filtering WAL records during recovery, allowing us to select databases, tables etc. My approach to this has been to submit a patch for rmgr plugins. The patch has other good uses too, most especially being able to decode WAL records by having access to a continuously maintained catalog. That patch may not have been understood by many and a recent private conversation indicated it may be vetoed, though not really sure why. If we are going to reject the patch on rmgr plugins, then I'd like to get that overwith as quickly as possible so I can add other code to allow filtering for Hot Standby by some other route for 8.4. Why are we adding features to patches? Joshua D. Drake Thanks, -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] QuickLZ compression algorithm (Re: Inclusion in the PostgreSQL backend for toasting rows)
Alvaro Herrera wrote: On Sat, Jan 3, 2009 at 17:56, Lasse Reinhold l...@quicklz.com wrote: That sounds really exciting, I'd love to see QuickLZ included into PostgreSQL. I'd be glad to offer support and add custom optimizations, features or hacks or whatever should turn up. My only concern is to avoid undermining the commercial license, but this can, as you suggest, be solved by exceptionally allowing QuickLZ to be linked with PostgreSQL. Since I have exclusive copyright of QuickLZ any construction is possible. Hmm ... keep in mind that PostgreSQL is used as a base for a certain number of commercial, non-BSD products (Greenplum, Netezza, EnterpriseDB, Truviso, are the ones that come to mind). Would this exception allow for linking QuickLZ with them too? It doesn't sound to me like you're open to relicensing it under BSD, which puts us in an uncomfortable position. I'm not speaking for Lasse, merely providing food for thought, but it sounds feasible to me (and conforming to Lasse's spirit of his intended license) to put something like the following license on his code, which would allow inclusion into the PostgreSQL codebase and not restrict usage in any of the derived works: Grant license to use the code in question without cost, provided that the code is being linked to at least 50% of the PostgreSQL code it is being distributed alongside with. This should allow commercial reuse in derived products without undesirable sideeffects. -- Sincerely, Stephen R. van den Berg. Well, if we're going to make a party of it, let's nibble Nobby's nuts! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] an idea, language SPI
2009/1/5 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: I am thinking about reimplementation PL/pgPSM, where code should be shared with PL/pgSQL. I have idea of some middle language, that should be used for both languages. This language could be based on SPI interface with some procedural elements (if, jmp, return). You mean exposed to the user? Why would anyone want that? yes, minimally it should work like decompiler and test environment for runtime. plpgsql and plpgpsm should be compiled to spi language, and this should be interpreted with spi interpret. I expect really general runtime, that should be used for any purposes - maybe for T-SQL, for some emulation layers. Current runtime is based on fat layer over SPI, where any optimizations are difficult. Next compiler should better generate code based on SPI or DirectFunctionCall interface. I am searching some p-code, for stored procedures, and this only idea, - to define this p-code near SPI. Pavel By the time you had added enough features to it to be usable, you'd have plpgsql or equivalent. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Time to finalize patches for 8.4 beta
On Mon, 2009-01-05 at 15:05 -0500, Bruce Momjian wrote: I assume hot standby is ready for review/commit. It's in good shape, yes. With such a large patch I don't expect review-commit to be a single iteration. Though that isn't because I think there is flaky stuff in there, just that reviewers may prefer coding/modularisation to be different than it is. The commit fest wiki has: o Infrastructure changes for recovery o Hot Standby - queries during archive recovery This is just one patch now, though Heikki has broken the first piece off again for review/commit. o rmgr hooks and contrib/rmgr_hook Yes o Synchronous log-shipping replication It is in rework. As *was*, IMHO, no. As agreed, yes, possibly. Are these all ready for review/commit? I assume all are except I don't know the answer for the last one. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Many loaded library logs by preload libraries
ITAGAKI Takahiro itagaki.takah...@oss.ntt.co.jp writes: Tom Lane t...@sss.pgh.pa.us wrote: Yeah, I was noticing that myself while testing pg_stat_statements. I agree that we should fix it to reduce the message level for reloads occurring in child processes. I'd suggest using DEBUG2 if (IsUnderPostmaster process_shared_preload_libraries_in_progress). I'm not so enthused about eliminating messaging for local_preload_libraries, though. Here is a patch to do it. Applied, thanks. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] QuickLZ compression algorithm (Re: Inclusion in the PostgreSQL backend for toasting rows)
On Mon, 2009-01-05 at 13:04 -0500, Robert Haas wrote: Are we willing to be dropped from Debian and possibly Red Hat if this is the case? Regardless of whether we do that or not, no one has offered any justification of the arbitrary decision not to compress columns 1MB, and at least one person (Peter) has suggested that it is exactly backwards. I think he's right, and this part should be backed out. That will leave us back in the sensible place where people who want compression can get it (which is currently false) and people who don't want it can get rid of it (which has always been true). If there is a demand for alternative compression algorithms, then someone can submit a patch for that for 8.5. ...Robert +1 Sincerely, Joshua D. Drake -- PostgreSQL Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] EmitWarningsOnPlaceholders is too quiet
I wrote: I rememebered what INFO is for: it's the elevel that VACUUM VERBOSE uses to ensure that its output gets seen at the client. (I missed that in my first grep because vacuum's elog/ereport calls don't use INFO as a hard-wired elevel.) So we probably can't get rid of it. But that makes it even more clear IMHO that EmitWarningsOnPlaceholders shouldn't be using it. It's not intended for error/warning types of uses, but for verbose logging. After a more careful grep, here are all the uses of INFO elog level: VACUUM/ANALYZE/CLUSTER VERBOSE NOTIFY, in a standalone backend guc.c's set_config_option, to bleat about bad config settings obtained from ALTER USER or ALTER DATABASE during connection startup guc.c's EmitWarningsOnPlaceholders, to bleat about bad custom variables Also, most of the PLs have ways for users to emit messages at INFO elevel, but whether a user uses that in a consistent way is not our problem. I'm of the opinion that guc.c is in error here, and what it should be using for its purposes is WARNING. Objections? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED compatible connection manager
Peter Eisentraut wrote:
Martin Pihlak wrote:
I would call it something like
pg_postgresql_fdw_options_string(server, user) returns text
Hmm, it is probably a good idea to avoid the fdw abbreviation -- the term
foreign data wrapper is already confusing enough. My suggestion:
pg_foreign_server_conninfo(server)
pg_foreign_server_conninfo(server,user)
If there are no objections, I'll whack those functions out, and bring
the dblink
patch up to date.
Sure, propose some code. (Note that you can use parameter default
values now.)
Proposal attached. This adds two C functions:
List *GetForeignConnectionOptions(Oid serverid, Oid userid);
char *GetForeignConnectionString(Oid serverid, Oid userid);
One for obtaining all of the connection related options as a list, and
another for transforming these options into a libpq conninfo string.
The latter should be useful for dblink (although the userid and serverid
need to be obtained first).
On top of those there are two SQL accessible functions:
pg_foreign_connection_options(server name, user name = current_user,
OUT option_class text, OUT option_name text, OUT option_value text);
pg_foreign_connection_string(server name, user name = current_user);
These should initially be restricted from ordinary users -- grant explicitly
if the user should see the connect strings. Otherwise use from security definer
functions. The pg_foreign_connection_options() exposes all of the connection
options and can be used by clients such as DBI link to construct the connect
string or equivalent. pg_foreign_connection_string() can be used for instance
by plpythonu or plperlu functions to connect to remote postgres database.
Example:
select * from pg_foreign_connection_options('foo');
option_class | option_name | option_value
--+-+--
server | host| localhost
server | port| 54321
server | dbname | foo
user mapping | user| bob
user mapping | password| secret
(5 rows)
select * from pg_foreign_connection_string('foo');
pg_foreign_connection_string
-
host='localhost' port='54321' dbname='foo' user='bob' password='secret'
(1 row)
Will add regression and tests if this is acceptable.
PS. I'm not sure if I nailed the proargdefaults syntax correctly in pg_proc.h,
for now I just copied it out from a sample function with similar arguments.
regards,
Martin
*** a/src/backend/foreign/foreign.c
--- b/src/backend/foreign/foreign.c
***
*** 31,36
--- 31,38
extern Datum pg_options_to_table(PG_FUNCTION_ARGS);
+ extern Datum pg_foreign_connection_string(PG_FUNCTION_ARGS);
+ extern Datum pg_foreign_connection_options(PG_FUNCTION_ARGS);
/* list of currently loaded foreign-data wrapper interfaces */
***
*** 321,338 GetUserMapping(Oid userid, Oid serverid)
}
/*
! * deflist_to_tuplestore - Helper function to convert DefElem list to
* tuplestore usable in SRF.
*/
static void
! deflist_to_tuplestore(ReturnSetInfo *rsinfo, List *options)
{
ListCell *cell;
TupleDesc tupdesc;
Tuplestorestate *tupstore;
! Datum values[2];
! bool nulls[2] = { 0 };
MemoryContext per_query_ctx;
MemoryContext oldcontext;
--- 323,447
}
+ /*
+ * Helper for appending a ForeignConnectionOption node to a list.
+ */
+ static List *
+ append_option_list(List *options, GenericOptionFlags type, DefElem *def)
+ {
+ ForeignConnectionOption *opt = makeNode(ForeignConnectionOption);
+
+ opt-opttype = type;
+ opt-option = def;
+ return lappend(options, opt);
+ }
+
+
+ /*
+ * GetForeignConnectionOptions - look up the options for foreign connection.
+ *
+ * Foreign connection is defined by the foreign data wrapper, server and
+ * user mapping triple. The options are simply merged together into a list
+ * of ForeignConnectionOption nodes.
+ *
+ * The role specified by userid must have a user mapping and USAGE privilege
+ * on the server.
+ */
+ List *
+ GetForeignConnectionOptions(Oid serverid, Oid userid)
+ {
+ ForeignServer *server;
+ UserMapping *um;
+ ForeignDataWrapper *fdw;
+ List *result = NIL;
+ ListCell *cell;
+ AclResult aclresult;
+
+ server = GetForeignServer(serverid);
+ um = GetUserMapping(userid, serverid);
+ fdw = GetForeignDataWrapper(server-fdwid);
+
+ /* Check permissions, user must have usage on the server. */
+ aclresult = pg_foreign_server_aclcheck(serverid, userid, ACL_USAGE);
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, ACL_KIND_FOREIGN_SERVER, server-servername);
+
+ /* Seems OK, prepare a list of all the options */
+ foreach (cell, fdw-options)
+ result = append_option_list(result, FdwOpt, lfirst(cell));
+ foreach (cell, server-options)
+ result = append_option_list(result, ServerOpt, lfirst(cell));
+ foreach (cell,
[HACKERS] Segfault on CVS HEAD
I am seeing the following segmentation fault when doing 'gmake install' on CVS HEAD; any idea why? $ gmake install /bin/sh ../config/mkinstalldirs '/usr/local/pgsql/lib/pgxs/src' /bin/sh ../config/install-sh -c -m 644 Makefile.global '/usr/local/pgsql/lib/pgxs/src/Makefile.global' /bin/sh ../config/install-sh -c -m 644 Makefile.port '/usr/local/pgsql/lib/pgxs/src/Makefile.port' /bin/sh ../config/install-sh -c -m 644 ./Makefile.shlib '/usr/local/pgsql/lib/pgxs/src/Makefile.shlib' /bin/sh ../config/install-sh -c -m 644 ./nls-global.mk '/usr/local/pgsql/lib/pgxs/src/nls-global.mk' gmake -C port install gmake[1]: Entering directory `/usr/var/local/src/gen/pgsql/CURRENT/pgsql/src/port' /bin/sh ../../config/install-sh -c -m 644 libpgport.a '/usr/local/pgsql/lib/libpgport.a' gmake[1]: Leaving directory `/usr/var/local/src/gen/pgsql/CURRENT/pgsql/src/port' gmake -C timezone install gmake[1]: Entering directory `/usr/var/local/src/gen/pgsql/CURRENT/pgsql/src/timezone' gmake -C ../../src/port all gmake[2]: Entering directory `/usr/var/local/src/gen/pgsql/CURRENT/pgsql/src/port' gmake[2]: Nothing to be done for `all'. gmake[2]: Leaving directory `/usr/var/local/src/gen/pgsql/CURRENT/pgsql/src/port' gmake: *** [install] Segmentation fault $ I rebooted my server but it didn't help. All this just started today. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] incoherent view of serializable transactions
Sorry if I'm restating the obvious, however I don't understand the confusion, as it seems the standard's definition isn't mysterious; it simply requires that the resulting state from the concurrent execution of transactions (and implicitly any subset) designated to occur at the isolation level SERIALIZABLE be equivalent to SOME LITERALLY SERIAL execution of said transactions. Thereby although of course transactions literally executed serially comply; they need not be executed literally serially, as long as some mechanism is used to warrant that the state resulting from the completion of every such transaction in some sequence be equivalent to the literally serial execution of the transactions in the same sequence. Thereby even if only a subset of such concurrently executed transactions may be complete at some point in time, any potentially remaining such transactions may be restarted for whatever reason and remain compliant (as the resulting state from the concurrent execution of such transactions will be equivalent to some literal serial execution of said transactions upon the completion of each so executed transaction; correspondingly any implementation which can not warrant the same, is simply non-compliant). Thereby although no pre-specified completion order exists per-se, the order of completion of transactions implicitly determine if incomplete transactions may need to be restarted if dependant on a preceding completed transaction's resulting mutated state (or alternatively allowed to proceed if previously blocked on said potentially mutated state). Thereby although a collection of such concurrently executed transactions my have mutual interdependencies, each subsequent complete transaction in effect break dependency deadlocks as may otherwise logically exist; and thereby may affect optimal logical execution order of remaining such transactions potentially concurrently; and be warranted to never deadlock. I believe IMHO. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] QuickLZ compression algorithm (Re: Inclusion in the PostgreSQL backend for toasting rows)
Guaranteed compression of large data fields is the responsibility of the client. The database should feel free to compress behind the scenes if it turns out to be desirable, but an expectation that it compresses is wrong in my opinion. That said, I'm wondering why compression has to be a problem or why 1 Mbyte is a reasonable compromise? I missed the original thread that lead to 8.4. It seems to me that transparent file system compression doesn't have limits like files must be less than 1 Mbyte to be compressed. They don't exhibit poor file system performance. I remember back in the 386/486 days, that I would always DriveSpace compress everything, because hard disks were so slow then that DriveSpace would actually increase performance. The toast tables already give a sort of block-addressable scheme. Compression can be on a per block or per set of blocks basis allowing for seek into the block, or if compression doesn't seem to be working for the first few blocks, the later blocks can be stored uncompressed? Or is that too complicated compared to what we have now? :-) Cheers, mark -- Mark Mielke m...@mielke.cc -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] QuickLZ compression algorithm (Re: Inclusion in the PostgreSQL backend for toasting rows)
A.M. wrote: On Jan 5, 2009, at 1:16 PM, Stephen R. van den Berg wrote: Upon reading the DFSG, it seems you have a point... However... QuickLZ is dual licensed: a. Royalty-free-perpetuous-use as part of the PostgreSQL backend or any derived works of PostgreSQL which link in *at least* 50% of the original PostgreSQL codebase. How does one even define 50% of the original PostgreSQL codebase? What nonsense. It's a suggested (but by no means definitive) technical translation of the legalese term substantial. Substitute with something better, by all means. -- Sincerely, Stephen R. van den Berg. Well, if we're going to make a party of it, let's nibble Nobby's nuts! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] QuickLZ compression algorithm (Re: Inclusion in the PostgreSQL backend for toasting rows)
Mark Mielke m...@mark.mielke.cc writes: It seems to me that transparent file system compression doesn't have limits like files must be less than 1 Mbyte to be compressed. They don't exhibit poor file system performance. Well I imagine those implementations are more complex than toast is. I'm not sure what lessons we can learn from their behaviour directly. I remember back in the 386/486 days, that I would always DriveSpace compress everything, because hard disks were so slow then that DriveSpace would actually increase performance. Surely this depends on whether your machine was cpu starved or disk starved? Do you happen to recall which camp these anecdotal machines from 1980 fell in? The toast tables already give a sort of block-addressable scheme. Compression can be on a per block or per set of blocks basis allowing for seek into the block, The current toast architecture is that we compress the whole datum, then store the datum either inline or using the same external blocking mechanism that we use when not compressing. So this doesn't fit at all. It does seem like an interesting idea to have toast chunks which are compressed individually. So each chunk could be, say, an 8kb chunk of plaintext and stored as whatever size it ends up being after compression. That would allow us to do random access into external chunks as well as allow overlaying the cpu costs of decompression with the i/o costs. It would get a lower compression ratio than compressing the whole object together but we would have to experiment to see how big a problem that was. It would be pretty much rewriting the toast mechanism for external compressed data though. Currently the storage and the compression are handled separately. This would tie the two together in a separate code path. Hm, It occurs to me we could almost use the existing code. Just store it as a regular uncompressed external datum but allow the toaster to operate on the data column (which it's normally not allowed to) to compress it, but not store it externally. or if compression doesn't seem to be working for the first few blocks, the later blocks can be stored uncompressed? Or is that too complicated compared to what we have now? :-) Actually we do that now, it was part of the same patch we're discussing. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] QuickLZ compression algorithm (Re: Inclusion in the PostgreSQL backend for toasting rows)
Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: The whole thing got started because Alex Hunsaker pointed out that his database got a lot bigger because we disabled compression on columns 1MB. It seems like the obvious thing to do is turn it back on again. After poking around in those threads a bit, I think that the current threshold of 1MB was something I just made up on the fly (I did note that it needed tuning...). Perhaps something like 10MB would be a better default. Another possibility is to have different minimum compression rates for small and large datums. As far as I can imagine, the following use cases apply: a. Columnsize = 2048 bytes without substring access. b. Columnsize = 2048 bytes with substring access. c. Columnsize 2048 bytes compressible without substring access (text). d. Columnsize 2048 bytes uncompressible with substring access (multimedia). Can anyone think of another use case I missed here? To cover those cases, the following solutions seem feasible: Sa. Disable compression for this column (manually, by the DBA). Sb. Check if the compression saves more than 20%, store uncompressed otherwise. Sc. Check if the compression saves more than 20%, store uncompressed otherwise. Sd. Check if the compression saves more than 20%, store uncompressed otherwise. For Sb, Sc and Sd we should probably only check the first 256KB or so to determine the expected savings. -- Sincerely, Stephen R. van den Berg. Well, if we're going to make a party of it, let's nibble Nobby's nuts! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Segfault on CVS HEAD
Bruce Momjian wrote: gmake: *** [install] Segmentation fault $ I rebooted my server but it didn't help. All this just started today. Your make is buggy? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Segfault on CVS HEAD
Alvaro Herrera alvhe...@commandprompt.com writes: Bruce Momjian wrote: gmake: *** [install] Segmentation fault Your make is buggy? I'm not totally sure, but I think this is make reporting that install crashed. Perhaps a corrupted install executable? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] QuickLZ compression algorithm (Re: Inclusion in the PostgreSQL backend for toasting rows)
Gregory Stark st...@enterprisedb.com writes: Hm, It occurs to me we could almost use the existing code. Just store it as a regular uncompressed external datum but allow the toaster to operate on the data column (which it's normally not allowed to) to compress it, but not store it externally. Yeah, it would be very easy to do that, but the issue then would be that instead of having a lot of toast-chunk rows that are all carefully made to fit exactly 4 to a page, you have a lot of toast-chunk rows of varying size, and you are certainly going to waste some disk space due to not being able to pack pages full. In the worst case you'd end up with zero benefit from compression anyway. As an example, if all of your 2K chunks compress by just under 20%, you get no savings because you can't quite fit 5 to a page. You'd need an average compression rate more than 20% to get savings. We could improve that figure by making the chunk size smaller, but that carries its own performance penalties (more seeks to fetch all of a toasted value). Also, the smaller the chunks the worse the compression will get. It's an interesting idea, and would be easy to try so I hope someone does test it out and see what happens. But I'm not expecting miracles. I think a more realistic approach would be the one somebody suggested upthread: split large values into say 1MB segments that are compressed separately and then stored to TOAST separately. Substring fetches then pay the overhead of decompressing 1MB segments that they might need only part of, but at least they're not pulling out the whole gosh-darn value. As long as the segment size isn't tiny, the added storage inefficiency should be pretty minimal. (How we'd ever do upgrade-in-place to any new compression scheme is an interesting question too...) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Kudos on v8.4dev
I just wanted to give you guys a virtual pat on the back for PG v8.4dev. I've been out of the computer world for a little over a year and we're still using v8.1 here where I work, so I'm a little behind the changes timeline since v8.1, but v8.4 is looking very nice. I'm working on migrating a 200G database (with large full text records) from a 3rd-party database system in 8.4dev as a proof of concept for using PG for everything, and so far things are great. Just the improvement in error message detail (esp. syntax errors WRT malformed data) is huge. I can't wait to get into all the other fancy stuff 8.4 has to offer. Hopefully it'll be released around the same time I've sold the PHB's on my POC. I'm going to be using 8.4dev for the next month or more, so if there's anything y'all would like feedback on, especially regarding multi- gigabyte databases w/ full text, I'll be happy to provide it. Keep up the great work! eric -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Time to finalize patches for 8.4 beta
Bruce, Aside from the big patches you mentioned. The following patches do not appear to be ready to commit based on most recent feedback, and should probably be deferred for 8.5: -- Column-Level permissions -- Automatic View Update Rules -- GIN Fast Insert -- On-Disk Bitmap Index (Gianni says new patch in a week.) -- Hash-Join Filter Using Bloom Filters -- Reducing some DDL Locks to ShareLock (remaining unapplied portions) The following patches are just waiting for one more fix, and have recently work by authors. We should set a deadline for them: -- B-Tree GIN (this was reviewed late, so Oleg may fix it) -- Multi-Batch Hash Join -- PITR Performance Improvement These patches might be ready to commit, but need further review. I've attempted to get reviewers for them, but none of the RRR have the right skills/experience to do the remaining review needed (i.e. we need some reviewers to step up from -hackers!): -- posix_fadvise -- parallel restore -- Solve a problem of LC_TIME of windows Finally, these patches need performance / field testing by a wide variety of users: -- posix_fadvise -- Simple postgresql.conf wizard (or is this pushed into pgFoundry?) -- pg_upgrade script for 8.3-8.4 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Time to finalize patches for 8.4 beta
On Mon, 2009-01-05 at 17:12 -0800, Josh Berkus wrote: -- B-Tree GIN (this was reviewed late, so Oleg may fix it) This one is really pretty much ready. The only reason I didn't pass it along for committer review are a couple very minor things that I thought the authors might want to comment on. If I don't see any comments later this week, I'll post a new patch. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Function with defval returns wrong result
Rushabh Lathia rushabh.lat...@gmail.com writes: Following test returns wrong result .. Ooops, seems we forgot to add default arguments recursively. Fixed, thanks for the report! regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] QuickLZ compression algorithm (Re: Inclusion in the PostgreSQL backend for toasting rows)
Gregory Stark wrote: Mark Mielke m...@mark.mielke.cc writes: It seems to me that transparent file system compression doesn't have limits like files must be less than 1 Mbyte to be compressed. They don't exhibit poor file system performance. Well I imagine those implementations are more complex than toast is. I'm not sure what lessons we can learn from their behaviour directly. I remember back in the 386/486 days, that I would always DriveSpace compress everything, because hard disks were so slow then that DriveSpace would actually increase performance. Surely this depends on whether your machine was cpu starved or disk starved? Do you happen to recall which camp these anecdotal machines from 1980 fell in? I agree. I'm sure it was disk I/O starved - and maybe not just the disk. The motherboard might have contributed. :-) My production machine in 2008/2009 for my uses still seems I/O bound. The main database server I use is 2 x Intel Xeon 3.0 Ghz (dual-core) = 4 cores, and the uptime load average for the whole system is currently 0.10. The database and web server use their own 4 drives with RAID 10 (main system is on two other drives). Yes, I could always upgrade to a fancy/larger RAID array, SAS, 15k RPM drives, etc. but if a PostgreSQL tweak were to give me 30% more performance at a 15% CPU cost... I think that would be a great alternative option. :-) Memory may also play a part. My server at home has 4Mbytes of L2 cache and 4Gbytes of RAM running with 5-5-5-18 DDR2 at 1000Mhz. At these speeds, my realized bandwidth for RAM is 6.0+ Gbyte/s. My L1/L2 operate at 10.0+ Gbyte/s. Compression doesn't run that fast, so at least for me, the benefit of having something in L1/L2 cache vs RAM isn't great, however, my disks in the RAID10 configuraiton only read/write at ~150Mbyte/s sustained, and much less if seeking is required. Compressing the data means 30% more data may fit into RAM or 30% increase in data read from disk, as I assume many compression algorithms can beat 150 Mbyte/s. Is my configuration typical? It's probably becoming more so. Certainly more common than the 10+ disk hardware RAID configurations. The toast tables already give a sort of block-addressable scheme. Compression can be on a per block or per set of blocks basis allowing for seek into the block, The current toast architecture is that we compress the whole datum, then store the datum either inline or using the same external blocking mechanism that we use when not compressing. So this doesn't fit at all. It does seem like an interesting idea to have toast chunks which are compressed individually. So each chunk could be, say, an 8kb chunk of plaintext and stored as whatever size it ends up being after compression. That would allow us to do random access into external chunks as well as allow overlaying the cpu costs of decompression with the i/o costs. It would get a lower compression ratio than compressing the whole object together but we would have to experiment to see how big a problem that was. It would be pretty much rewriting the toast mechanism for external compressed data though. Currently the storage and the compression are handled separately. This would tie the two together in a separate code path. Hm, It occurs to me we could almost use the existing code. Just store it as a regular uncompressed external datum but allow the toaster to operate on the data column (which it's normally not allowed to) to compress it, but not store it externally. Yeah - sounds like it could be messy. or if compression doesn't seem to be working for the first few blocks, the later blocks can be stored uncompressed? Or is that too complicated compared to what we have now? :-) Actually we do that now, it was part of the same patch we're discussing. Cheers, mark -- Mark Mielke m...@mielke.cc
Re: [HACKERS] QuickLZ compression algorithm (Re: Inclusion in the PostgreSQL backend for toasting rows)
Holger Hoffstaette wrote: On Mon, 05 Jan 2009 13:44:57 -0500, Andrew Chernow wrote: Robert Haas wrote: What we do have is a suggestion from several people that the database shouldn't be in the business of compressing data AT ALL. If we want DB2 users generally seem very happy with the built-in compression. IMHO, this is a job for the application. Changing applications is several times more expensive and often simply not possible. The database can still handle all of the compression requirements if the application creates a couple user-defined functions (probably in c) that utilize one of the many existing compression libraries (hand picked for their needs). You can use them in triggers to make it transparent. You can use them directly in statements. You can control selecting the data compressed or uncomrpessed, which is a valid use case for remote clients that have to download a large bytea or text. You can toggle compression algorithms and settings dependant on $whatever. You can do this all of this right w/o the built-in compression, which is my point; why have the built-in compression at all? Seems like a home-cut solution provides more features and control with minimal engineering. All the real engineering is done: the database and compression libraries. All that's left are a few glue functions in C. Well, my two pennies :) -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Time to finalize patches for 8.4 beta
Bruce Momjian wrote: Now that we are two months into the final commit fest, it is time to finalize all the open patches so we can target a February beta. The two major outstanding patches are: o SE-PostgreSQL: The author has done an outstanding job of reworking the patch so the burden is now on the community. We have to decide if we want to add this amount of new code to have both SQL row permissions and SE-Linux support. At least, this patch set has been ready for reviewing for several months, but it also hasn't get any serious code reviews since CommitFest:May. (No need to say, I feel grateful for some of proposals, like PGACE framework, Row-level ACLS, simultaneous DACMAC, ...) I've already prepared to rework the patch set on reviewer's comments, and our employer allows me to pay my efforts to v8.4 development cycle with the highest priority. Again, I strongly want it to be progressed. If necessary, I'll update my patches as soon as possible. Thanks, o Recovery, Replication, Hot Standby: We need a _final_ version of any patches that are targeted for 8.4. There is so much activity in this area I am unclear what is ready for 8.4. I think the remaining patches can be addressed pretty easily but we need final versions from any authors who are still adjusting them. Let's see what we can get done in the next two weeks and reevaluate. -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Segfault on CVS HEAD
Tom Lane wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Bruce Momjian wrote: gmake: *** [install] Segmentation fault Your make is buggy? I'm not totally sure, but I think this is make reporting that install crashed. Perhaps a corrupted install executable? Turns out the cause was a buggy gmake that segment fauled on this: $(if $(ZIC),$(ZIC),./zic) -d ... It affects at least gmake 3.78.1 but is fixed in at least version 3.81. I talked to Peter and it seemed easist to just simplify the gmake rules and document the issue with a comment, attached. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: src/timezone/Makefile === RCS file: /cvsroot/pgsql/src/timezone/Makefile,v retrieving revision 1.30 diff -c -c -r1.30 Makefile *** src/timezone/Makefile 5 Jan 2009 10:25:59 - 1.30 --- src/timezone/Makefile 6 Jan 2009 02:06:26 - *** *** 38,49 all: submake-libpgport zic endif zic: $(ZICOBJS) $(CC) $(CFLAGS) $(ZICOBJS) $(LDFLAGS) $(LIBS) -o $...@$(X) install: all installdirs ifeq (,$(with_system_tzdata)) ! $(if $(ZIC),$(ZIC),./zic) -d '$(DESTDIR)$(datadir)/timezone' -p '$(POSIXRULES)' $(TZDATAFILES) endif $(MAKE) -C tznames $@ --- 38,57 all: submake-libpgport zic endif + # We could do this test in the action section: + # $(if $(ZIC),$(ZIC),./zic) + # but gmake versions = 3.78.1 or perhaps later have a bug + # that causes a segfault; gmake 3.81 or later fixes this. + ifeq (,$(ZIC)) + ZIC= ./zic + endif + zic: $(ZICOBJS) $(CC) $(CFLAGS) $(ZICOBJS) $(LDFLAGS) $(LIBS) -o $...@$(X) install: all installdirs ifeq (,$(with_system_tzdata)) ! $(ZIC) -d '$(DESTDIR)$(datadir)/timezone' -p '$(POSIXRULES)' $(TZDATAFILES) endif $(MAKE) -C tznames $@ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] QuickLZ compression algorithm (Re: Inclusion in the PostgreSQL backend for toasting rows)
I suggest that before we make any knee-jerk responses, we need to go back and reread the prior discussion. http://archives.postgresql.org/pgsql-patches/2008-02/msg00053.php and that message links to several older threads that were complaining about the 8.3 behavior. In particular the notion of an upper limit on what we should attempt to compress was discussed in this thread: http://archives.postgresql.org/pgsql-general/2007-08/msg01129.php Thanks for the pointers. After poking around in those threads a bit, I think that the current threshold of 1MB was something I just made up on the fly (I did note that it needed tuning...). Perhaps something like 10MB would be a better default. Another possibility is to have different minimum compression rates for small and large datums. After reading these discussions, I guess I still don't understand why we would treat small and large datums differently. It seems to me that you had it about right here: http://archives.postgresql.org/pgsql-hackers/2007-08/msg00082.php # Or maybe it should just be a min_comp_rate and nothing else. # Compressing a 1GB field to 999MB is probably not very sane either. I agree with that. force_input_size doesn't seem like a good idea because compression can be useless on big datums just as it can be on little ones - the obvious case being media file formats that are already internally compressed. Even if you can squeeze a little more out, you're using a lot of CPU time for a very small gain in storage and/or I/O. Furthermore, on a large object, saving even 1MB is not very significant if the datum is 1GB in size - so, again, a percentage seems like the right thing. On the other hand, even after reading these threads, I still don't see any need to disable compression for large datums. I can't think of any reason why I would want to try compressing a 900kB object but not 1MB one. It makes sense to me to not compress if the object doesn't compress well, or if some initial segment of the object doesn't compress well (say, if we can't squeeze 10% out of the first 64kB), but size by itself doesn't seem significant. To put that another way, if small objects and large objects are to be treated differently, which one will we try harder to compress and why? Greg Stark makes an argument that we should try harder when it might avoid the need for a toast table: http://archives.postgresql.org/pgsql-hackers/2007-08/msg00087.php ...which has some merit, though clearly it would be a lot better if we could do it when, and only when, it was actually going to work. Also, not compressing very small datums ( 256 bytes) also seems smart, since that could end up producing a lot of extra compression attempts, most of which will end up saving little or no space. Apart from those two cases I don't see any clear motivation for discriminating on size. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Fix for compiler warning in reloptions.c
The attached applied patch prevents a compiler warning; the compiler
doesn't know about our elog(ERROR) exit case.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: src/backend/access/common/reloptions.c
===
RCS file: /cvsroot/pgsql/src/backend/access/common/reloptions.c,v
retrieving revision 1.13
diff -c -c -r1.13 reloptions.c
*** src/backend/access/common/reloptions.c 5 Jan 2009 17:14:28 - 1.13
--- src/backend/access/common/reloptions.c 6 Jan 2009 02:35:32 -
***
*** 667,673
{
char *value;
int value_len;
! bool parsed;
bool nofree = false;
if (option-isset validate)
--- 667,673
{
char *value;
int value_len;
! bool parsed = true; /* quiet compiler */
bool nofree = false;
if (option-isset validate)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] contrib Japanese README
Hi, There are some Japanese README files in contrib module: contrib/pgbench/README.pgbench_jis contrib/pgrowlocks/README.pgrowlocks.euc_jp contrib/pgstattuple/README.pgstattuple.euc_jp These files are outdated and are not necessarily living there since the official docs are now provided as SGML files (Japanese docs are provided by JPUG). For these reasons I propose to remove them. Unless there's an objection, I will commit the removal into CVS Head. -- Tatsuo Ishii SRA OSS, Inc. Japan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] version() output vs. 32/64 bits
Tom Lane wrote:
Bruce Momjian br...@momjian.us writes:
So what do we want to do for 8.4? Add 32/64-bit version() indicator and
add OUT parameters to the TODO list?
+1. There seems a good case for making the 32/64bit distinction
visible somewhere, and the text version string is as good as anyplace.
OK, done with the attached patch, and autoconf run. Magnus, would you
add this change to the MSVC build? Thanks.
test= select version();
version
--
PostgreSQL 8.4devel on i386-pc-bsdi4.3.1, compiled by GCC 2.95.3, 32-bit
(1 row)
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: src/backend/access/common/reloptions.c
===
RCS file: /cvsroot/pgsql/src/backend/access/common/reloptions.c,v
retrieving revision 1.13
diff -c -c -r1.13 reloptions.c
*** src/backend/access/common/reloptions.c 5 Jan 2009 17:14:28 - 1.13
--- src/backend/access/common/reloptions.c 6 Jan 2009 02:35:32 -
***
*** 667,673
{
char *value;
int value_len;
! bool parsed;
bool nofree = false;
if (option-isset validate)
--- 667,673
{
char *value;
int value_len;
! bool parsed = true; /* quiet compiler */
bool nofree = false;
if (option-isset validate)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Fix for compiler warning in reloptions.c
Bruce Momjian wrote: The attached applied patch prevents a compiler warning; the compiler doesn't know about our elog(ERROR) exit case. Hmm, I don't like your fix; ISTM it would be better to set the variable only in the default: case (or maybe change the break for a return). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] contrib Japanese README
Tatsuo Ishii wrote: Hi, There are some Japanese README files in contrib module: contrib/pgbench/README.pgbench_jis contrib/pgrowlocks/README.pgrowlocks.euc_jp contrib/pgstattuple/README.pgstattuple.euc_jp These files are outdated and are not necessarily living there since the official docs are now provided as SGML files (Japanese docs are provided by JPUG). For these reasons I propose to remove them. Unless there's an objection, I will commit the removal into CVS Head. Agreed. I assume the content is now being translated to Japanese as part of the SGML docs. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] contrib Japanese README
These files are outdated and are not necessarily living there since the official docs are now provided as SGML files (Japanese docs are provided by JPUG). For these reasons I propose to remove them. Unless there's an objection, I will commit the removal into CVS Head. Agreed. I assume the content is now being translated to Japanese as part of the SGML docs. Yes, the content is provided as English SGML files and then they are translated into Japanese by JPUG. So we would lose nothing actually. -- Tatsuo Ishii SRA OSS, Inc. Japan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Fix for compiler warning in reloptions.c
Alvaro Herrera wrote:
Bruce Momjian wrote:
The attached applied patch prevents a compiler warning; the compiler
doesn't know about our elog(ERROR) exit case.
Hmm, I don't like your fix; ISTM it would be better to set the variable
only in the default: case (or maybe change the break for a return).
Agreed, I used the 'default' idea, attached, applied.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: src/backend/access/common/reloptions.c
===
RCS file: /cvsroot/pgsql/src/backend/access/common/reloptions.c,v
retrieving revision 1.14
diff -c -c -r1.14 reloptions.c
*** src/backend/access/common/reloptions.c 6 Jan 2009 02:44:17 - 1.14
--- src/backend/access/common/reloptions.c 6 Jan 2009 03:10:36 -
***
*** 667,673
{
char *value;
int value_len;
! bool parsed = true; /* quiet compiler */
bool nofree = false;
if (option-isset validate)
--- 667,673
{
char *value;
int value_len;
! bool parsed;
bool nofree = false;
if (option-isset validate)
***
*** 736,741
--- 736,742
break;
default:
elog(ERROR, unsupported reloption type %d, option-gen-type);
+ parsed = true; /* quiet compiler */
break;
}
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] dblink vs SQL/MED - security and implementation details
Peter Eisentraut wrote:
Joe Conway wrote:
I'm mainly concerned about re-opening security holes that we spent a
lot of time debating and subsequently closing. I suspect if we assume
that any FDW-derived connect string can bypass the checks we put in
place, we will regret it later. But I'm open to arguments on both
sides...
Can you elaborate what security issues you are concerned about?
For example: on a freshly installed postgres installation, that does
*not* require authentication, you would get the following behavior which
previously was found to be unacceptable:
8--
--
-- as the superuser
--
CREATE FOREIGN DATA WRAPPER postgresql LIBRARY 'postgresql_fdw'
LANGUAGE C;
CREATE SERVER fdtest FOREIGN DATA WRAPPER postgresql
OPTIONS (dbname 'contrib_regression');
CREATE USER dblink_regression_test;
CREATE USER MAPPING FOR dblink_regression_test SERVER fdtest;
\c - dblink_regression_test
psql (8.4devel)
You are now connected to database contrib_regression as user
dblink_regression_test.
--
-- now as untrusted user dblink_regression_test
--
contrib_regression= SELECT dblink_connect('myconn', 'fdtest');
dblink_connect
OK
(1 row)
contrib_regression= SELECT * FROM dblink('myconn','SELECT
current_user') AS t(u text);
u
--
postgres
(1 row)
8--
Now, we can blame the superuser for explicitly allowing this, but I
don't see that as much different from the previously voiced security
concerns. I'm sure there are other privilege escalation scenarios, but I
haven't tried at all hard to think of them...
Joe
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] QuickLZ compression algorithm (Re: Inclusion in the PostgreSQL backend for toasting rows)
Robert Haas wrote: After poking around in those threads a bit, I think that the current threshold of 1MB was something I just made up on the fly (I did note that it needed tuning...). Perhaps something like 10MB would be a better default. Another possibility is to have different minimum compression rates for small and large datums. After reading these discussions, I guess I still don't understand why we would treat small and large datums differently. It seems to me that you had it about right here: http://archives.postgresql.org/pgsql-hackers/2007-08/msg00082.php # Or maybe it should just be a min_comp_rate and nothing else. # Compressing a 1GB field to 999MB is probably not very sane either. I agree with that. force_input_size doesn't seem like a good idea because compression can be useless on big datums just as it can be on little ones - the obvious case being media file formats that are already internally compressed. Even if you can squeeze a little more out, you're using a lot of CPU time for a very small gain in storage and/or I/O. Furthermore, on a large object, saving even 1MB is not very significant if the datum is 1GB in size - so, again, a percentage seems like the right thing. On the other hand, even after reading these threads, I still don't see any need to disable compression for large datums. I can't think of any reason why I would want to try compressing a 900kB object but not 1MB one. It makes sense to me to not compress if the object doesn't compress well, or if some initial segment of the object doesn't compress well (say, if we can't squeeze 10% out of the first 64kB), but size by itself doesn't seem significant. To put that another way, if small objects and large objects are to be treated differently, which one will we try harder to compress and why? Greg Stark makes an argument that we should try harder when it might avoid the need for a toast table: http://archives.postgresql.org/pgsql-hackers/2007-08/msg00087.php ...which has some merit, though clearly it would be a lot better if we could do it when, and only when, it was actually going to work. Also, not compressing very small datums ( 256 bytes) also seems smart, since that could end up producing a lot of extra compression attempts, most of which will end up saving little or no space. Apart from those two cases I don't see any clear motivation for discriminating on size. Agreed. I have seen a lot of discussion on this topic and the majority seems to feel that a size limit on compress doesn't make sense in the general case. It is true that there is dimminished performance for substring operations as TOAST values get longer but compression does give better performance for longer values for full field retrieval. I don't think we should be optimizing TOAST for substrings --- users who know they are going to be using substrings can specify the storage type for the column directly. Having any kind of maximum makes it hard for administrators to know exactly what is happening in TOAST. I think the upper limit should be removed with a documentation mention in the substring() section mentioning the use of non-compressed TOAST storage. The only way I think an upper compression limit makes sense is if the backend can't uncompress the value to return it to the user, but then you have to wonder how the value got into the database in the first place. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] QuickLZ compression algorithm (Re: Inclusion in the PostgreSQL backend for toasting rows)
Robert Haas robertmh...@gmail.com writes: After reading these discussions, I guess I still don't understand why we would treat small and large datums differently. It seems to me that you had it about right here: http://archives.postgresql.org/pgsql-hackers/2007-08/msg00082.php # Or maybe it should just be a min_comp_rate and nothing else. # Compressing a 1GB field to 999MB is probably not very sane either. Well, that's okay with me. I think that the other discussion was mainly focused on the silliness of compressing large datums when only a small percentage could be saved. What we might do for the moment is just to set the upper limit to INT_MAX in the default strategy, rather than rip out the logic altogether. IIRC that limit is checked only once per compression, not in the inner loop, so it won't cost us any noticeable performance to leave the logic there in case someone finds a use for it. not compressing very small datums ( 256 bytes) also seems smart, since that could end up producing a lot of extra compression attempts, most of which will end up saving little or no space. But note that the current code will usually not try to do that anyway, at least for rows of ordinary numbers of columns. The present code has actually reduced the lower-bound threshold from where it used to be. I think that if anyone wants to argue for a different value, it'd be time to whip out some actual tests ;-). We can't set specific parameter values from gedanken-experiments. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] ERROR: failed to find conversion function from unknown to text
Q1: select '' union all select ''
Q2: select '' union all select * from (select '' ) as s
version: PostgreSQL 8.3.1, compiled by Visual C++ build 1400
Hi All,
Q1 works just fine, but Q2 fails with:
ERROR: failed to find conversion function from unknown to text
Q2 is a generalization of a huge query we are facing, which we cannot
modify. I don't think this is a 'removed-casts' problem generally faced in
8.3, but I may be wrong. Will adding some cast resolve this?
Best regards,
--
gurjeet[.sin...@enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
Re: [HACKERS] ERROR: failed to find conversion function from unknown to text
Hello
2009/1/6 Gurjeet Singh singh.gurj...@gmail.com:
Q1: select '' union all select ''
Q2: select '' union all select * from (select '' ) as s
version: PostgreSQL 8.3.1, compiled by Visual C++ build 1400
Hi All,
Q1 works just fine, but Q2 fails with:
ERROR: failed to find conversion function from unknown to text
Q2 is a generalization of a huge query we are facing, which we cannot
modify. I don't think this is a 'removed-casts' problem generally faced in
8.3, but I may be wrong. Will adding some cast resolve this?
yes
postgres=# select '' union all select * from (select ''::text ) as s;
?column?
--
(2 rows)
regards
Pavel Stehule
Best regards,
--
gurjeet[.sin...@enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ERROR: failed to find conversion function from unknown to text
As I mentioned, we cannot change the query, so adding casts to the query is
not an option. I was looking for something external to the query, like a
CREATE CAST command that'd resolve the issue.
Best regards,
On Tue, Jan 6, 2009 at 12:00 PM, Pavel Stehule pavel.steh...@gmail.comwrote:
Hello
2009/1/6 Gurjeet Singh singh.gurj...@gmail.com:
Q1: select '' union all select ''
Q2: select '' union all select * from (select '' ) as s
version: PostgreSQL 8.3.1, compiled by Visual C++ build 1400
Hi All,
Q1 works just fine, but Q2 fails with:
ERROR: failed to find conversion function from unknown to text
Q2 is a generalization of a huge query we are facing, which we cannot
modify. I don't think this is a 'removed-casts' problem generally faced
in
8.3, but I may be wrong. Will adding some cast resolve this?
yes
postgres=# select '' union all select * from (select ''::text ) as s;
?column?
--
(2 rows)
regards
Pavel Stehule
Best regards,
--
gurjeet[.sin...@enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
--
gurjeet[.sin...@enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
Re: [HACKERS] ERROR: failed to find conversion function from unknown to text
2009/1/6 Gurjeet Singh singh.gurj...@gmail.com:
As I mentioned, we cannot change the query, so adding casts to the query is
not an option. I was looking for something external to the query, like a
CREATE CAST command that'd resolve the issue.
I am sorry, I blind - I tested casting on 8.3.0 and it doesn't work
(but I am have old 8.3)
postgres=# create function unknown2text(unknown) returns text as
$$select $1::text$$ language sql;
CREATE FUNCTION
postgres=# create cast(unknown as text) with function
unknown2text(unknown) as implicit;
CREATE CAST
postgres=# select '' union all select * from (select '' ) as s;
ERROR: stack depth limit exceeded
HINT: Increase the configuration parameter max_stack_depth, after
ensuring the platform's stack depth limit is adequate.
CONTEXT: SQL function unknown2text during startup
SQL function unknown2text statement 1
SQL function unknown2text statement 1
SQL function unknown2text statement 1
SQL function unknown2text statement 1
SQL function unknown2text statement 1
It working on 8.4
postgres=# create cast (unknown as text) with inout as implicit;
CREATE CAST
postgres=# select '' union all select * from (select '' ) as s;
?column?
--
(2 rows)
regards
Pavel Stehule
Best regards,
On Tue, Jan 6, 2009 at 12:00 PM, Pavel Stehule pavel.steh...@gmail.com
wrote:
Hello
2009/1/6 Gurjeet Singh singh.gurj...@gmail.com:
Q1: select '' union all select ''
Q2: select '' union all select * from (select '' ) as s
version: PostgreSQL 8.3.1, compiled by Visual C++ build 1400
Hi All,
Q1 works just fine, but Q2 fails with:
ERROR: failed to find conversion function from unknown to text
Q2 is a generalization of a huge query we are facing, which we
cannot
modify. I don't think this is a 'removed-casts' problem generally faced
in
8.3, but I may be wrong. Will adding some cast resolve this?
yes
postgres=# select '' union all select * from (select ''::text ) as s;
?column?
--
(2 rows)
regards
Pavel Stehule
Best regards,
--
gurjeet[.sin...@enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
--
gurjeet[.sin...@enterprisedb.com
singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] QuickLZ compression algorithm (Re: Inclusion in the PostgreSQL backend for toasting rows)
Robert Haas robertmh...@gmail.com writes: not compressing very small datums ( 256 bytes) also seems smart, since that could end up producing a lot of extra compression attempts, most of which will end up saving little or no space. That was presumably the rationale for the original logic. However experience shows that there are certainly databases that store a lot of compressible short strings. Obviously databases with CHAR(n) desperately need us to compress them. But even plain text data are often moderately compressible even with our fairly weak compression algorithm. One other thing that bothers me about our toast mechanism is that it only kicks in for tuples that are too large. It seems weird that the same column is worth compressing or not depending on what other columns are in the same tuple. If you store a 2000 byte tuple that's all spaces we don't try to compress it at all. But if you added one more attribute we would go to great lengths compressing and storing attributes externally -- not necessarily the attribute you just added, the ones that were perfectly fine previously -- to try to get it under 2k. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's RemoteDBA services! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
