Re: [HACKERS] plperl needs upgrade for Fedora 10
Andrew Dunstan wrote: 2. You have not provided the info I asked for, namely the configure params and the build log. e.g.: My apologies. I missed the attachments with this info. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP] In-place upgrade
Robert Haas napsal(a): Really, what I'd ideally like to see here is a system where the V3 code is in essence error-recovery code. Everything should be V4-only unless you detect a V3 page, and then you error out (if in-place upgrade is not enabled) or jump to the appropriate V3-aware code (if in-place upgrade is enabled). In theory, with a system like this, it seems like the overhead for V4 ought to be no more than the cost of checking the page version on each page read, which is a cheap sanity check we'd be willing to pay for anyway, and trivial in cost. OK. It was original idea to make Convert on read which has several problems with no easy solution. One is that new data does not fit on the page and second big problem is how to convert TOAST table data. Another problem which is general is how to convert indexes... Convert on read has minimal impact on core when latest version is processed. But problem is what happen when you need to migrate tuple form page to new one modify index and also needs convert toast value(s)... Problem is that response could be long in some query, because it invokes a lot of changes and conversion. I think in corner case it could requires converts all index when you request one record. Zdenek -- Zdenek Kotala Sun Microsystems Prague, Czech Republic http://sun.com/postgresql -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
I think we need to distinguish between patches that are clearly not going in, and patches that are not going in in their present form but might be able to be reworked. My suggestion would be that only the first category be moved to the Returned with feedback section, and the others just have their status changed to Waiting for new version or similar. Alternatively, we could create a separate section for patches in this category. ...Robert On Tue, Nov 4, 2008 at 12:22 AM, Brendan Jurd [EMAIL PROTECTED] wrote: On Thu, Sep 18, 2008 at 6:03 AM, Ron Mayer [EMAIL PROTECTED] wrote: The attached patch (1) adds a new GUC called IntervalStyle that decouples interval output from the DateStyle GUC, and (2) adds a new interval style that will match the SQL standards for interval literals when given interval data that meets the sql standard (year-month or date-time only; and no mixed sign). Hi Ron, I've been assigned to do an initial review of your interval patches. I'm going to be reviewing them one at a time, starting with this one (the introduction of the new IntervalStyle GUC). I grabbed the latest version of the patch from the URL posted up on the CF wiki page: http://0ape.com/postgres_interval_patches/stdintervaloutput.patch Nice site you've got set up for the patches, BTW. It certainly makes it all a lot more approachable. On with the review then ... The patch applied cleanly to the latest version of HEAD in the git repository. I was able to build both postgres and the documentation without complaint on x86_64 gentoo. When I ran the regression tests, I got one failure in the new interval tests. Looks like the nonstandard extended format gets a bit confused when the seconds are negative: *** /home/direvus/src/postgres/src/test/regress/expected/interval.out Tue Nov 4 14:46:34 2008 --- /home/direvus/src/postgres/src/test/regress/results/interval.out Tue Nov 4 15:19:53 2008 *** *** 629,634 - interval '1 years 2 months -3 days 4 hours 5 minutes 6.789 seconds'; interval | ?column? --+-- ! +1-2 -3 +4:05:06.789 | -1-2 +3 -4:05:06.789 (1 row) --- 629,634 - interval '1 years 2 months -3 days 4 hours 5 minutes 6.789 seconds'; interval | ?column? --+-- ! +1-2 -3 +4:05:06.789 | -1-2 +3 -4:05:-6.789 (1 row) Otherwise, the feature seemed to behave as advertised. I tried throwing a few bizarre intervals at it, but didn't manage to break anything. The C code has some small stylistic inconsistencies; in some cases the spaces around binary operators are missing (e.g., (fsec0)). See src/backend/utils/adt/datetime.c lines 3691, 3694, 3697, 3729-3731. There are also a lot of function calls missing the space after the argument separator (e.g., sprintf(cp,%d %d:%02d:,mday,hour,min)). Apart from not merging well with the style of the surrounding code, I respectfully suggest that omitting the spaces really does make the code harder to read. The new documentation is good in terms of content, but there are some minor stylistic and spelling cleanups I would suggest. The standard is referred to variously as SQL standard, SQL-standard and SQL Standard in the patch. The surrounding documentation seems to use SQL standard, so that's probably the way to go. These sentences in datatype.sgml are a bit awkward: The postgres style will output intervals that match the style PostgreSQL 8.3 outputed when the DateStyle parameter was set to ISO. The postgres_verbose style will output intervals that match the style PostgreSQL 8.3 outputed when the DateStyle parameter was set to SQL. As far as I know, outputed isn't a word, and singling out 8.3 in particular is a bit misleading, since the statement applies to earlier versions as well. I would go with something more along the lines of: The postgres style will output intervals matching those output by PostgreSQL prior to version 8.4, with the DateStyle parameter set to ISO. Likewise in config.sgml, the patch has: The value postgres will output intervals in a format that matches what old releases had output when the DateStyle was set to 'ISO'. The value postgres_verbose will output intervals in a format that matches what old releases had output when the DateStyle was set to 'SQL'. I don't think old releases is specific enough. Most folks reading the documentation aren't going to know what is meant by old. Better to be precise. Again I would suggest phrasing like ... releases prior to 8.4, with the DateStyle set to That's all the feedback I have for the moment. I hope you found my comments helpful. I'll be setting the status of this patch to Returned with Feedback and wait for your reponses before I move forward with reviewing the other patches. Cheers, BJ -- Sent via
Re: [HACKERS] [WIP] In-place upgrade
OK. It was original idea to make Convert on read which has several problems with no easy solution. One is that new data does not fit on the page and second big problem is how to convert TOAST table data. Another problem which is general is how to convert indexes... Convert on read has minimal impact on core when latest version is processed. But problem is what happen when you need to migrate tuple form page to new one modify index and also needs convert toast value(s)... Problem is that response could be long in some query, because it invokes a lot of changes and conversion. I think in corner case it could requires converts all index when you request one record. I don't think I'm proposing convert on read, exactly. If you actually try to convert the entire page when you read it in, I think you're doomed to failure, because, as you rightly point out, there is absolutely no guarantee that the page contents in their new format will still fit into one block. I think what you want to do is convert the structures within the page one by one as you read them out of the page. The proposed refactoring of ExecStoreTuple will do exactly this, for example. HEAD uses a pointer into the actual buffer for a V4 tuple that comes from an existing relation, and a pointer to a palloc'd structure for a tuple that is generated during query execution. The proposed refactoring will keep these rules, plus add a new rule that if you happen to read a V3 page, you will palloc space for a new V4 tuple that is semantically equivalent to the V3 tuple on the page, and use that pointer instead. That, it seems to me, is exactly the right balance - the PAGE is still a V3 page, but all of the tuples that the upper-level code ever sees are V4 tuples. I'm not sure how far this particular approach can be generalized. ExecStoreTuple has the advantage that it already has to deal with both direct buffer pointers and palloc'd structures, so the code doesn't need to be much more complex to handle this case as well. I think the thing to do is go through and scrutinize all of the ReadBuffer call sites and figure out an approach to each one. I haven't looked at your latest code yet, so you may have already done this, but just for example, RelationGetBufferForTuple should probably just reject any V3 pages encountered as if they were full, including updating the FSM where appropriate. I would think that it would be possible to implement that with almost zero performance impact. I'm happy to look at and discuss the problem cases with you, and hopefully others will chime in as well since my knowledge of the code is far from exhaustive. ...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] Spurious Kerberos error messages
Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: I get the following display now when I connect to a non-running server, all default settings: psql: pg_krb5_init: krb5_cc_get_principal: No credentials cache found could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? Hmm ... a few of the buildfarm machines have failed like that too in recent days, but it's inconsistent (only one or two of the regression tests fail that way, typically). Does yours fail always? Nothing has changed about when it fails, only the extra krb error message before the usual error messages (could not connect, server is starting up) are new. This probably has something to do with Magnus's work on concatenating rather than hiding error messages across multiple passes. I see this on Mac and Linux, so it should be reproducible with any Kerberos-enabled build. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] SQL5 budget
Hi, all. I had preliminary conversation with my employer about implementation of SQL5 [1] without Driven Scene [2]. He needs concrete budget. So i'm asking you to estimate and say, how much will it cost. In particular, speach goes about implemention of slides # 17-42, 47-56, 63, 102-109, 114-143, 147-157, 160-177, 180-197 of sql5.16.4.pdf, and about implementation of http://sql50.euro.ru/site/sql50/en/author/mtd_eng.htm [1] http://sql50.euro.ru/sql5.16.4.pdf Brief description of SQL5 is on http://blogs.ingres.com/technology/2008/07/31/bringing-dbms-in-line-with-modern-communication-requirements-sql2009 or on http://sql50.euro.ru/site/sql50/en/author/resume_eng.htm [2] http://computer20.euro.ru/driven-scene.pdf Brief description of SQL5 is on http://blogs.ingres.com/technology/2008/07/31/new-step-in-office-technologies-driven-scene or on http://computer20.euro.ru/site/computer20/en/author/driven-scene_eng.htm -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] libpq and sslmode=require
In testing an SSL patch, I found that if I use 'sslmode=require' in the libpq connection string, it does not use SSL over a unix-domain socket. libpq should either use SSL (which I don't think it can), or error out, or we should at least document this behavior. -- Bruce Momjian [EMAIL PROTECTED]http://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] SQL5 budget
I had preliminary conversation with my employer about implementation of SQL5 [1] without Driven Scene [2]. He needs concrete budget. So i'm asking you to estimate and say, how much will it cost. In particular, speach goes about implemention of slides # 17-42, 47-56, 63, 102-109, 114-143, 147-157, 160-177, 180-197 of sql5.16.4.pdf, and about implementation of http://sql50.euro.ru/site/sql50/en/author/mtd_eng.htm Changes of two related projects (SQL5 HTML6), not reflected in documentation yet. ===50 in regard to http://sql50.euro.ru/sql5.16.4.pdf (slide #31,etc) in addition to mentioned in pdf-document, statement SELECT ... COMMENTING. extracts additional record from table as first xml-element, attributes of which contain comments, specified for each field earlier by statement COMMENT tab/@fld BY column title; this first xml-element contains additional service xml-attribute '_=comment'. usually comments are specified for html-tables, instead of html-trees -- in regard to http://sql50.euro.ru/sql5.16.4.pdf (slide #18) http://html60.euro.ru/site/html60/en/author/tabfile_eng.htm (Sending of form) http://html60.euro.ru/site/html60/en/author/cube_eng.htm(Sending of form) Obtaining single xml-element (instead of xml-tree) with primary key (PK) for DBMS table (specified in some xml-attribute) means the following: *) if this PK is already in DBMS table, then DBMS *updates* fields of record with this PK *) if this PK is not in table (this usually means, that record with this PK was already deleted), then DBMS *inserts* this xml-element under new PK (obtained from sequence for this PK), i.e. PK will be changed during insertion. -- in regard to http://sql50.euro.ru/sql5.16.4.pdf (slide #31-32, 39(Another output), 40) http://html60.euro.ru/site/html60/en/author/forxml_eng.htm http://html60.euro.ru/site/html60/en/author/quest_eng.htm#webforms_data (data id=records.../data) Each 'select ...' extracts data into LAN or WAN. (instead of 'insert ... select ...' and 'select ... into ...', coping data into internal DBMS table). Each 'select ... union ... select ...' interpreted as single extraction of xml into LAN or WAN. Each extraction of data into LAN or WAN is automatically: *) anticipated by content of field 'beginning' of system table 'SysXmlFormatting' *) ended by content of field 'end' of system table 'SysXmlFormatting' and *) by bament ?newdocument/? (to inform client, e.g. browser, about end of document) P.S. Other field of system table 'SysXmlFormatting' is URL (which specifies browser URL; 'null' means all URL and is applied, only if 'SysXmlFormatting' does not contain record with exact URL). -- in regard to http://sql50.euro.ru/sql5.16.4.pdf (slide #173) Authentification (obtaining username and password, slide #173) must be implemented by baments (look at slides #26-27) and is described nowhere. --other SQL5 additions in addition to mentioned in pdf-document, each 'select ...' (extracting data into LAN/WAN) is savepoint. DBMS rollbacks to this savepoint, having obtained bament ?back/? in addition to mentioned in pdf-document, permissions for field are controlled GRANT/REVOKE SELECT/INSERT/UPDATE ON tab/@fld FOR user1; in addition to mentioned in pdf-document, if table b below is created -- | b1 | b2 | b3 | || | | a1 | a2 | a3 | || || || -- then expression IMPLY b/@b3 as b/@b3/@a2; influence so, that any 'SELECT @b3 FROM b' will extract only one elementary field (@a2) instead of all branch elementary fields (@a1, @a2, @a3), i.e. mentioned statement will be equivalent to 'SELECT @b3/@a2 FROM b'. it's for multi-language applications (@a1, @a2, @a3 contain notes in different languages) DBMS creates separete CSS-file username.css for each user username in local (for DBMS) directory. All accessable database fields (for user username) are listed in this file as ¶fieldname [instead of §fieldname, because DBMS does not know, what fields are service (like @colspan, @rowspan) or are values of properties (like in http://html60.euro.ru/site/html60/en/author/chart_eng.htm), and what fields contain data for visualization - so mark ¶ acts like comment for this xml-attribute), except fields, which are primary keys in database, and which are always specified (and never ignored) as invisible. Fields, calculated upon other fields and don't saved really, specified as calculated by browser upon other fields. CSS-files are available for editing for DBMS administrator. ===60 in regard to http://html60.euro.ru/site/html60/en/author/looker_eng.htm http://sql50.euro.ru/site/sql50/en/author/mtd_eng.htm instead of table name=M action=./scriptM.cgi portion=50 visual=20 id=id1 table name=S action=./scriptS.cgi portion=40 visual=10 master=id1 the following
Re: [HACKERS] patch: array_ndims
Robert Haas wrote: After reading Josh Berkus's email suggesting that the intagg module be dropped, I was wondering what would be required to create a array enumerator (variously called unnest, unroll, array_enum, and, as contemplated by the TODO list, array_to_set). Pavel Stehule's generate_subscripts function provides most of what is needed - however, you need to know the number of dimensions in the array, and it appears we don't have a function to provide that information, at least not in a straightforward fashion. That seems like a pretty useful thing to have anyway, so here's a patch to add it. I have committed your array_ndims function with the addition of a small regression test, and I changed the return type to integer because that is what the rest of the array functions use for dimension information as well. -- 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] Enable pl/python to return records based on multiple OUT params
Hannu Krosing [EMAIL PROTECTED] writes: ... even the code currently in CVS crashes the backend for this py=# create or replace function add_any(in i1 anyelement, in i2 anyelement, out t text) language plpythonu as $$ return i1 + i2 $$; CREATE FUNCTION py=# select * from add_any(1,2); server closed the connection unexpectedly Well, that's just a stupid uninitialized-variable bug :-( 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] Enable pl/python to return records based on multiple OUT params
On Tue, 2008-11-04 at 09:57 -0500, Tom Lane wrote: Hannu Krosing [EMAIL PROTECTED] writes: ... even the code currently in CVS crashes the backend for this py=# create or replace function add_any(in i1 anyelement, in i2 anyelement, out t text) language plpythonu as $$ return i1 + i2 $$; CREATE FUNCTION py=# select * from add_any(1,2); server closed the connection unexpectedly Well, that's just a stupid uninitialized-variable bug :-( there are probably more complex ones, if a ANYELEMENT taking function is used more than one time, with different types of args regards, tom lane -- -- Hannu Krosing http://www.2ndQuadrant.com PostgreSQL Scalability and Availability Services, Consulting and Training -- 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] libpq and sslmode=require
Bruce Momjian wrote: In testing an SSL patch, I found that if I use 'sslmode=require' in the libpq connection string, it does not use SSL over a unix-domain socket. libpq should either use SSL (which I don't think it can), or error out, or we should at least document this behavior. We discussed this before 8.3 already. It might be time to address this now that the SSL support is being redesigned. SSL over Unix-domain sockets with libpq works perfectly fine if you remove the code in libpq and/or the postmaster (forgot which exactly) that thinks that it doesn't work. The issue previously was the libpq defaults to sslmode=prefer and that would impose a noticeable connection initiation overhead on everyone's Unix-domain socket uses. You could make it use SSL in require mode, but it seems weird that prefer mode would end up doing something different than require mode. Maybe Magnus has an opinion on how we could make this fit into the new scheme of things. I assume since we require certificates to be set up now, SSL will by default be off and so using it over Unix-domain sockets when enabled would not be in the common path, which was the objection previously. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP] In-place upgrade
Robert Haas napsal(a): OK. It was original idea to make Convert on read which has several problems with no easy solution. One is that new data does not fit on the page and second big problem is how to convert TOAST table data. Another problem which is general is how to convert indexes... Convert on read has minimal impact on core when latest version is processed. But problem is what happen when you need to migrate tuple form page to new one modify index and also needs convert toast value(s)... Problem is that response could be long in some query, because it invokes a lot of changes and conversion. I think in corner case it could requires converts all index when you request one record. I don't think I'm proposing convert on read, exactly. If you actually try to convert the entire page when you read it in, I think you're doomed to failure, because, as you rightly point out, there is absolutely no guarantee that the page contents in their new format will still fit into one block. I think what you want to do is convert the structures within the page one by one as you read them out of the page. The proposed refactoring of ExecStoreTuple will do exactly this, for example. I see. But Vacuum and other internals function access heap pages directly without ExecStoreTuple. however you point to one idea which I'm currently thinking about it too. There is my version: If you look into new page API it has PageGetHeapTuple. It could do the conversion job. Problem is that you don't have relation info there and you cannot convert data, but transaction information can be converted. I think about HeapTupleData structure modification. It will have pointer to transaction info t_transinfo, which will point to the page tuple for V4. For V3 PageGetHeapTuple function will allocate memory and put converted data here. ExecStoreTuple will finally convert data. Because it know about relation and It does not make sense convert data early. Who wants to convert invisible or dead data. With this approach tuple will be processed same way with V4 without any overhead (they will be small overhead with allocating and free heaptupledata in some places - mostly vacuum). Only multi version access will be driven on page basis. Zdenek -- Zdenek Kotala Sun Microsystems Prague, Czech Republic http://sun.com/postgresql -- 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] libpq and sslmode=require
Bruce Momjian [EMAIL PROTECTED] writes: In testing an SSL patch, I found that if I use 'sslmode=require' in the libpq connection string, it does not use SSL over a unix-domain socket. It's always done that. 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] some strange bugs related to upgrade from 8.1 to 8.3
Hello I started upgrade our databases from 8.1 to 8.3. I found two strange bugs related to tsearch2. a) server crash after creating tsearch2 function (I use tsearch2 contrib from 8.3) (gdb) bt #0 0x003838033075 in raise () from /lib64/libc.so.6 #1 0x003838034be3 in abort () from /lib64/libc.so.6 #2 0x0068db5d in ExceptionalCondition (conditionName=value optimized out, errorType=value optimized out, fileName=value optimized out, lineNumber=value optimized out) at assert.c:57 #3 0x00a6cc79 in plpgsql_HashTableInit () at pl_comp.c:2016 #4 0x00e95d72 in _PG_init () from /usr/lib64/pgsql/plpgsql.so #5 0x00693105 in internal_load_library (libname=0x1fd5c70 /usr/lib64/pgsql/plpgsql.so) at dfmgr.c:296 #6 0x00693506 in load_external_function (filename=value optimized out, funcname=0x1fa8358 plpgsql_call_handler, signalNotFound=1 '\001', filehandle=0x7fff8eb5d678) at dfmgr.c:110 #7 0x004bd1b9 in fmgr_c_validator (fcinfo=value optimized out) at pg_proc.c:509 #8 0x006964e6 in OidFunctionCall1 (functionId=value optimized out, arg1=28155) at fmgr.c:1532 #9 0x004bdab0 in ProcedureCreate (procedureName=0x1f6a430 plpgsql_call_handler, procNamespace=2200, replace=0 '\0', returnsSet=0 '\0', returnType=2280, languageObjectId=13, languageValidator=2247, prosrc=0x1f6a640 plpgsql_call_handler, probin=0x1f6a608 /usr/lib64/pgsql/plpgsql, isAgg=0 '\0', security_definer=0 '\0', isStrict=0 '\0', volatility=118 'v', parameterTypes=0x1fa7a90, allParameterTypes=0, parameterModes=0, parameterNames=0, proconfig=0, procost=1, prorows=0) at pg_proc.c:413 #10 0x00502588 in CreateFunction (stmt=0x1f6a890) at functioncmds.c:785 #11 0x005e3da5 in PortalRunUtility (portal=0x1fc3678, utilityStmt=0x1f6a890, isTopLevel=-1 '�', dest=0x1f6a948, completionTag=0x7fff8eb5e020 ) at pquery.c:1173 #12 0x005e53d5 in PortalRunMulti (portal=0x1fc3678, isTopLevel=value optimized out, dest=0x1f6a948, altdest=0x1f6a948, completionTag=0x7fff8eb5e020 ) at pquery.c:1266 #13 0x005e5c1b in PortalRun (portal=0x1fc3678, count=9223372036854775807, isTopLevel=64 '@', dest=0x1f6a948, altdest=0x1f6a948, completionTag=0x7fff8eb5e020 ) at pquery.c:813 #14 0x005e0a0c in exec_simple_query ( query_string=0x1f69ae8 CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler\nAS '/usr/lib64/pgsql/plpgsql', 'plpgsql_call_handler'\nLANGUAGE c;) at postgres.c:986 #15 0x005e1f67 in PostgresMain (argc=4, argv=value optimized out, username=0x1ed56a8 pavel) at postgres.c:3572 #16 0x005ae96a in BackendRun () at postmaster.c:3207 #17 BackendStartup () at postmaster.c:2830 #18 ServerLoop () at postmaster.c:1274 #19 0x005af685 in PostmasterMain (argc=3, argv=0x1ed1540) at postmaster.c:1029 #20 0x0055f208 in main (argc=3, argv=0x1ed1540) at main.c:188 (gdb) ERROR: function public.lexize(text) does not exist STATEMENT: ALTER FUNCTION public.lexize(text) OWNER TO postgres; ERROR: could not find function parse in file /usr/local/pgsql8.3/lib/tsearch2.so STATEMENT: CREATE FUNCTION parse(oid, text) RETURNS SETOF tokenout --AS '$libdir/tsearch2', 'parse' --LANGUAGE c STRICT; ERROR: function public.parse(oid, text) does not exist STATEMENT: ALTER FUNCTION public.parse(oid, text) OWNER TO postgres; ERROR: could not find function parse_byname in file /usr/local/pgsql8.3/lib/tsearch2.so STATEMENT: CREATE FUNCTION parse(text, text) RETURNS SETOF tokenout --AS '$libdir/tsearch2', 'parse_byname' --LANGUAGE c STRICT; ERROR: function public.parse(text, text) does not exist STATEMENT: ALTER FUNCTION public.parse(text, text) OWNER TO postgres; ERROR: could not find function parse_current in file /usr/local/pgsql8.3/lib/tsearch2.so STATEMENT: CREATE FUNCTION parse(text) RETURNS SETOF tokenout --AS '$libdir/tsearch2', 'parse_current' --LANGUAGE c STRICT; ERROR: function public.parse(text) does not exist STATEMENT: ALTER FUNCTION public.parse(text) OWNER TO postgres; TRAP: FailedAssertion(!(plpgsql_HashTable == ((void *)0)), File: pl_comp.c, Line: 2016) LOG: server process (PID 4672) was terminated by signal 6: Aborted LOG: terminating any other active server processes LOG: all server processes terminated; reinitializing LOG: database system was interrupted; last known up at 2008-11-04 15:01:14 CET LOG: database system was not properly shut down; automatic recovery in progress b) after instalation tsearch2 (8.3) postgresql knows tsvector type template1=# select 'tsvector'::regtype; regtype -- tsvector (1 row) template1=# create table foo(a tsvector); CREATE TABLE template1=# but inside import pg should forget this type and use tsvector only as shell type ERROR: type tsvector is only a shell STATEMENT: CREATE TYPE tsdebug AS ( ts_name text, tok_type text, description text, token text,
Re: [HACKERS] Transactions and temp tables
Emmanuel Cecchet wrote: What's the purpose of checking that a table is empty on prepare? I think I'd feel more comfortable with the approach of only accepting PREPARE TRANSACTIOn if the accessed temp tables have been created and destroyed in the same transaction, to avoid possibly surprising behavior when a temp table is kept locked by a prepared transaction and you try to drop it later in the sesssion, but the patch allows more than that. I guess accessing an existing ON COMMIT DELETE ROWS temp table would also be OK, Yes, I was trying to allow also ON COMMIT DROP and ON COMMIT DELETE ROW. An empty temp table at PREPARE time would be similar to an ON COMMIT DELETE ROW table. I think you'll want to check explicitly that the table is defined with ON COMMIT DELETE ROWS, instead of checking that it's empty. but checking that there's no visible rows in the table doesn't achieve that. If the relation exist but contains no row, is it possible that the table is not empty? What would I need to do to ensure that the table is empty? Yeah, thanks to MVCC, it's possible that the table looks empty to the transaction being prepared, using SnapshotNow, but there's some tuples that are still visible to other transactions. For example: CREATE TEMPORARY TABLE foo (id int4); INSERT INTO foo VALUES (1); begin; DELETE FROM foo; PREPARE TRANSACTION 'foo'; -- doesn't error, because the table is empty, according to SnapshotNow SELECT * FROM foo; -- Still shows the one row, because the deleting transaction hasn't committed yet. I don't think you can just ignore prepared temp relations in findDependentObjects to avoid the lockup at backend exit. It's also used for DROP CASCADE, for example. Do you mean that it will break the DROP CASCADE behavior in general, or that would break the behavior for master/child temp tables? For temp tables, I suppose. The hack in findDependentObjects still isn't enough, anyway. If you have a prepared transaction that created a temp table, the database doesn't shut down: $ bin/pg_ctl -D data start server starting $ LOG: database system was shut down at 2008-11-04 10:27:27 EST LOG: autovacuum launcher started LOG: database system is ready to accept connections $ bin/psql postgres -c begin; CREATE TEMPORARY TABLE temp (id integer); PREPARE TRANSACTION 'foo'; PREPARE TRANSACTION [EMAIL PROTECTED]:~/pgsql.fsmfork$ bin/pg_ctl -D data stop LOG: received smart shutdown request LOG: autovacuum launcher shutting down waiting for server to shut down... failed pg_ctl: server does not shut down By the way, does Postgres support child temp tables? Yes. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP] In-place upgrade
I see. But Vacuum and other internals function access heap pages directly without ExecStoreTuple. Right. I don't think there's any getting around the fact that any function which accesses heap pages directly is going to need modification. The key is to make those modifications as non-invasive as possible. For example, in the case of vacuum, as soon as it detects that a V3 page has been read, it should call a special function whose only purpose in life is to move the data out of that V3 page and onto one or more V4 pages, and return. What you shouldn't do is try to make the regular vacuum code handle both V3 and V4 pages, because that will lead to code that may be slow and will almost certainly be complicated and difficult to maintain. I'll read through the rest of this when I have a bit more time. ...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] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Brendan Jurd wrote: ...Sep 18, 2008... Ron Mayer [EMAIL PROTECTED] wrote: The attached patch (1) adds a new GUC called IntervalStyle that decouples interval output from the DateStyle GUC, and (2) adds a new interval style that will match the SQL standards for interval literals when given interval data that meets the sql standard (year-month or date-time only; and no mixed sign). I've been assigned to do an initial review of your interval patches. I'm going to be reviewing them one at a time, starting with this one (the introduction of the new IntervalStyle GUC). Great! Thanks much! I grabbed the latest version of the patch from the URL posted up on the CF wiki page: http://0ape.com/postgres_interval_patches/stdintervaloutput.patch Nice site you've got set up for the patches, BTW. It certainly makes it all a lot more approachable. Ah. If you're using GIT, you might find it more convenient to pull/merge from http://git.0ape.com/postgresql/ or browse through gitweb: http://git.0ape.com/?p=postgresql;a=shortlog;h=refs/heads/cleanup http://git.0ape.com/git-browser/by-commit.html?r=postgresql though this is the first time I've set up gitweb so it might have rough edges. The patch applied cleanly to the latest version of HEAD in the git repository. I was able to build both postgres and the documentation without complaint on x86_64 gentoo. When I ran the regression tests, I got one failure in the new interval tests. Looks like the nonstandard extended format gets a bit confused when the seconds are negative: Ah yes. Let me guess, HAVE_INT64_TIMESTAMP was defined. I believe the later refactoring patch also avoids that bug; but yes, I obviously should have had it working in this patch. This fix was simple (can be seen on gitweb here: http://tinyurl.com/5fxeyw) and I think I've pushed the updated patches to my website. Once I fix the stylistic points you mentioned below I'll post the resulting patch to the mailing list. Otherwise, the feature seemed to behave as advertised. I tried throwing a few bizarre intervals at it, but didn't manage to break anything. The C code has some small stylistic inconsistencies ...documentation...some minor stylistic and spelling cleanups I would suggest. Totally agree with all your style suggestions. Will send an update a bit later today. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP] In-place upgrade
Zdenek Kotala wrote: Robert Haas napsal(a): Really, what I'd ideally like to see here is a system where the V3 code is in essence error-recovery code. Everything should be V4-only unless you detect a V3 page, and then you error out (if in-place upgrade is not enabled) or jump to the appropriate V3-aware code (if in-place upgrade is enabled). In theory, with a system like this, it seems like the overhead for V4 ought to be no more than the cost of checking the page version on each page read, which is a cheap sanity check we'd be willing to pay for anyway, and trivial in cost. OK. It was original idea to make Convert on read which has several problems with no easy solution. One is that new data does not fit on the page and second big problem is how to convert TOAST table data. Another problem which is general is how to convert indexes... We've talked about this many times before, so I'm sure you know what my opinion is. Let me phrase it one more time: 1. You *will* need a function to convert a page from old format to new format. We do want to get rid of the old format pages eventually, whether it's during VACUUM, whenever a page is read in, or by using an extra utility. And that process needs to online. Please speak up now if you disagree with that. 2. It follows from point 1, that you *will* need to solve the problems with pages where the data doesn't fit on the page in new format, as well as converting TOAST data. We've discussed various solutions to those problems; it's not insurmountable. For the data doesn't fit anymore problem, a fairly simple solution is to run a pre-upgrade utility in the old version, that reserves some free space on each page, to make sure everything fits after converting to new format. For TOAST, you can retoast tuples when the heap page is read in. I'm not sure what the problem with indexes is, but you can split pages if necessary, for example. Assuming everyone agrees with point 1, could we focus on these issues? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Spurious Kerberos error messages
Peter Eisentraut [EMAIL PROTECTED] writes: Nothing has changed about when it fails, only the extra krb error message before the usual error messages (could not connect, server is starting up) are new. This probably has something to do with Magnus's work on concatenating rather than hiding error messages across multiple passes. I see this on Mac and Linux, so it should be reproducible with any Kerberos-enabled build. Ah ... I had to try it on a machine *without* a credentials cache to see something funny ;-) What's happening is that pg_fe_getauthname - pg_krb5_authname - pg_krb5_init fails and sets an error message in conn-errorMessage, which we don't care about because we will get the username from pqGetpwuid if Kerberos can't help us. But because of the concatenation change, this gets added onto the (unrelated) later failure message. I'm tempted to say that this code path simply shouldn't be setting errorMessage at all. Alternatively we could have pg_fe_getauthname clear out errorMessage upon successfully fetching a non-Kerberized username ... but that would lose anything previously put into errorMessage. (In which connection it seems like a bad thing that pg_krb5_init uses printfPQExpBuffer rather than appendPQExpBuffer.) Thoughts? 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] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Ah. And one final question regarding functionality. It seems to me that the last remaining place where we input a SQL-2008 standard literal and do something different from what the standard suggests is with the string: '-1 2:03:04' The standard seems to say that the - affects both the days and hour/min/sec part; while PostgreSQL historically, and the patch as I first submitted it only apply the negative sign to the days part. IMHO when the IntervalStyle GUC is set to sql_standard, it'd be better if the parsing of this literal matched the standard. We already have the precedent where DateStyle is used to interpret otherwise ambiguous output. If the IntervalStyle is set to anything other than sql_standard we'll keep parsing them the old way; so I think backwards compatibility issues would be minimized. And those using the sql_standard mode are likely to be standards fanatics anyway, and would probably appreciate following the standard rather than the backward compatible mode. Thoughts? I have a version of each alternative working here, and I'd be happy to submit the final patch either way. -- 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] Synchronous replication patch v1
Fujii Masao wrote: On Fri, Oct 31, 2008 at 11:12 PM, Heikki Linnakangas [EMAIL PROTECTED] wrote: AFAICS, there's no security, at all. Anyone that can log in, can become a WAL sender, and receive all WAL for the whole cluster. One simple solution is to define the database only for replication. In this solution, we can handle the authentication for replication like the usual database access. That is, pg_hba.conf, the cooperation with a database role, etc are supported also in replication. So, a user can set up the authentication rules easily. You mean like a pseudo database name in pg_hba.conf, and in the startup message, that actually means connect for replication? Yeah, something like that sounds reasonable to me. ISTM that there is no advantage which separates authentication for replication from the existing mechanism. Agreed. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decouplingDateStyle from IntervalStyle
Ron Mayer [EMAIL PROTECTED] wrote: It seems to me that the last remaining place where we input a SQL-2008 standard literal and do something different from what the standard suggests is with the string: '-1 2:03:04' The standard seems to say that the - affects both the days and hour/min/sec part; Agreed. while PostgreSQL historically, and the patch as I first submitted it only apply the negative sign to the days part. IMHO when the IntervalStyle GUC is set to sql_standard, it'd be better if the parsing of this literal matched the standard. We already have the precedent where DateStyle is used to interpret otherwise ambiguous output. If the IntervalStyle is set to anything other than sql_standard we'll keep parsing them the old way; so I think backwards compatibility issues would be minimized. And those using the sql_standard mode are likely to be standards fanatics anyway, and would probably appreciate following the standard rather than the backward compatible mode. Thoughts? I think it would be good to be able to configure PostgreSQL such that it didn't take standards-compliant literals and silently treat them in a non-standard way. What you propose here seems sane to me, but if someone objects, it would be good for some other value or other GUC to provide compliant behavior. -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] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Ron Mayer [EMAIL PROTECTED] writes: Ah. And one final question regarding functionality. It seems to me that the last remaining place where we input a SQL-2008 standard literal and do something different from what the standard suggests is with the string: '-1 2:03:04' The standard seems to say that the - affects both the days and hour/min/sec part; while PostgreSQL historically, and the patch as I first submitted it only apply the negative sign to the days part. IMHO when the IntervalStyle GUC is set to sql_standard, it'd be better if the parsing of this literal matched the standard. Then how would you input a value that had different signs for the day and the h/m/s? I don't think you can't is an acceptable answer there, because it would mean that interval_out has to fail on such values when IntervalStyle is sql_standard. Which is very clearly not gonna do. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP] In-place upgrade
We've talked about this many times before, so I'm sure you know what my opinion is. Let me phrase it one more time: 1. You *will* need a function to convert a page from old format to new format. We do want to get rid of the old format pages eventually, whether it's during VACUUM, whenever a page is read in, or by using an extra utility. And that process needs to online. Please speak up now if you disagree with that. Well, I just proposed an approach that doesn't work this way, so I guess I'll have to put myself in the disagree category, or anyway yet to be convinced. As long as you can move individual tuples onto new pages, you can eventually empty V3 pages and reinitialize them as new, empty V4 pages. You can force that process along via, say, VACUUM, but in the meantime you can still continue to read the old pages without being forced to change them to the new format. That's not the only possible approach, but it's not obvious to me that it's insane. If you think it's a non-starter, it would be good to know why. ...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] Bitmap Indexes patch
Simon Riggs [EMAIL PROTECTED] writes: On Mon, 2008-11-03 at 23:28 +, Simon Riggs wrote: On Mon, 2008-11-03 at 17:37 -0500, Greg Stark wrote: There are a lot of comments in the code which imply that vacuuming is not implemented but in fact from what I can see it is -- sort of. It does rewrite the bitmap in bmbulkdelete but it doesn't have to rebuild the index from scratch. Are the comments out of date or am i misunderstanding them or the code? How complete is the vacuum implementation? As I understood it, complete. Looking at the code, it looks like my understanding was complete-ly wrong and your comments seem accurate. What I would appreciate is a README explaining how vacuum and vacuum full work. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres 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: Bitmap Indexes patch (was Re: [HACKERS] Bitmap Indexes: request for feedback)
On Mon, Nov 03, 2008 at 04:53:28PM -0700, Vladimir Sitnikov wrote: I wish to focus on the performance aspect of the patch, however, it turned out there are major issues with functionality: the index stores wrong tids inside :( I really would love to fix that issue and have a chance to validate the performance. Unfortunately, I have spent more than a day with almost void success. This can be helpful for us to explain one of the two open issues that we mentioned at submission time (meanwhile we have just fixed the other one): On Sat, Nov 01, 2008 at 01:01:54AM +0100, Gianni Ciolli wrote: * Our workaround for HOT tuples has still one bug; we are currently working on it and we expect to fix it soon. This bug can be reproduced by looking at the rows column of the performance test. As for the other problem: On Mon, Nov 03, 2008 at 05:37:24PM -0500, Greg Stark wrote: There are a lot of comments in the code which imply that vacuuming is not implemented but in fact from what I can see it is -- sort of. It does rewrite the bitmap in bmbulkdelete but it doesn't have to rebuild the index from scratch. Are the comments out of date or am i misunderstanding them or the code? How complete is the vacuum implementation? This morning I looked at that part of the code, and I found that indeed the vacuum implementation has a lack that we didn't notice. After refactoring we had made some tests which suggested that vacuum was working, but now I realize that in the hurry we missed something. Now, the point is that this VACUUM problem might need more work than we expected, and that it might just be too much work for a review phase; so, despite of the interest that showed up regarding this feature, I will understand if the decision will be to withdraw the patch from this Commitfest and postpone it for the next development phase. Thank you to everyone for your remarks, Dr. Gianni Ciolli - 2ndQuadrant Italia PostgreSQL Training, Services and Support [EMAIL PROTECTED] | www.2ndquadrant.it -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Extending pg_class info + more flexible TOAST chunk size
Robert Haas napsal(a): Zdenek, It seems like there is general agreement that this patch needs some changes before being considered for application. Is that correct? http://archives.postgresql.org/pgsql-hackers/2008-11/msg00049.php Are you planning to send a new version for this CommitFest, or is this 8.5 material at this point? Yes, I plan to do it. 8.5 is too late for this change. Thanks Zdenek -- Zdenek Kotala Sun Microsystems Prague, Czech Republic http://sun.com/postgresql -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP] In-place upgrade
That's sane *if* you can guarantee that only negligible overhead is added for accessing data that is in the up-to-date format. I don't think that will be the case if we start putting version checks into every tuple access macro. Yes, the point is that you'll read the page as V3 or V4, whichever it is, but if it's V3, you'll convert the tuples to V4 format before you try to doing anything with them (for example by modifying ExecStoreTuple to copy any V3 tuple into a palloc'd buffer, which fits nicely into what that function already does). ...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] some strange bugs related to upgrade from 8.1 to 8.3
Pavel Stehule [EMAIL PROTECTED] writes: I started upgrade our databases from 8.1 to 8.3. I found two strange bugs related to tsearch2. Did you follow the advice here: http://www.postgresql.org/docs/8.3/static/textsearch-migration.html a) server crash after creating tsearch2 function (I use tsearch2 contrib from 8.3) I couldn't reproduce that with the script you gave. 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] pre-MED
Alex Hunsaker [EMAIL PROTECTED] writes: On Thu, Oct 30, 2008 at 05:16, Tom Lane [EMAIL PROTECTED] wrote: Surely they all have a way to call a SQL function that returns text. Sure but when you call that function you get *that* functions qualifier. And unless there already is a way to grab the parent query qualifiers, the PL specific hacks seem not so bad and very similar to how we have to build trigger arguments for every PL already. The PL hacks are awful :-(, not least because that path leads to randomly different syntax in each PL for exactly the same functionality. As I see it, there are two big problem areas in this patch: 1. How to get the information about restriction clauses to the place where we'd use it; 2. What to do with it once we've got it. We already went over #2 in some detail: the existing ruleutils.c code just isn't very suitable for this purpose. This could probably be worked around, eg drop restriction clauses that contain subplans or outer-relation variables; though I'm quite unsure about the issue of which names to use for the column variables. As for #1, the patch proposes to pass the Plan node (more or less, but that's the key thing) via a new field in ReturnSetInfo. The first problem with that is that it only makes the information available to set-returning functions occurring at the top level of a FunctionScan node. The second problem is that any layer of code we want to pass the information down through has to explicitly know about it, and we have to invent some new API for each one of those layers. ISTM that a saner way to approach this would be to set up a global way to find out what's the currently executing Plan node?. Then this particular problem could be solved by just grabbing the qual field from that node. Infrastructure like that could be useful for instrumentation and debugging purposes as well. I'm not sure about the overhead involved, though. The obvious implementation would add three lines of code to ExecProcNode: PlanState *save_ActivePlanNode = ActivePlanNode; ActivePlanNode = node; ... ActivePlanNode = save_ActivePlanNode; which doesn't seem like it would amount to anything compared to the total execution of a plan node, but we do know that ExecProcNode is a hot spot in some usages. 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] Patch for ALTER DATABASE WITH TABLESPACE
--On Samstag, Oktober 25, 2008 23:50:47 +0200 Guillaume Lelarge [EMAIL PROTECTED] wrote: Hi, Here is my patch to add the ALTER DATABASE WITH TABLESPACE statement. It is part of the TODO list. It intends to allow the move of all relations of a database in its new default tablespace. Comments welcome. I had a first look on this and in my opinion the patch looks reasonable. I moved the usage of heap_modifytuple() to the new heap_modify_tuple() API (see attached new diff) and did other minor cleanups. However, i'm not satisfied with the syntax, which is currently ALTER DATABASE name TABLESPACE foo. We use all over the place SET TABLESPACE (e.g. for tables and indexes) and SET SCHEMA for namespaces even, so this looks inconsistent. However, hacking this requires a little bit more parser-foo, a quick hack shows reduce conflicts due to SetResetClause rule. So what do we want in this case? I did some minor additions in the docs as well. -- Thanks Bernd alterdb_tablespace_v2.patch.bz2 Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Tom Lane wrote: Ron Mayer [EMAIL PROTECTED] writes: Ah. And one final question regarding functionality. It seems to me that the last remaining place where we input a SQL-2008 standard literal and do something different from what the standard suggests is with the string: '-1 2:03:04' The standard seems to say that the - affects both the days and hour/min/sec part; while PostgreSQL historically, and the patch as I first submitted it only apply the negative sign to the days part. IMHO when the IntervalStyle GUC is set to sql_standard, it'd be better if the parsing of this literal matched the standard. Then how would you input a value that had different signs for the day and the h/m/s? I don't think you can't is an acceptable answer there, because it would mean that interval_out has to fail on such values when IntervalStyle is sql_standard. Which is very clearly not gonna do. In the patch I submitted: -1 +2:03:04 always means negative day, positive hours/min/sec +1 -2:03:04 always means positive day, negative hours/min/sec When given a non-standard interval value, EncodeInterval is always outputting all the signs (+ and -) to force it to be unambiguous. -- test a couple non-standard interval values too SELECT interval '1 years 2 months -3 days 4 hours 5 minutes 6.789 seconds', - interval '1 years 2 months -3 days 4 hours 5 minutes 6.789 seconds'; interval | ?column? --+-- +1-2 -3 +4:05:06.789 | -1-2 +3 -4:05:06.789 (1 row) -- 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] gram.y = preproc.y
Michael Meskes [EMAIL PROTECTED] writes: On Mon, Nov 03, 2008 at 07:10:01PM -0500, Tom Lane wrote: (You might need to compress the files if the message comes to more than 100-some KB. Also, given that preproc.y can be generated so easily, it could just be left out.) I did this this time. The file is just 84K. Came through this time, thanks. I'm quite unhappy with the invasiveness of the proposed gram.y changes. The @ECPG annotations are bad enough, but why are you changing actual productions? I'm not entirely convinced that the backend still parses exactly what it did before. It strikes me that most if not all of the @ECPGINCLUDE annotations ought to be unnecessary given that you've carefully identified each chunk of ecpg.addons. The substitution script ought to be able to match those annotations to the input for itself. FWIW, I'm also pretty firmly convinced that awk was the wrong choice for implementing this script... 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] Patch for ALTER DATABASE WITH TABLESPACE
Bernd Helmle a écrit : --On Samstag, Oktober 25, 2008 23:50:47 +0200 Guillaume Lelarge [EMAIL PROTECTED] wrote: Here is my patch to add the ALTER DATABASE WITH TABLESPACE statement. It is part of the TODO list. It intends to allow the move of all relations of a database in its new default tablespace. Comments welcome. I had a first look on this and in my opinion the patch looks reasonable. I moved the usage of heap_modifytuple() to the new heap_modify_tuple() API (see attached new diff) and did other minor cleanups. OK. However, i'm not satisfied with the syntax, which is currently ALTER DATABASE name TABLESPACE foo. We use all over the place SET TABLESPACE (e.g. for tables and indexes) and SET SCHEMA for namespaces even, so this looks inconsistent. However, hacking this requires a little bit more parser-foo, a quick hack shows reduce conflicts due to SetResetClause rule. So what do we want in this case? My first intent was to use SET TABLESPACE. But the other parameter available in the ALTER DATABASE statement use the WITH syntax. So, to be coherent with the actual ALTER DATABASE statement, I used the WITH syntax. I know this is not coherent with ALTER TABLE, but it is with ALTER DATABASE. Anyway, if many think I need to change this, I'll try it. I did some minor additions in the docs as well. Thanks for your review. -- Guillaume. http://www.postgresqlfr.org http://dalibo.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] Enable pl/python to return records based on multiple OUT params
On Sat, Nov 1, 2008 at 7:52 AM, Hannu Krosing [EMAIL PROTECTED] wrote: On Sat, 2008-11-01 at 06:13 +0200, Hannu Krosing wrote: attached is a patch which enables plpython to recognize function with multiple OUT params as returning a record Overrides previous patch. Fixed some bugs, added regression tests. Hi Hannu: I was wondering if it would be possible to get plpython to convert IN parameters of type ARRAY to Python lists? I see some example functions here [1], but it would be nice if it was done automatically. David 1. http://archives.postgresql.org/pgsql-general/2007-01/msg01417.php -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] PageGetTempPage cleanup
Tom Lane napsal(a): Zdenek Kotala [EMAIL PROTECTED] writes: I attach patch which cleans up code around PageGetTempPage. These changes were discussed here: http://archives.postgresql.org/pgsql-hackers/2008-08/msg00102.php Applied with a minor change: instead of inventing Page PageGetTempPage(Page page, bool copy) I split it into two functions Page PageGetTempPage(Page page) Page PageGetTempPageCopy(Page page) I don't see any advantage to having the single function, because it doesn't seem like any calling code path would be likely to want both behaviors depending on some condition. Moreover, the way you had it meant that we'd be replacing Page PageGetTempPage(Page page, Size specialSize); with Page PageGetTempPage(Page page, bool copy); which seems risky to me. If someone failed to update code that was meant to call the old API, they'd get no warning about it --- at least not in any C compiler I'm familiar with. Changing the number of arguments guarantees a compile error for un-updated code. Agree. Just a question you sometime argue that somebody should uses this interface for external module. Is there any clue which function is used and which not? Should we create something like core API description which will be stable? Zdenek -- Zdenek Kotala Sun Microsystems Prague, Czech Republic http://sun.com/postgresql -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP] In-place upgrade
Robert Haas [EMAIL PROTECTED] writes: Well, I just proposed an approach that doesn't work this way, so I guess I'll have to put myself in the disagree category, or anyway yet to be convinced. As long as you can move individual tuples onto new pages, you can eventually empty V3 pages and reinitialize them as new, empty V4 pages. You can force that process along via, say, VACUUM, but in the meantime you can still continue to read the old pages without being forced to change them to the new format. That's not the only possible approach, but it's not obvious to me that it's insane. If you think it's a non-starter, it would be good to know why. That's sane *if* you can guarantee that only negligible overhead is added for accessing data that is in the up-to-date format. I don't think that will be the case if we start putting version checks into every tuple access macro. 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] plperl needs upgrade for Fedora 10
2008/11/4 Andrew Dunstan [EMAIL PROTECTED]: Andrew Dunstan wrote: 2. You have not provided the info I asked for, namely the configure params and the build log. e.g.: My apologies. I missed the attachments with this info. no problem Pavel cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] libpq and sslmode=require
Peter Eisentraut wrote: Bruce Momjian wrote: In testing an SSL patch, I found that if I use 'sslmode=require' in the libpq connection string, it does not use SSL over a unix-domain socket. libpq should either use SSL (which I don't think it can), or error out, or we should at least document this behavior. We discussed this before 8.3 already. It might be time to address this now that the SSL support is being redesigned. SSL over Unix-domain sockets with libpq works perfectly fine if you remove the code in libpq and/or the postmaster (forgot which exactly) that thinks that it doesn't work. The issue previously was the libpq defaults to sslmode=prefer and that would impose a noticeable connection initiation overhead on everyone's Unix-domain socket uses. You could make it use SSL in require mode, but it seems weird that prefer mode would end up doing something different than require mode. Maybe Magnus has an opinion on how we could make this fit into the new scheme of things. I assume since we require certificates to be set up now, SSL will by default be off and so using it over Unix-domain sockets when enabled would not be in the common path, which was the objection previously. Yep, the problem is that sslmode doesn't have any way to specify if we want unix domain sockets to behave differently from tcp sockets, and our default for sslmode makes that even worse. -- Bruce Momjian [EMAIL PROTECTED]http://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] Signal handling patch (v2) for Synch Rep
On Tue, 2008-11-04 at 21:04 +0900, Fujii Masao wrote: To be reviewed easily, I'm splitting Synch Rep patch into some pieces. Great idea. I'll be doing that also. -- 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] Patch for ALTER DATABASE WITH TABLESPACE
Guillaume Lelarge [EMAIL PROTECTED] writes:
Bernd Helmle a écrit :
However, i'm not satisfied with the syntax, which is currently ALTER
DATABASE name TABLESPACE foo. We use all over the place SET TABLESPACE
(e.g. for tables and indexes) and SET SCHEMA for namespaces even, so
this looks inconsistent. However, hacking this requires a little bit
more parser-foo, a quick hack shows reduce conflicts due to
SetResetClause rule. So what do we want in this case?
My first intent was to use SET TABLESPACE. But the other parameter
available in the ALTER DATABASE statement use the WITH syntax. So, to be
coherent with the actual ALTER DATABASE statement, I used the WITH syntax.
FWIW, bison seems perfectly happy with this:
AlterDatabaseStmt:
ALTER DATABASE database_name opt_with alterdb_opt_list
{
AlterDatabaseStmt *n =
makeNode(AlterDatabaseStmt);
n-dbname = $3;
n-options = $5;
$$ = (Node *)n;
}
+ | ALTER DATABASE database_name SET TABLESPACE name
+{
+ AlterDatabaseStmt *n =
makeNode(AlterDatabaseStmt);
+ n-dbname = $3;
+ ...
+ $$ = (Node *)n;
+}
;
Not sure what Bernd tried exactly, but it can be done.
I see the point about the parallel to CREATE DATABASE, but on the other
hand we also have ALTER DATABASE SET for parameters. I suspect people
are more likely to expect the SET syntax.
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] Probable problem with pg_standby
Fujii Masao wrote: On Tue, Nov 4, 2008 at 8:09 PM, Detlef Ulherr [EMAIL PROTECTED] wrote: All I did was forcing the primary in a recovery to generate a new timeline. The installed version was 8.3.4, but the problem is the same with earlier versions as well. It occurred in 8.2 also. this problem is reproducible all the times. For my agent code I implemented a workaround which guarantees that during a resilvering process the primary and the standby start at t the same timeline. But my feeling is that the standby should go to the same timeline as the primary when he receives the history file without disruption, and by all means it should never stop the recovery unmotivated. This will make a full synchronization necessary and in times of larger databases, this may cause major downtimes. I agree with you only if normal archive recovery case (not specified recovery_target_xid/time). But, in point-in-time recovery case, the standby cannot continue to redo without stopping. DBA has to reconstruct the standby (get new online-backup with new timeline ID, locate it on the standby and restart recovery). Or, we should deal with normal archive recovery and point-in-time one separately? Regards, Agreed, a point in time recovery can send the primary behind the standby, but this should not happen with a normal archive recovery, so separating the two cases will be a big improvement. A meaningful error message in the log will help the poor dba, currently there is nothing in the standby's log. It just stops the recovery. In my case it was a normal archive recovery, and definitely no point in time recovery. Regards, -- * Detlef Ulherr Staff Engineer Tel: (++49 6103) 752-248 Availability EngineeringFax: (++49 6103) 752-167 Sun Microsystems GmbH Amperestr. 6 mailto:[EMAIL PROTECTED] 63225 Langenhttp://www.sun.de/ * Sitz der Gesellschaft: Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Amtsgericht Muenchen: HRB 161028 Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering * -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [PATCH] Cleanup of PLpgSQL_recfield
While looking to add some functionality to PL/pgSQL, I found that the rfno member of the PLpgSQL_recfield structure is unused. This patch is just a cleanup and doesn't seem along the same lines as the patches in CommitFest... should I add it to the wiki anyway? -- Jonah H. Harris, Senior DBA myYearbook.com plpgsql_unused_recrfno.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for ALTER DATABASE WITH TABLESPACE
--On Dienstag, November 04, 2008 14:56:44 -0500 Tom Lane [EMAIL PROTECTED] wrote: [...] Not sure what Bernd tried exactly, but it can be done. Cool, i didn't recognize the obvious possibility to add a separate rule for this. I've just extended the alterdb_opt_item with SET TABLESPACE, which lead to a shift/reduce. I see the point about the parallel to CREATE DATABASE, but on the other hand we also have ALTER DATABASE SET for parameters. I suspect people are more likely to expect the SET syntax. Yes, that seems logical to me, too. So i think we should go for it. Guillaume? -- Thanks Bernd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for ALTER DATABASE WITH TABLESPACE
Bernd Helmle a écrit : --On Dienstag, November 04, 2008 14:56:44 -0500 Tom Lane [EMAIL PROTECTED] wrote: [...] Not sure what Bernd tried exactly, but it can be done. Cool, i didn't recognize the obvious possibility to add a separate rule for this. I've just extended the alterdb_opt_item with SET TABLESPACE, which lead to a shift/reduce. I see the point about the parallel to CREATE DATABASE, but on the other hand we also have ALTER DATABASE SET for parameters. I suspect people are more likely to expect the SET syntax. Yes, that seems logical to me, too. So i think we should go for it. Guillaume? I'm OK for this. I also think it's a better way, more logical to do it. Should I provide a complete new patch with Bernd's and Tom's changes? -- Guillaume. http://www.postgresqlfr.org http://dalibo.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] [PATCHES] updated hash functions for postgresql v1
Just interested if you repeat your tests not with cracklib-dict,
but using 8-bit words. From our experience we found many hash functions
are optimized for 7-bit words and produce too many collisions
for 8-bit words. That's why we use crc32.
Oleg
On Tue, 4 Nov 2008, Kenneth Marshall wrote:
Sorry about the delay for this update to the new hash
index implementation. I was trying to get the WAL logging
in place and forgot to post the actual patch. The WAL
for hash indexes will need to wait for 8.5, but I did
want to add back in the piece of the Bob Jenkins 2006
hash function that was stripped out of the initial
patch on application due to concerns about the randomness
of the resulting hash values. Here is a re-post of my
initial findings comparing the old/new Jenkins hash
from lookup2 and lookup3. I have added a third column
containing the results for the hash_any() resulting
from the attached patch as well as simple timing test
for a DB reindex both before and after patching.
Also attached is a simple documentation patch updating
the note attached to the hash index description.
Regards,
Ken
Hi,
I have finally had a chance to do some investigation on
the performance of the old hash mix() function versus
the updated mix()/final() in the new hash function. Here
is a table of my current results for both the old and the
new hash function. In this case cracklib refers to the
cracklib-dict containing 1648379 unique words massaged
in various ways to generate input strings for the hash
functions. The result is the number of collisions in the
hash values generated.
hash inputoldnew newv2
-------- -
cracklib 338316 338
cracklib x 2 (i.e. clibclib) 305319 300
cracklib x 3 (clibclibclib) 323329 315
cracklib x 10 302310 329
cracklib x 100350335 298
cracklib x 1000 314309 315
cracklib x 100 truncated to char(100) 311327 320
uint32 from 1-1648379 309319 347
(uint32 1-1948379)*256309314 304
(uint32 1-1948379)*16 310314 324
auint32 (i.e. a1,a0002...) 320321 312
uint32uint32 (i.e. uint64)321287 309
The different result columns are old = Jenkins 1996 hash
function(lookup2.c), new = Jenkins 2006 hash function
(lookup3.c), and newv2 = adaptation of current hash_any()
to incorporate the separate mix()/final() functions. As
you can see from the results, spliting the mix() and final()
apart does not result in any perceptible loss of randomness
in the hash assignment. I also ran a crude timing for a
reindex of the following database:
CREATE TABLE dict (word text);
CREATE INDEX wordhash ON dict USING hash (word);
INSERT INTO dict (word) VALUES('s;lhkjdpyoijxfg;lktjgh;sdlhkjo');
INSERT INTO dict (SELECT MAX(word)||MAX(word) FROM dict);
... (21 times)
REINDEX TABLE
...
The average time to reindex the table using our current
hash_any() without the separate mix()/final() was 1696ms
and 1482ms with the separate mix()/final() stages giving
almost 13% better performance for this stupid metric.
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Brendan Jurd wrote:
...Sep 18, 2008...Ron Mayer [EMAIL PROTECTED] wrote:
(1) ...GUC called IntervalStyle...
(2) ...interval style that will match the SQL standards...
...an initial review...
When I ran the regression tests, I got one failure in the new interval
Fixed, and I did a bit more testing both with and without HAVE_INT64_TIMESTAMP.
The C code has some small stylistic inconsistencies; ...
... spaces around binary operators are missing (e.g., (fsec0)).
Thanks. Fixed these.
...function calls missing the space after the argument separator...
I think I fixed all these now too.
The new documentation is good in terms of content, but there are some
minor stylistic and spelling cleanups I would suggest.
...variously...SQL standard, SQL-standard and SQL Standard...
Got it. There are a few inconsistencies elsewhere in the file
talking about other data types. I wonder if I should fix those
as well.
These sentences in datatype.sgml are a bit awkward ...
I would go with something more along the lines of...
Yes. Thanks for the better wording.
I don't think old releases is specific enough.
Yup - fixed that too.
That's all the feedback I have for the moment. I hope you found my
comments helpful. I'll be setting the status of this patch to
Returned with Feedback and wait for your responses before I move
forward with reviewing the other patches.
Great. I've tried to update the style on my remaining patches as well.
In addition, I've added to the docs describing how I use
explicit '+' and '-' signs to disambiguate the mixed-sign
non-standard intervals when in the sql_standard mode.
As before the 3 patches are at: http://0ape.com/postgres_interval_patches/
and http://git.forensiclogic.com/postgresql/ and
http://git.forensiclogic.com/?p=postgresql;a=shortlog;h=refs/heads/cleanup
I'm attaching the patch dealing with sql standard intervals here for the
archives.
*** a/doc/src/sgml/config.sgml
--- b/doc/src/sgml/config.sgml
***
*** 4013,4018 SET XML OPTION { DOCUMENT | CONTENT };
--- 4013,4056
/listitem
/varlistentry
+ varlistentry id=guc-intervalstyle xreflabel=IntervalStyle
+ termvarnameIntervalStyle/varname (typestring/type)/term
+ indexterm
+primaryvarnameIntervalStyle/ configuration parameter/primary
+ /indexterm
+ listitem
+para
+ Sets the display format for interval values.
+ The value literalsql_standard/ will produce
+ output matching acronymSQL/acronym standard
+ interval literals for values that conform to the
+ acronymSQL/acronym standard (either year-month
+ only or date-time only; and no mixing of positive
+ and negative components).
+
+ The value literalpostgres/ will produce output
+ matching PostgreSQL releases prior to 8.4
+ when the xref linkend=guc-datestyle
+ parameter was set to literalISO/.
+
+ The value literalpostgres_verbose/ will produce output
+ matching PostgreSQL releases prior to 8.4
+ when the xref linkend=guc-datestyle
+ parameter was set to literalSQL/.
+/para
+para
+ The IntervalStyle GUC also affects the interpretation
+ of one ambiguous interval literal input. In SQL 2008
+ the negative sign in the interval literal '-1 2:03:04'
+ applies to both the days and hour/minute/second parts.
+ PostgreSQL traditionally only applied the negative
+ sign to the days part. If IntervalStyle is set to
+ literalsql_standard/literal it will follow the standard
+ otherwise it uses the traditional postgres interpretation.
+/para
+ /listitem
+ /varlistentry
+
varlistentry id=guc-timezone xreflabel=timezone
termvarnametimezone/varname (typestring/type)/term
indexterm
*** a/doc/src/sgml/datatype.sgml
--- b/doc/src/sgml/datatype.sgml
***
*** 1962,1968 January 8 04:05:06 1999 PST
a combination of years and months can be specified with a dash;
for example literal'200-10'/ is read the same as literal'200 years
10 months'/. (These shorter forms are in fact the only ones allowed
! by the SQL standard.)
/para
para
--- 1962,1968
a combination of years and months can be specified with a dash;
for example literal'200-10'/ is read the same as literal'200 years
10 months'/. (These shorter forms are in fact the only ones allowed
! by the acronymSQL/acronym standard.)
/para
para
***
*** 2213,2218 January 8 04:05:06 1999 PST
--- 2213,2310
/para
/sect2
+sect2 id=interval-output
+ titleInterval Output/title
+
+ indexterm
+ primaryinterval/primary
+ secondaryoutput format/secondary
+ seealsoformatting/seealso
+ /indexterm
+
+ para
+
Re: [HACKERS] BufferAccessStrategy for bulk insert
Robert Haas [EMAIL PROTECTED] writes: Patch resnapped to HEAD, with straightforward adjustments to compensate for Heikki's changes to the ReadBuffer interface. See attached. I looked this over a bit. A couple of suggestions: 1. You could probably simplify life a bit by treating the BulkInsertState as having an *extra* pin on the buffer, ie, do IncrBufferRefCount when saving a buffer reference in BulkInsertState and ReleaseBuffer when removing one. Changing a buffer's local pin count from 1 to 2 or back again is quite cheap, so you wouldn't need to special-case things to avoid the existing pin and release operations. For instance this diff hunk goes away: *** *** 1963,1969 END_CRIT_SECTION(); ! UnlockReleaseBuffer(buffer); /* * If tuple is cachable, mark it for invalidation from the caches in case --- 1987,1996 END_CRIT_SECTION(); ! /* Release the lock, but keep the buffer pinned if doing bulk insert. */ ! LockBuffer(buffer, BUFFER_LOCK_UNLOCK); ! if (!bistate) ! ReleaseBuffer(buffer); /* * If tuple is cachable, mark it for invalidation from the caches in case 2. The logic changes in RelationGetBufferForTuple seem bizarre and overcomplicated. ISTM that the buffer saved by the bistate ought to be about equivalent to relation-rd_targblock, ie, it's your first trial location and also a place to save the located buffer on the way out. I'd suggest tossing that part of the patch and starting over. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP] In-place upgrade
Well, I just proposed an approach that doesn't work this way, so I guess I'll have to put myself in the disagree category, or anyway yet to be convinced. As long as you can move individual tuples onto new pages, you can eventually empty V3 pages and reinitialize them as new, empty V4 pages. You can force that process along via, say, VACUUM, No, if you can force that process along via some command, whatever it is, then you're still in the category he described. Maybe. The difference is that I'm talking about converting tuples, not pages, so What happens when the data doesn't fit on the new page? is a meaningless question. Since that seemed to be Heikki's main concern, I thought we must be talking about different things. My thought was that the code path for converting a tuple would be very similar to what heap_update does today, and large tuples would be handled via TOAST just as they are now - by converting the relation one tuple at a time, you might end up with a new relation that has either more or fewer pages than the old relation, and it really doesn't matter which. I haven't really thought through all of the other kinds of things that might need to be converted, though. That's where it would be useful for someone more experienced to weigh in on indexes, etc. ...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] [PATCH] Cleanup of PLpgSQL_recfield
Jonah H. Harris [EMAIL PROTECTED] writes:
While looking to add some functionality to PL/pgSQL, I found that the
rfno member of the PLpgSQL_recfield structure is unused. This patch
is just a cleanup
No, that'd be wrong. Note here:
/*
* PLpgSQL_datum is the common supertype for PLpgSQL_expr, PLpgSQL_var,
* PLpgSQL_row, PLpgSQL_rec, PLpgSQL_recfield, PLpgSQL_arrayelem, and
* PLpgSQL_trigarg
*/
typedef struct
{/* Generic datum array item*/
intdtype;
intdno;
} PLpgSQL_datum;
I am not real sure why the code is inconsistent about spelling the
second field's name differently in some of the structs, but it seems
like a bad idea --- as you've demonstrated, it invites confusion.
What would probably be better is a patch to rename exprno, rfno, etc
to all be called dno to make this connection more obvious.
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] [PATCH] Cleanup of PLpgSQL_recfield
Jonah H. Harris escribió: While looking to add some functionality to PL/pgSQL, I found that the rfno member of the PLpgSQL_recfield structure is unused. This patch is just a cleanup and doesn't seem along the same lines as the patches in CommitFest... should I add it to the wiki anyway? Nah -- I just applied it. Thanks. -- 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] [PATCH] Cleanup of PLpgSQL_recfield
Tom Lane escribió: Jonah H. Harris [EMAIL PROTECTED] writes: While looking to add some functionality to PL/pgSQL, I found that the rfno member of the PLpgSQL_recfield structure is unused. This patch is just a cleanup No, that'd be wrong. Oops. Reverting. -- 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] Patch for ALTER DATABASE WITH TABLESPACE
Guillaume Lelarge [EMAIL PROTECTED] writes: Should I provide a complete new patch with Bernd's and Tom's changes? Please --- it's better if you integrate it since you know the patch already. 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] plperl needs upgrade for Fedora 10
Pavel Stehule wrote: postgres=# select version(); version -- PostgreSQL 8.3.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.3.2 20080917 (Red Hat 4.3.2-4) (1 row) postgres=# CREATE LANGUAGE plperlu; ERROR: could not load library /usr/local/pgsql8.3/lib/plperl.so: /usr/local/pgsql8.3/lib/plperl.so: undefined symbol: boot_DynaLoader postgres=# 1. Please do not top-answer. 2. You have not provided the info I asked for, namely the configure params and the build log. e.g.: configure --enable-cassert --enable-debug --enable-nls --enable-integer-datetimes \ --with-perl --with-python --with-tcl \ --with-krb5 --with-includes=/usr/include/et --with-openssl \ --with-pam --with-ldap --with-libxml --with-libxslt --with-ossp-uuid --with-gssapi --enable-depend --prefix=/home/andrew/bf/root/HEAD/inst --with-pgport=5678 make[3]: Entering directory `/home/andrew/bf/root/HEAD/pgsql.24747/src/pl/plperl' ccache gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -g -fpic -I/home/andrew/bf/root/HEAD/pgsql.24747/../pgsql/src/pl/plperl -I../../../src/include -I/home/andrew/bf/root/HEAD/pgsql.24747/../pgsql/src/include -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include/et -I/usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/CORE -c -o plperl.o /home/andrew/bf/root/HEAD/pgsql.24747/../pgsql/src/pl/plperl/plperl.c -MMD -MP -MF .deps/plperl.Po ccache gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -g -fpic -I/home/andrew/bf/root/HEAD/pgsql.24747/../pgsql/src/pl/plperl -I../../../src/include -I/home/andrew/bf/root/HEAD/pgsql.24747/../pgsql/src/include -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include/et -I/usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/CORE -c -o spi_internal.o /home/andrew/bf/root/HEAD/pgsql.24747/../pgsql/src/pl/plperl/spi_internal.c -MMD -MP -MF .deps/spi_internal.Po /usr/bin/perl /usr/lib/perl5/5.8.8/ExtUtils/xsubpp -typemap /usr/lib/perl5/5.8.8/ExtUtils/typemap /home/andrew/bf/root/HEAD/pgsql.24747/../pgsql/src/pl/plperl/SPI.xs SPI.c ccache gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -g -fpic -I/home/andrew/bf/root/HEAD/pgsql.24747/../pgsql/src/pl/plperl -I../../../src/include -I/home/andrew/bf/root/HEAD/pgsql.24747/../pgsql/src/include -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include/et -I/usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/CORE -c -o SPI.o SPI.c -MMD -MP -MF .deps/SPI.Po ccache gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -g -fpic -shared plperl.o spi_internal.o SPI.o -L/usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/CORE -L../../../src/port /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/auto/DynaLoader/DynaLoader.a -lperl -lresolv -lnsl -ldl -lm -lcrypt -lutil -lpthread -lc -Wl,-rpath,'/usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/CORE' -o plperl.so make[3]: Leaving directory `/home/andrew/bf/root/HEAD/pgsql.24747/src/pl/plperl' 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] BufferAccessStrategy for bulk insert
2. The logic changes in RelationGetBufferForTuple seem bizarre and overcomplicated. ISTM that the buffer saved by the bistate ought to be about equivalent to relation-rd_targblock, ie, it's your first trial location and also a place to save the located buffer on the way out. I'd suggest tossing that part of the patch and starting over. Hmm, would that be safe in the presence of concurrent or recursive bulk inserts into the same relation? ...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] Probable problem with pg_standby
On Tue, Nov 4, 2008 at 8:09 PM, Detlef Ulherr [EMAIL PROTECTED] wrote: All I did was forcing the primary in a recovery to generate a new timeline. The installed version was 8.3.4, but the problem is the same with earlier versions as well. It occurred in 8.2 also. this problem is reproducible all the times. For my agent code I implemented a workaround which guarantees that during a resilvering process the primary and the standby start at t the same timeline. But my feeling is that the standby should go to the same timeline as the primary when he receives the history file without disruption, and by all means it should never stop the recovery unmotivated. This will make a full synchronization necessary and in times of larger databases, this may cause major downtimes. I agree with you only if normal archive recovery case (not specified recovery_target_xid/time). But, in point-in-time recovery case, the standby cannot continue to redo without stopping. DBA has to reconstruct the standby (get new online-backup with new timeline ID, locate it on the standby and restart recovery). Or, we should deal with normal archive recovery and point-in-time one separately? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Spurious Kerberos error messages
Peter Eisentraut [EMAIL PROTECTED] writes: I get the following display now when I connect to a non-running server, all default settings: psql: pg_krb5_init: krb5_cc_get_principal: No credentials cache found could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? Hmm ... a few of the buildfarm machines have failed like that too in recent days, but it's inconsistent (only one or two of the regression tests fail that way, typically). Does yours fail always? 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] [PATCHES] updated hash functions for postgresql v1
On Tue, Nov 04, 2008 at 11:32:47PM +0300, Oleg Bartunov wrote:
Just interested if you repeat your tests not with cracklib-dict,
but using 8-bit words. From our experience we found many hash functions
are optimized for 7-bit words and produce too many collisions
for 8-bit words. That's why we use crc32.
Oleg
I think that the lines:
uint32 from 1-1648379 309319 347
(uint32 1-1948379)*256309314 304
(uint32 1-1948379)*16 310314 324
auint32 (i.e. a1,a0002...) 320321 312
uint32uint32 (i.e. uint64)321287 309
can count as 8-bit words if taken a byte at a time. In fact
that is how hash_any() treats them, as a character string
and a length. One of the design goals of the original 1997
hash function in lookup2 and the 2006 update in lookup3 is
to support keys of arbitrary arrangements of bits. I can run
any additional checks that you want since the test harness
is perl with Inline::C. If you are using crc32 his article in
Dr. Dobbs shows that CRC has a 2 into 1 funnel-15 and an
11 into 10 funnel-100 unless you are using a generalized
CRC. Also, unless you can inline your CRC the Jenkins lookup3
is 5n+20 where CRC is 9n+3.
Regards,
Ken
On Tue, 4 Nov 2008, Kenneth Marshall wrote:
Sorry about the delay for this update to the new hash
index implementation. I was trying to get the WAL logging
in place and forgot to post the actual patch. The WAL
for hash indexes will need to wait for 8.5, but I did
want to add back in the piece of the Bob Jenkins 2006
hash function that was stripped out of the initial
patch on application due to concerns about the randomness
of the resulting hash values. Here is a re-post of my
initial findings comparing the old/new Jenkins hash
from lookup2 and lookup3. I have added a third column
containing the results for the hash_any() resulting
from the attached patch as well as simple timing test
for a DB reindex both before and after patching.
Also attached is a simple documentation patch updating
the note attached to the hash index description.
Regards,
Ken
Hi,
I have finally had a chance to do some investigation on
the performance of the old hash mix() function versus
the updated mix()/final() in the new hash function. Here
is a table of my current results for both the old and the
new hash function. In this case cracklib refers to the
cracklib-dict containing 1648379 unique words massaged
in various ways to generate input strings for the hash
functions. The result is the number of collisions in the
hash values generated.
hash inputoldnew newv2
-------- -
cracklib 338316 338
cracklib x 2 (i.e. clibclib) 305319 300
cracklib x 3 (clibclibclib) 323329 315
cracklib x 10 302310 329
cracklib x 100350335 298
cracklib x 1000 314309 315
cracklib x 100 truncated to char(100) 311327 320
uint32 from 1-1648379 309319 347
(uint32 1-1948379)*256309314 304
(uint32 1-1948379)*16 310314 324
auint32 (i.e. a1,a0002...) 320321 312
uint32uint32 (i.e. uint64)321287 309
The different result columns are old = Jenkins 1996 hash
function(lookup2.c), new = Jenkins 2006 hash function
(lookup3.c), and newv2 = adaptation of current hash_any()
to incorporate the separate mix()/final() functions. As
you can see from the results, spliting the mix() and final()
apart does not result in any perceptible loss of randomness
in the hash assignment. I also ran a crude timing for a
reindex of the following database:
CREATE TABLE dict (word text);
CREATE INDEX wordhash ON dict USING hash (word);
INSERT INTO dict (word) VALUES('s;lhkjdpyoijxfg;lktjgh;sdlhkjo');
INSERT INTO dict (SELECT MAX(word)||MAX(word) FROM dict);
... (21 times)
REINDEX TABLE
...
The average time to reindex the table using our current
hash_any() without the separate mix()/final() was 1696ms
and 1482ms with the separate mix()/final() stages giving
almost 13% better performance for this stupid metric.
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
Re: ARRAY vars (was [HACKERS] Enable pl/python to return records based on multiple OUT params)
On Tue, 2008-11-04 at 14:05 -0500, David Blewett wrote:
On Sat, Nov 1, 2008 at 7:52 AM, Hannu Krosing [EMAIL PROTECTED] wrote:
On Sat, 2008-11-01 at 06:13 +0200, Hannu Krosing wrote:
attached is a patch which enables plpython to recognize function with
multiple OUT params as returning a record
Overrides previous patch.
Fixed some bugs, added regression tests.
Hi Hannu:
I was wondering if it would be possible to get plpython to convert IN
parameters of type ARRAY to Python lists? I see some example functions
here [1], but it would be nice if it was done automatically.
This is one thing I definitely will do, praobably right after getting
ANY* to work, maybe even before, if getting ANY* to work requires too
many changes.
One open question is how to translate arrays with non-default subscript
values
Quote: Subscripted assignment allows creation of arrays that do not use
one-based subscripts. For example one might assign to myarray[-2:7] to
create an array with subscript values running from -2 to 7.
Should I just shift it to standard python tuple, or would it be better
to return it as a dictionary with keys from -2 to 7
sample:
hannu=# create table ta(ia int[]);
CREATE TABLE
hannu=# insert into ta values('{27000,27000}');
INSERT 0 1
hannu=# update ta set ia[-2:1] = '{-2,-1,0,1}';
UPDATE 1
hannu=# select * from ta;
ia
--
[-2:2]={-2,-1,0,1,27000}
(1 row)
and if I do return a dictionary , the after this
hannu=# update ta set ia[7:7] = '{7}';
UPDATE 1
hannu=# select * from ta;
ia
[-2:7]={-2,-1,0,1,27000,NULL,NULL,NULL,NULL,7}
(1 row)
should the returned python dict have keys 3-6 with None, or could they
just be omitted ?
Actually I have quite long todo list (attached) of what I'd like to do
with pl/python, but I'm not sure how much will be accepted in 8.4 under
current commitfest/feature freeze scheme.
Anyway, I should put that up on wiki for comments.
David
1. http://archives.postgresql.org/pgsql-general/2007-01/msg01417.php
--
--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training
plpython
* refactor code,
* make maximal use of funcapi.h
* split out to seperate files
* trigger,
* conversions,
* spi,
* python objects, ...
* pass function arguments as locals
* add support for types Decimal and datetime, date, time, bool
* add special handling for bytea, as standard conversion through string
representation does not work because python and postgreSQL escapes don't match
* add support for ARRAY types
* add support for polymorphic types (ANY, ANYARRAY)
* add suppoort for input type RECORD (and maybe output too ?)
* ? add support for any postgresql datatype ( lift code from pl/py ?? ) using
postgreSQL's internal ops/funcs
* add support for transactions, db procedures. test that it is called
directly, then run outside trx
* add support for modules inside db, either
* using not yet present PACKAGE mechanism
* as functions returning type plpython_module
* as a special relation ( pg_modules ) + functions for
inserting/updating/deleting these
* use pg_proc.probin for storing compiled code
* add VALIDATOR support (
http://www.postgresql.org/docs/8.3/interactive/xplang-install.html )
* add trusted language using zope3 RestrictedPython
* maybe special/direct support for inline SQL using parser from
RestrictedPython
* enable access to stdin/stdout, for COPY FROM/TO like functionality
* add DEBUGGER support
* add support for module level stuff
* add module cjson for JSON support on input and output
SPI interface:
* add direct function calls
* SPI: add iterator support for query() result
* SPI: add with: support for WHERE CURRENT OF
* add with: support for subtransactions
* alternatively add ability to do try: except: with subtransactions
* support for automatically storing SPI plans
postgreSQL core
1. add support for pull (streaming) to functions
2. add support for defining query tree nodes / node-returning-functions or
NRFs
3. re-enable SELECT rules, so that selects can be remoted more intelligently
using views, probably variant of 2.
?? mixed access to objects , super duck typing
1. try sequence
2. try dict
3. try attribute
ideas from http://www.postgresql.org/docs/faqs.TODO.html
Other
* Add capability to create and call PROCEDURES
* Add PL/PythonU tracebacks
http://archives.postgresql.org/pgsql-patches/2006-02/msg00288.php
* Allow data to be passed in native language formats, rather than only text
http://archives.postgresql.org/pgsql-hackers/2007-05/msg00289.php
...
PL/pgSQL functions can also be declared to accept and return the polymorphic
types
Re: [HACKERS] BufferAccessStrategy for bulk insert
Robert Haas [EMAIL PROTECTED] writes: 2. The logic changes in RelationGetBufferForTuple seem bizarre and overcomplicated. ISTM that the buffer saved by the bistate ought to be about equivalent to relation-rd_targblock, ie, it's your first trial location and also a place to save the located buffer on the way out. I'd suggest tossing that part of the patch and starting over. Hmm, would that be safe in the presence of concurrent or recursive bulk inserts into the same relation? As safe as it is now --- you're relying on the bistate to carry the query-local state. Probably the best design is to just ignore rd_targblock when a bistate is provided, and use the bistate's buffer instead. 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] Synchronous replication patch v1
Hi, thank you for taking time to review the patch. On Fri, Oct 31, 2008 at 11:12 PM, Heikki Linnakangas [EMAIL PROTECTED] wrote: Fujii Masao wrote: Attached is a patch for a synchronous log-shipping replication which was discussed just a month ago. I would like you to review this patch in Nov commit fest. Here's some first quick comments: AFAICS, there's no security, at all. Anyone that can log in, can become a WAL sender, and receive all WAL for the whole cluster. One simple solution is to define the database only for replication. In this solution, we can handle the authentication for replication like the usual database access. That is, pg_hba.conf, the cooperation with a database role, etc are supported also in replication. So, a user can set up the authentication rules easily. ISTM that there is no advantage which separates authentication for replication from the existing mechanism. How about this solution? -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] updated hash functions for postgresql v1
Oleg, Here is a little more information on the use of CRC32 as a hash function, with some warning caveats: http://home.comcast.net/~bretm/hash/8.html Regards, Ken On Tue, Nov 04, 2008 at 03:15:44PM -0600, Kenneth Marshall wrote: On Tue, Nov 04, 2008 at 11:32:47PM +0300, Oleg Bartunov wrote: Just interested if you repeat your tests not with cracklib-dict, but using 8-bit words. From our experience we found many hash functions are optimized for 7-bit words and produce too many collisions for 8-bit words. That's why we use crc32. Oleg I think that the lines: uint32 from 1-1648379 309319 347 (uint32 1-1948379)*256309314 304 (uint32 1-1948379)*16 310314 324 auint32 (i.e. a1,a0002...) 320321 312 uint32uint32 (i.e. uint64)321287 309 can count as 8-bit words if taken a byte at a time. In fact that is how hash_any() treats them, as a character string and a length. One of the design goals of the original 1997 hash function in lookup2 and the 2006 update in lookup3 is to support keys of arbitrary arrangements of bits. I can run any additional checks that you want since the test harness is perl with Inline::C. If you are using crc32 his article in Dr. Dobbs shows that CRC has a 2 into 1 funnel-15 and an 11 into 10 funnel-100 unless you are using a generalized CRC. Also, unless you can inline your CRC the Jenkins lookup3 is 5n+20 where CRC is 9n+3. Regards, Ken -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: array_agg (was Re: [HACKERS] The Axe list)
Ian Caulfield wrote: 2008/10/15 Ian Caulfield [EMAIL PROTECTED]: I started to look at implementing array_agg by making the existing intagg stuff more generic ... and here's what I've come up with. Going through the commit fest listings, I think we can safely omit this patch and work out an in-core solution somewhere between the patches of Robert Haas and 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] libpq and sslmode=require
Peter Eisentraut wrote: Bruce Momjian wrote: In testing an SSL patch, I found that if I use 'sslmode=require' in the libpq connection string, it does not use SSL over a unix-domain socket. libpq should either use SSL (which I don't think it can), or error out, or we should at least document this behavior. We discussed this before 8.3 already. It might be time to address this now that the SSL support is being redesigned. SSL over Unix-domain sockets with libpq works perfectly fine if you remove the code in libpq and/or the postmaster (forgot which exactly) that thinks that it doesn't work. Well, perfectly fine I'm not sure. What would you verify the CN in the certificate against, if there is no hostname? And doing this CN verification would actually be the only reason you'd want to use SSL over Unix sockets, I think. If we can figure out something reasonable to do here, it might be worthwhile to support it. The issue previously was the libpq defaults to sslmode=prefer and that would impose a noticeable connection initiation overhead on everyone's Unix-domain socket uses. You could make it use SSL in require mode, but it seems weird that prefer mode would end up doing something different than require mode. Maybe Magnus has an opinion on how we could make this fit into the new scheme of things. I assume since we require certificates to be set up now, SSL will by default be off and so using it over Unix-domain sockets when enabled would not be in the common path, which was the objection previously. The only difference is that we require certificate or CA on the client. Well, not require, but by default request it. We have always required a certificate on the server. SSL is still controlled by the ssl=on on the server side primarily. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP] In-place upgrade
Robert Haas [EMAIL PROTECTED] writes: We've talked about this many times before, so I'm sure you know what my opinion is. Let me phrase it one more time: 1. You *will* need a function to convert a page from old format to new format. We do want to get rid of the old format pages eventually, whether it's during VACUUM, whenever a page is read in, or by using an extra utility. And that process needs to online. Please speak up now if you disagree with that. Well, I just proposed an approach that doesn't work this way, so I guess I'll have to put myself in the disagree category, or anyway yet to be convinced. As long as you can move individual tuples onto new pages, you can eventually empty V3 pages and reinitialize them as new, empty V4 pages. You can force that process along via, say, VACUUM, No, if you can force that process along via some command, whatever it is, then you're still in the category he described. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: Hot standby v5 patch - Databases created post backup remain inaccessible + replica SIGSEGV when coming out of standby
On Tue, 2008-11-04 at 18:33 +1300, Mark Kirkwood wrote:
postgres=# \l
List of databases
Name| Owner | Encoding | Collation | Ctype | Access
Privileges
---+--+---+---+---+-
bench | postgres | SQL_ASCII | C | C |
postgres | postgres | SQL_ASCII | C | C |
template0 | postgres | SQL_ASCII | C | C |
{=c/postgres,postgres=CTc/postgres}
template1 | postgres | SQL_ASCII | C | C |
{=c/postgres,postgres=CTc/postgres}
(4 rows)
postgres=# \c bench
FATAL: database bench does not exist
Previous connection kept
CREATE DATABASE didn't trigger the db flat file update, code for which
existed and was triggered in the cases when a transaction would normally
rebuild the flat files. Simple fix, but stupid oversight.
Spotted another problem which is that BuildFlatFile may not be built
consistently if a rebuild is triggered prior to us reaching the recovery
consistency point. This is fixed by forcing a rebuild of the flat files
when we hit the recovery point.
Both one line changes, but I'll go looking for other issues there.
--
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
[HACKERS] Signal handling patch (v2) for Synch Rep
Hi, To be reviewed easily, I'm splitting Synch Rep patch into some pieces. Attached is a patch of signal handling changes for Synch Rep. http://archives.postgresql.org/pgsql-hackers/2008-09/msg00950.php Though I've posted the WIP patch previously, this is a finished one. Please feel free to comment on it. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center Index: src/backend/access/transam/twophase.c === RCS file: /projects/cvsroot/pgsql/src/backend/access/transam/twophase.c,v retrieving revision 1.46 diff -c -r1.46 twophase.c *** src/backend/access/transam/twophase.c 20 Oct 2008 19:18:18 - 1.46 --- src/backend/access/transam/twophase.c 27 Oct 2008 01:59:57 - *** *** 285,290 --- 285,291 gxact-proc.databaseId = databaseid; gxact-proc.roleId = owner; gxact-proc.inCommit = false; + gxact-proc.signalFlags = 0; gxact-proc.vacuumFlags = 0; gxact-proc.lwWaiting = false; gxact-proc.lwExclusive = false; Index: src/backend/commands/async.c === RCS file: /projects/cvsroot/pgsql/src/backend/commands/async.c,v retrieving revision 1.141 diff -c -r1.141 async.c *** src/backend/commands/async.c 30 Aug 2008 01:39:13 - 1.141 --- src/backend/commands/async.c 27 Oct 2008 01:59:57 - *** *** 915,923 * a frontend command. Signal handler execution of inbound notifies * is disabled until the next EnableNotifyInterrupt call. * ! * The SIGUSR1 signal handler also needs to call this, so as to ! * prevent conflicts if one signal interrupts the other. So we ! * must return the previous state of the flag. */ bool DisableNotifyInterrupt(void) --- 915,924 * a frontend command. Signal handler execution of inbound notifies * is disabled until the next EnableNotifyInterrupt call. * ! * This also needs to be called when SIGUSR1 with ! * PROCSIGNAL_CATCHUP_INTERRUPT is received, so as to prevent conflicts ! * if one signal interrupts the other. So we must return the previous ! * state of the flag. */ bool DisableNotifyInterrupt(void) *** *** 954,960 nulls[Natts_pg_listener]; bool catchup_enabled; ! /* Must prevent SIGUSR1 interrupt while I am running */ catchup_enabled = DisableCatchupInterrupt(); if (Trace_notify) --- 955,961 nulls[Natts_pg_listener]; bool catchup_enabled; ! /* Must prevent catchup interrupt while I am running */ catchup_enabled = DisableCatchupInterrupt(); if (Trace_notify) Index: src/backend/postmaster/autovacuum.c === RCS file: /projects/cvsroot/pgsql/src/backend/postmaster/autovacuum.c,v retrieving revision 1.84 diff -c -r1.84 autovacuum.c *** src/backend/postmaster/autovacuum.c 13 Aug 2008 00:07:50 - 1.84 --- src/backend/postmaster/autovacuum.c 27 Oct 2008 01:59:57 - *** *** 1480,1486 pqsignal(SIGALRM, handle_sig_alarm); pqsignal(SIGPIPE, SIG_IGN); ! pqsignal(SIGUSR1, CatchupInterruptHandler); /* We don't listen for async notifies */ pqsignal(SIGUSR2, SIG_IGN); pqsignal(SIGFPE, FloatExceptionHandler); --- 1480,1486 pqsignal(SIGALRM, handle_sig_alarm); pqsignal(SIGPIPE, SIG_IGN); ! pqsignal(SIGUSR1, proc_sigusr1_handler); /* We don't listen for async notifies */ pqsignal(SIGUSR2, SIG_IGN); pqsignal(SIGFPE, FloatExceptionHandler); Index: src/backend/storage/ipc/sinval.c === RCS file: /projects/cvsroot/pgsql/src/backend/storage/ipc/sinval.c,v retrieving revision 1.86 diff -c -r1.86 sinval.c *** src/backend/storage/ipc/sinval.c 19 Jun 2008 21:32:56 - 1.86 --- src/backend/storage/ipc/sinval.c 27 Oct 2008 01:59:57 - *** *** 27,33 * need a way to give an idle backend a swift kick in the rear and make * it catch up before the sinval queue overflows and forces it to go * through a cache reset exercise. This is done by sending SIGUSR1 ! * to any backend that gets too far behind. * * State for catchup events consists of two flags: one saying whether * the signal handler is currently allowed to call ProcessCatchupEvent --- 27,34 * need a way to give an idle backend a swift kick in the rear and make * it catch up before the sinval queue overflows and forces it to go * through a cache reset exercise. This is done by sending SIGUSR1 ! * with PROCSIGNAL_CATCHUP_INTERRUPT to any backend that gets too far ! * behind. * * State for catchup events consists of two flags: one saying whether * the signal handler is currently allowed to call ProcessCatchupEvent *** *** 144,152 /* ! * CatchupInterruptHandler * ! * This is the signal handler for SIGUSR1. * * If we are
[HACKERS] Spurious Kerberos error messages
I get the following display now when I connect to a non-running server, all default settings: psql: pg_krb5_init: krb5_cc_get_principal: No credentials cache found could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? -- 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] Windowing Function Patch Review - Performance Comparison.
Hitoshi Harada wrote: Test 3 and 5 did not seem to make use of an index to get a sorted list of results. I disabled enable_seqscan but the planner still failed to choose index_scan. Is there any reason for this? Perhaps I'm missing something. Hitoshi, can you take a look at this? Ah, good point. Maybe it's because I haven't paid attention to choose index_scan for upper sort node. I just put the sort node whatever the downer node is, so it might be needed to sink the information down to scan choice process that we use sort node upper. Could someone point me out how to do it, or which part of the existing code would be a good guide? I know you need to wait for an answer about this, so I'd like to delay any further performance tests until that's sorted out as it should affect performance of larger tables quite a bit. I found how to do it, though it's only on the case you gave. Thinking about the planner optimization of the Window nodes (and its attached Sort nodes), we must consider the execution order of more than one node. In the test case we only take care of only one window, but there may be more window/sort node sets, which is too difficult to choose the best execution order including the downer indexscan, mergejoin in subquery and sort-based GROUP BY. So I didn't touch the complicated planner jungle. I rewrote the patch so that only the given bottom window's sort can consider indexscan. Deeper optimizations are over my capability. I've just looked into what some other implementations do. Sybase seems to do exactly what you've done. It only looks at the first window clause in the query. Oracle seems to use the index regardless to the position of the window clause. To me personally what you've done seems fine for now. Perhaps something could be done later to improve on this. Maybe someone else has ideas about how to do it? It seems quite similar to SELECT MAX(idxcol),MAX(idxcol2) where the planner often makes use of 2 indexes when available, yet this case is probably far more simple as there is always just 1 row. Costing would likely be more complex with the windowing functions version. Good work. I'll continue with more benchmarks soon. David. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Synchronous replication patch v1
On Fri, Oct 31, 2008 at 10:15 PM, Heikki Linnakangas [EMAIL PROTECTED] wrote: Fujii Masao wrote: And, there are some problems in this patch; * This patch is somewhat big, though it should be subdivided for review. * Source code comments and documents are insufficient. Is it against the rule of commit fest to add such a status patch into review-queue? If so, I would aim for 8.5. Otherwise, I will deal with the problems also during commit fest. What is your opinion? You can add work-in-progress patches and even just design docs to the commitfest queue. That's perfectly OK. They will be reviewed as any other work, but naturally if it's not a patch that's ready to be committed without major work, it won't be committed. I haven't looked at the patch yet, but if you think there's chances to get it into shape for inclusion to 8.4, before the commit fest is over, you can and should keep working on it and submit updated patches during the commit fest. However, help with reviewing other patches would also be very much appreciated. The idea of commitfests is that everyone stops working on their own stuff, except for cleaning up and responding to review comments on one's own patches that are in the queue, and helps to review other people's patches. OK, thanks Heikki. I will keep working on Synch Rep during commit-fest. At first, as you say, I'll split the signal handling changes into an individual patch ASAP. Regards; -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Probable problem with pg_standby
Hi, First to introduce myself, I am working in Sun Cluster engineering and I am responsible for the integration (the agent) between PostgreSQL and Sun Cluster. The PostgreSQL agent provides a feature which uses WAL file shipping and pg_standby as a replacement for shared storage. Let's talk about the problem now. Whenever the primary database server selects a new timeline, the standby server which is running pg_standby stops applying logs to its database. It comes even worse, after a while pg_standby terminates the recovery mode and now we have primary and standby accepting requests. there was no trigger file created, nor a signal sent manually to pg_standby. Here is some debugging output of pg_standby. running restore : OK removing /pgs/83_walarchives/001A00B5 LOG: restored log file 001F00D4 from archive LOG: record with zero length at 0/D460 LOG: redo done at 0/D420 Trigger file: /pgs/data/failover Waiting for WAL file: 001F00D4 WAL file path : /pgs/83_walarchives/001F00D4 Restoring to... : pg_xlog/RECOVERYXLOG Sleep interval : 5 seconds Max wait interval : 0 forever Command for restore : cp /pgs/83_walarchives/001F00D4 pg_xlog/RECOVERYXLOG Keep archive history: 001F00B6 and later running restore : OK LOG: restored log file 001F00D4 from archive Trigger file: /pgs/data/failover Waiting for WAL file: 0020.history WAL file path : /pgs/83_walarchives/0020.history Restoring to... : pg_xlog/RECOVERYHISTORY Sleep interval : 5 seconds Max wait interval : 0 forever Command for restore : cp /pgs/83_walarchives/0020.history pg_xlog/RECOVERYHISTORY Keep archive history: No cleanup required running restore : OKLOG: restored log file 0020.history from archive Trigger file: /pgs/data/failover Waiting for WAL file: 0021.history WAL file path : /pgs/83_walarchives/0021.history Restoring to... : pg_xlog/RECOVERYHISTORY Sleep interval : 5 seconds Max wait interval : 0 forever Command for restore : cp /pgs/83_walarchives/0021.history pg_xlog/RECOVERYHISTORY Keep archive history: No cleanup required running restore :cp: cannot access /pgs/83_walarchives/0021.history cp: cannot access /pgs/83_walarchives/0021.history cp: cannot access /pgs/83_walarchives/0021.history not restored: history file not found LOG: selected new timeline ID: 33 Trigger file: /pgs/data/failover Waiting for WAL file: 001F.history WAL file path : /pgs/83_walarchives/001F.history Restoring to... : pg_xlog/RECOVERYHISTORY Sleep interval : 5 seconds Max wait interval : 0 forever Command for restore : cp /pgs/83_walarchives/001F.history pg_xlog/RECOVERYHISTORY Keep archive history: No cleanup required running restore : OKLOG: restored log file 001F.history from archive LOG: archive recovery complete LOG: autovacuum launcher started LOG: database system is ready to accept connections And here are the corresponding logs from the primary database server. LOG: autovacuum launcher started LOG: database system is ready to accept connections building file list ... done 001D00D1 sent 16779397 bytes received 42 bytes 6711775.60 bytes/sec total size is 16777216 speedup is 1.00 building file list ... done 001F.history sent 2248 bytes received 42 bytes 4580.00 bytes/sec total size is 2119 speedup is 0.93 building file list ... done 001F00D2 sent 16779397 bytes received 42 bytes 11186292.67 bytes/sec total size is 16777216 speedup is 1.00 LOG: received fast shutdown request LOG: aborting any active transactions LOG: autovacuum launcher shutting down LOG: shutting down LOG: database system is shut down LOG: database system was shut down at 2008-10-29 14:07:40 CET LOG: database system is ready to accept connections LOG: autovacuum launcher started building file list ... done 001F00D3 sent 16779397 bytes received 42 bytes 11186292.67 bytes/sec total size is 16777216 speedup is 1.00 LOG: received fast shutdown request LOG: aborting any active transactions LOG: autovacuum launcher shutting down LOG: shutting down LOG: database system is shut down building file list ... done 001D00D2 sent 16779397 bytes received 42 bytes 11186292.67 bytes/sec total size is 16777216 speedup is 1.00 building file list ... done 001E00D2 sent 16779397 bytes received 42 bytes 6711775.60 bytes/sec total size is 16777216 speedup is 1.00 LOG: database system was shut down at 2008-10-29 14:10:59 CET LOG: starting archive recovery LOG: restore_command = 'cp /pgs/83_walarchives/%f %p'
Re: [HACKERS] plperl needs upgrade for Fedora 10
Pavel Stehule wrote: 2008/11/4 Andrew Dunstan [EMAIL PROTECTED]: Andrew Dunstan wrote: 2. You have not provided the info I asked for, namely the configure params and the build log. e.g.: My apologies. I missed the attachments with this info. no problem Please send the output of the following: perl -V nm /usr/lib64/perl5/5.10.0/x86_64-linux-thread-multi/CORE/libperl.so | grep boot_Dyn cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP] In-place upgrade
Robert Haas [EMAIL PROTECTED] writes: Well, I just proposed an approach that doesn't work this way, so I guess I'll have to put myself in the disagree category, or anyway yet to be convinced. As long as you can move individual tuples onto new pages, you can eventually empty V3 pages and reinitialize them as new, empty V4 pages. You can force that process along via, say, VACUUM, No, if you can force that process along via some command, whatever it is, then you're still in the category he described. Maybe. The difference is that I'm talking about converting tuples, not pages, so What happens when the data doesn't fit on the new page? is a meaningless question. No it's not, because as you pointed out you still need a way for the user to force it to happen sometime. Unless you're going to be happy with telling users they need to update all their tuples which would not be an online process. In any case it sounds like you're saying you want to allow multiple versions of tuples on the same page -- which a) would be much harder and b) doesn't solve the problem since the page still has to be converted sometime anyways. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS 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] [PATCHES] updated hash functions for postgresql v1
Sorry about the delay for this update to the new hash
index implementation. I was trying to get the WAL logging
in place and forgot to post the actual patch. The WAL
for hash indexes will need to wait for 8.5, but I did
want to add back in the piece of the Bob Jenkins 2006
hash function that was stripped out of the initial
patch on application due to concerns about the randomness
of the resulting hash values. Here is a re-post of my
initial findings comparing the old/new Jenkins hash
from lookup2 and lookup3. I have added a third column
containing the results for the hash_any() resulting
from the attached patch as well as simple timing test
for a DB reindex both before and after patching.
Also attached is a simple documentation patch updating
the note attached to the hash index description.
Regards,
Ken
Hi,
I have finally had a chance to do some investigation on
the performance of the old hash mix() function versus
the updated mix()/final() in the new hash function. Here
is a table of my current results for both the old and the
new hash function. In this case cracklib refers to the
cracklib-dict containing 1648379 unique words massaged
in various ways to generate input strings for the hash
functions. The result is the number of collisions in the
hash values generated.
hash inputoldnew newv2
-------- -
cracklib 338316 338
cracklib x 2 (i.e. clibclib) 305319 300
cracklib x 3 (clibclibclib) 323329 315
cracklib x 10 302310 329
cracklib x 100350335 298
cracklib x 1000 314309 315
cracklib x 100 truncated to char(100) 311327 320
uint32 from 1-1648379 309319 347
(uint32 1-1948379)*256309314 304
(uint32 1-1948379)*16 310314 324
auint32 (i.e. a1,a0002...) 320321 312
uint32uint32 (i.e. uint64)321287 309
The different result columns are old = Jenkins 1996 hash
function(lookup2.c), new = Jenkins 2006 hash function
(lookup3.c), and newv2 = adaptation of current hash_any()
to incorporate the separate mix()/final() functions. As
you can see from the results, spliting the mix() and final()
apart does not result in any perceptible loss of randomness
in the hash assignment. I also ran a crude timing for a
reindex of the following database:
CREATE TABLE dict (word text);
CREATE INDEX wordhash ON dict USING hash (word);
INSERT INTO dict (word) VALUES('s;lhkjdpyoijxfg;lktjgh;sdlhkjo');
INSERT INTO dict (SELECT MAX(word)||MAX(word) FROM dict);
... (21 times)
REINDEX TABLE
...
The average time to reindex the table using our current
hash_any() without the separate mix()/final() was 1696ms
and 1482ms with the separate mix()/final() stages giving
almost 13% better performance for this stupid metric.
--- indices.sgml2008-10-13 14:40:06.0 -0500
+++ indices.sgml.NEW2008-11-04 12:42:35.0 -0600
@@ -190,13 +190,11 @@
note
para
-Testing has shown productnamePostgreSQL/productname's hash
-indexes to perform no better than B-tree indexes, and the
-index size and build time for hash indexes is much worse.
-Furthermore, hash index operations are not presently WAL-logged,
+productnamePostgreSQL/productname's hash indexes provide
+the fast O(1) lookups, even for very large objects.
+Hash index operations are not presently WAL-logged,
so hash indexes might need to be rebuilt with commandREINDEX/
-after a database crash.
-For these reasons, hash index use is presently discouraged.
+after a database crash.
/para
/note
--- hashfunc.c.ORIG 2008-09-03 13:07:14.0 -0500
+++ hashfunc.c.NEW 2008-11-04 08:36:16.0 -0600
@@ -200,39 +200,94 @@
* hash function, see http://burtleburtle.net/bob/hash/doobs.html,
* or Bob's article in Dr. Dobb's Journal, Sept. 1997.
*
- * In the current code, we have adopted an idea from Bob's 2006 update
- * of his hash function, which is to fetch the data a word at a time when
- * it is suitably aligned. This makes for a useful speedup, at the cost
- * of having to maintain four code paths (aligned vs unaligned, and
- * little-endian vs big-endian). Note that we have NOT adopted his newer
- * mix() function, which is faster but may sacrifice some randomness.
+ * In the current code, we have adopted Bob's 2006 update of his hash
+ * which fetches the data a word at a time when it is suitably aligned.
+ * This makes for a useful speedup, at the cost of having to maintain
+ * four code paths (aligned vs unaligned, and little-endian vs big-endian).
+ * It also two separate mixing functions mix() and final() instead
+ * of a single multi-purpose function, that is slower as a result.
*/
/* Get a bit mask of the bits set in
Re: [HACKERS] libpq and sslmode=require
Bruce Momjian wrote: would impose a noticeable connection initiation overhead on everyone's Unix-domain socket uses. You could make it use SSL in require mode, but it seems weird that prefer mode would end up doing something different than require mode. Maybe Magnus has an opinion on how we could make this fit into the new scheme of things. I assume since we require certificates to be set up now, SSL will by default be off and so using it over Unix-domain sockets when enabled would not be in the common path, which was the objection previously. Yep, the problem is that sslmode doesn't have any way to specify if we want unix domain sockets to behave differently from tcp sockets, and our default for sslmode makes that even worse. I have added the following documentation patch to mention that 'sslmode' is ignored for unix domain sockets; backpatched to 8.3.X. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/libpq.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/libpq.sgml,v retrieving revision 1.266 diff -c -c -r1.266 libpq.sgml *** doc/src/sgml/libpq.sgml 27 Oct 2008 09:42:31 - 1.266 --- doc/src/sgml/libpq.sgml 4 Nov 2008 22:33:49 - *** *** 233,240 termliteralsslmode/literal/term listitem para !This option determines whether or with what priority an !acronymSSL/ connection will be negotiated with the server. There are four modes: literaldisable/ will attempt only an unencrypted acronymSSL/ connection; literalallow/ will negotiate, trying first a --- 233,240 termliteralsslmode/literal/term listitem para !This option determines whether or with what priority a !acronymSSL/ TCP/IP connection will be negotiated with the server. There are four modes: literaldisable/ will attempt only an unencrypted acronymSSL/ connection; literalallow/ will negotiate, trying first a *** *** 243,249 will negotiate, trying first an acronymSSL/ connection, then if that fails, trying a regular non-acronymSSL/ connection; literalrequire/ will try only an !acronymSSL/ connection. /para para --- 243,250 will negotiate, trying first an acronymSSL/ connection, then if that fails, trying a regular non-acronymSSL/ connection; literalrequire/ will try only an !acronymSSL/ connection. literalsslmode/ is ignored !for Unix domain socket communication. /para para -- 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 (r1168)
KaiGai Kohei wrote: OK. I am wondering if we _want_ two ways to set column permisions, especially since I think there will be only one way to set row-level permissions. I think we should not see the feature from only the viewpoint of granularity in access controls. The both of new security features (sepgsql and rowacl) are enhanced security features, but the Stephen's efforts is one of the core features based on SQL-standard and enabled in the default. Please pay mention that any given queries have to be checked by the core facility, and can be checked by the enhanced one if enabled. The PGACE security framework enables us to implement various kind of enhanced security features, and has two guest facilities now. They can have its own security model and granularities as a part of its design. The one has its granularities with some of overlaps on tables/columns/functions, and the other also has its granularity without overlaps because its purpose is supplement of the core security facilities. So, it is not a strange design there is only one way to set row-level permissions, because the current SQL-standard does not have its specifications and no core facilities are here. If the future version of PostgreSQL got a newer row-level permissions defined within SQL-standard, I think there should be two ways to set row-level ones for both of the core and enhanced. OK, I understand. Thanks. -- Bruce Momjian [EMAIL PROTECTED]http://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] [PATCH] Cleanup of PLpgSQL_recfield
On Tue, Nov 4, 2008 at 3:57 PM, Tom Lane [EMAIL PROTECTED] wrote: I am not real sure why the code is inconsistent about spelling the second field's name differently in some of the structs, but it seems like a bad idea --- as you've demonstrated, it invites confusion. What would probably be better is a patch to rename exprno, rfno, etc to all be called dno to make this connection more obvious. Attached. Passed regressions and basic testing. -- Jonah H. Harris, Senior DBA myYearbook.com plpgsql_datumnaming_cleanup.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Transactions and temp tables
Heikki Linnakangas wrote: Yes, I was trying to allow also ON COMMIT DROP and ON COMMIT DELETE ROW. An empty temp table at PREPARE time would be similar to an ON COMMIT DELETE ROW table. I think you'll want to check explicitly that the table is defined with ON COMMIT DELETE ROWS, instead of checking that it's empty. Where can I find the field containing the CREATE options for the temp table? Yeah, thanks to MVCC, it's possible that the table looks empty to the transaction being prepared, using SnapshotNow, but there's some tuples that are still visible to other transactions. For example: CREATE TEMPORARY TABLE foo (id int4); INSERT INTO foo VALUES (1); begin; DELETE FROM foo; PREPARE TRANSACTION 'foo'; -- doesn't error, because the table is empty, according to SnapshotNow SELECT * FROM foo; -- Still shows the one row, because the deleting transaction hasn't committed yet. Is that a problem? If your transaction isolation level is not serializable the SELECT will not block and return the current snapshot. From the transaction standpoint, it is fine that the transaction can prepare or am I missing something? Actually, I did a test and if the temp table is created with 'on commit delete rows' option, the select blocks until the transaction is committed. This seems a normal behavior to me. I don't think you can just ignore prepared temp relations in findDependentObjects to avoid the lockup at backend exit. It's also used for DROP CASCADE, for example. Do you mean that it will break the DROP CASCADE behavior in general, or that would break the behavior for master/child temp tables? For temp tables, I suppose. I confirm that doing a drop cascade on a master temp table after a prepared transaction committed from another backend will not drop the children for now. The hack in findDependentObjects still isn't enough, anyway. If you have a prepared transaction that created a temp table, the database doesn't shut down: $ bin/pg_ctl -D data start server starting $ LOG: database system was shut down at 2008-11-04 10:27:27 EST LOG: autovacuum launcher started LOG: database system is ready to accept connections $ bin/psql postgres -c begin; CREATE TEMPORARY TABLE temp (id integer); PREPARE TRANSACTION 'foo'; PREPARE TRANSACTION [EMAIL PROTECTED]:~/pgsql.fsmfork$ bin/pg_ctl -D data stop LOG: received smart shutdown request LOG: autovacuum launcher shutting down waiting for server to shut down... failed pg_ctl: server does not shut down Interesting case, if the table is created but not accessed it is not enlisted and then the shutdown does not catch this dependency. The table should be enlisted at CREATE time as well. The bookkeeping of prepared commit tables is just for the shutdown case right now. If you think it is a bad idea altogether to have session temp tables (even with delete rows on commit) that can cross commit boundaries, then we can remove that second bookkeeping and only allow temp tables that have been created withing the scope of the transaction. I fixed the hash_freeze problem but this drop cascade on temp table seems to be an issue (if anyone uses that feature). Emmanuel -- Emmanuel Cecchet FTO @ Frog Thinker Open Source Development Consulting -- Web: http://www.frogthinker.org email: [EMAIL PROTECTED] Skype: emmanuel_cecchet -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Cleanup of PLpgSQL_recfield
Jonah H. Harris [EMAIL PROTECTED] writes: On Tue, Nov 4, 2008 at 3:57 PM, Tom Lane [EMAIL PROTECTED] wrote: I am not real sure why the code is inconsistent about spelling the second field's name differently in some of the structs, but it seems like a bad idea --- as you've demonstrated, it invites confusion. What would probably be better is a patch to rename exprno, rfno, etc to all be called dno to make this connection more obvious. Attached. Passed regressions and basic testing. Looks good, applied. 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] Patch for ALTER DATABASE WITH TABLESPACE
Tom Lane a écrit : Guillaume Lelarge [EMAIL PROTECTED] writes: Should I provide a complete new patch with Bernd's and Tom's changes? Please --- it's better if you integrate it since you know the patch already. I worked with Bernd's patch and replace the WITH syntax with the SET one. It works AFAICS, but I'm not sure this is the best way to do it. I'm no bison-guru. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com alterdb_tablespace_v3.patch.bz2 Description: application/bzip -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Windowing Function Patch Review - Standard Conformance
I wrote: All, This is my first patch review for PostgreSQL. I did submit a patch last commit fest (Boyer-Moore) so I feel I should review one this commit fest. I'm quite new to PostgreSQL so please don't rely on me totally. I'll do my best. Heikki is also reviewing this patch which makes me feel better. My aim is to get the author has much feed back as quickly as possible. For this reason I'm going to be breaking down my reviews into the following topics. 1. Does patch apply cleanly? 2. Any compiler warnings? 3. Do the results follow the SQL standard? 4. Performance Comparison, does it perform better than alternate ways of doing things. Self joins, sub queries etc. 5. Performance, no comparison. How does it perform with larger tables? This thread covers part of 3. Quoted from SQL:2008 If CUME_DIST is specified, then the relative rank of a row R is defined as NP/NR, where NP is defined to be the number of rows preceding or peer with R in the window ordering of the window partition of R and NR is defined to be the number of rows in the window partition of R. So let me create a quick test case... create table employees ( id INT primary key, name varchar(30) not null, department varchar(30) not null, salary int not null, check (salary = 0) ); insert into employees values(1,'Jeff','IT',1); insert into employees values(2,'Sam','IT',12000); insert into employees values(3,'Richard','Manager',3); insert into employees values(4,'Ian','Manager',2); insert into employees values(5,'John','IT',6); insert into employees values(6,'Matthew','Director',6); My interpretation of the standard should make the last two columns in the following query equal, and they are in the patch. SELECT name,CAST(r AS FLOAT) / c, cd FROM (SELECT name, ROW_NUMBER() OVER(ORDER BY salary) as r, COUNT(*) OVER() AS c, CUME_DIST() OVER(ORDER BY salary) AS cd FROM employees ) t; Both Oracle and Sybase say otherwise. Have I (we both) misinterpreted the standard? name,cast(t.r as real)/t.c,cd 'Jeff',0.1,0.1 'Sam',0.,0. 'Ian',0.5,0.5 'Richard',0.,0. 'John',0.8334,1.0 'Matthew',1.0,1.0 Above are the results from Sybase. Can anyone see who is correct here? Is it possible that both Oracle and Sybase have it wrong? David. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windowing Function Patch Review - Standard Conformance
Quoted from SQL:2008 If CUME_DIST is specified, then the relative *rank *of a row R is defined as NP/NR, where NP is defined to be the number of rows preceding or peer with R in the window ordering of the window partition of R and NR is defined to be the number of rows in the window partition of R. I guess there is a difference between row_number and number of rows preceding or peer with R number of rows preceding or peer with R == count(*) over (order by salary) As far as I understand, the following query should calculate cume_dist properly (and it does so in Oracle): SELECT name,CAST(r AS FLOAT) / c, cd FROM (SELECT name, COUNT(*) OVER(ORDER BY salary) as r, COUNT(*) OVER() AS c, CUME_DIST() OVER(ORDER BY salary) AS cd FROM employees ) t; Sincerely yours, Vladimir Sitnikov
[HACKERS] Questions about patch Table command
Hi I am reviewer of Patch Table command. I wanted to build code checked out from code repo got via rsyn, but I didn't find relating code in file gram.y. Do i need to add the code to the file?
Re: [HACKERS] Windowing Function Patch Review - Standard Conformance
2008/11/5 Vladimir Sitnikov [EMAIL PROTECTED]: Quoted from SQL:2008 If CUME_DIST is specified, then the relative rank of a row R is defined as NP/NR, where NP is defined to be the number of rows preceding or peer with R in the window ordering of the window partition of R and NR is defined to be the number of rows in the window partition of R. I guess there is a difference between row_number and number of rows preceding or peer with R number of rows preceding or peer with R == count(*) over (order by salary) As far as I understand, the following query should calculate cume_dist properly (and it does so in Oracle): SELECT name,CAST(r AS FLOAT) / c, cd FROM (SELECT name, COUNT(*) OVER(ORDER BY salary) as r, COUNT(*) OVER() AS c, CUME_DIST() OVER(ORDER BY salary) AS cd FROM employees ) t; I'm afraid I misinterpreted it. As you say, number of rows preceding == row_number() and rumber of rows preceding or peers to R != row_number() (neither rank()) peers to R in the window function context means same rows by the ORDER BY clause, so in the first example, id=5 and id=6 are peers and in both rows, NP should be 6, as Oracle and Sybase say. Even though I understand the definition, your suggestion of COUNT(*) OVER (ORDER BY salary) doesn't make sense. In the patch, it simply returns the same value as row_number() but is it wrong, too? Regards, -- Hitoshi Harada -- 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] Windowing Function Patch Review - Standard Conformance
Even though I understand the definition, your suggestion of COUNT(*) OVER (ORDER BY salary) doesn't make sense. Why does not that make sense? I have not read the spec, however Oracle has a default window specification in case there is only an order by clause. The default window is range between unbounded preceding and current row. count(*) over (order by salary range between unbounded preceding and current row) is perfectly identical to the number of rows preceding or peers to R by the definition, isn't it? I see here a word-by-word translation from SQL to the English and vice versa. If the patch returns row_number it is wrong since there is no way for row_number to be a number of rows preceding or peer with R, is there? Regards, Vladimir Sitnikov
Re: [HACKERS] [WIP] In-place upgrade
Maybe. The difference is that I'm talking about converting tuples, not pages, so What happens when the data doesn't fit on the new page? is a meaningless question. No it's not, because as you pointed out you still need a way for the user to force it to happen sometime. Unless you're going to be happy with telling users they need to update all their tuples which would not be an online process. In any case it sounds like you're saying you want to allow multiple versions of tuples on the same page -- which a) would be much harder and b) doesn't solve the problem since the page still has to be converted sometime anyways. No, that's not what I'm suggesting. My thought was that any V3 page would be treated as if it were completely full, with the exception of a completely empty page which can be reinitialized as a V4 page. So you would never add any tuples to a V3 page, but you would need to update xmax, hint bits, etc. Eventually when all the tuples were dead you could reuse the page. ...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] [WIP] In-place upgrade
Robert Haas [EMAIL PROTECTED] writes: Maybe. The difference is that I'm talking about converting tuples, not pages, so What happens when the data doesn't fit on the new page? is a meaningless question. No it's not, because as you pointed out you still need a way for the user to force it to happen sometime. Unless you're going to be happy with telling users they need to update all their tuples which would not be an online process. In any case it sounds like you're saying you want to allow multiple versions of tuples on the same page -- which a) would be much harder and b) doesn't solve the problem since the page still has to be converted sometime anyways. No, that's not what I'm suggesting. My thought was that any V3 page would be treated as if it were completely full, with the exception of a completely empty page which can be reinitialized as a V4 page. So you would never add any tuples to a V3 page, but you would need to update xmax, hint bits, etc. Eventually when all the tuples were dead you could reuse the page. But there's no guarantee that will ever happen. Heikki claimed you would need a mechanism to convert the page some day and you said you proposed a system where that wasn't true. -- 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] Windowing Function Patch Review - Standard Conformance
2008/11/5 Vladimir Sitnikov [EMAIL PROTECTED]: Even though I understand the definition, your suggestion of COUNT(*) OVER (ORDER BY salary) doesn't make sense. Why does not that make sense? I have not read the spec, however Oracle has a default window specification in case there is only an order by clause. The default window is range between unbounded preceding and current row. count(*) over (order by salary range between unbounded preceding and current row) is perfectly identical to the number of rows preceding or peers to R by the definition, isn't it? I see here a word-by-word translation from SQL to the English and vice versa. If the patch returns row_number it is wrong since there is no way for row_number to be a number of rows preceding or peer with R, is there? I've got it. I had thought that implicit window framing specified by ORDER BY clause (such like above) would mean ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. But actually reading the spec more closely it says: Otherwise, WF consists of all rows of the partition of R that precede R or are peers of R in the window ordering of the window partition defined by the window ordering clause. So it means RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW as you pointed. And the result of count(*) OVER (ORDER BY salary) doesn't equal to row_number(). Now my assumption is broken. Let me take time to think about how to solve it... Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP] In-place upgrade
No, that's not what I'm suggesting. My thought was that any V3 page would be treated as if it were completely full, with the exception of a completely empty page which can be reinitialized as a V4 page. So you would never add any tuples to a V3 page, but you would need to update xmax, hint bits, etc. Eventually when all the tuples were dead you could reuse the page. But there's no guarantee that will ever happen. Heikki claimed you would need a mechanism to convert the page some day and you said you proposed a system where that wasn't true. What's the scenario you're concerned about? An old snapshot that never goes away? Can we lock the old and new pages, move the tuple to a V4 page, and update index entries without changing xmin/xmax? ...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] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
On Wed, Nov 5, 2008 at 7:34 AM, Ron Mayer [EMAIL PROTECTED] wrote: Brendan Jurd wrote: When I ran the regression tests, I got one failure in the new interval Fixed, and I did a bit more testing both with and without HAVE_INT64_TIMESTAMP. Confirmed, all regression tests now pass on my system with the updated patch. The C code has some small stylistic inconsistencies; ... ... spaces around binary operators are missing (e.g., (fsec0)). Thanks. Fixed these. ...function calls missing the space after the argument separator... I think I fixed all these now too. Awesome. As far as I can tell, you got them all. I don't have any further nits to pick about the code style. The changes to the documentation all look good. I did notice one final typo that I think was introduced in the latest version. doc/src/sgml/datatype.sgml:2270 has Nonstandardrd instead of Nonstandard. But, apart from that I have no further feedback. I will sign off on this one and mark it Ready for committer in the commitfest. Review of the other two patches coming soon to a mail client near you. Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] libpq does not manage SSL callbacks properly when other libraries are involved.
Magnus Hagander wrote:
Your analysis of this problem is right on target. When the SSL
callbacks were implemented for threaded libpq, there was never any
thought on the effect of unloading libpq while the callbacks were still
registered.
The attached patch unregisters the callback on the close of the last
libpq connection. Fortunately we require PQfinish() even if the
connection request failed, meaning there should be proper accounting of
the number of open connections with the method used in this patch.
We do leak some memory for every load/unload of libpq, but the leaks
extend beyond the SSL code to the rest of libpq so I didn't attempt to
address that in this patch (and no one has complained about it).
I also could have implemented a function to unload the SSL callbacks.
It would have to have been called before libpq was unloaded, but I
considered it inconvenient and unlikely to be adopted by applications
using libpq in the short-term.
I don't see why destroy_ssl_system sets up it's own mutex (that's also
called init_mutex). I think it'd make more sense to make the mutex
created in init_ssl_system() visible to the destroy function, and make
use of that one instead. You'll need to somehow interlock against these
two functions running on different threads after all.
Also, the code for destroying/unlinking appears to never be called.. The
callchain ends in pqsecure_destroy(), which is never called.
Thanks for the review, Magnus. I have adjusted the patch to use the
same mutex every time the counter is accessed, and adjusted the
pqsecure_destroy() call to properly decrement in the right place.
Also, I renamed the libpq global destroy function to be clearer
(the function is not exported).
--
Bruce Momjian [EMAIL PROTECTED]http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: src/backend/libpq/be-secure.c
===
RCS file: /cvsroot/pgsql/src/backend/libpq/be-secure.c,v
retrieving revision 1.85
diff -c -c -r1.85 be-secure.c
*** src/backend/libpq/be-secure.c 24 Oct 2008 12:24:35 - 1.85
--- src/backend/libpq/be-secure.c 5 Nov 2008 04:21:14 -
***
*** 88,94
static int verify_cb(int, X509_STORE_CTX *);
static void info_cb(const SSL *ssl, int type, int args);
static void initialize_SSL(void);
- static void destroy_SSL(void);
static int open_server_SSL(Port *);
static void close_SSL(Port *);
static const char *SSLerrmessage(void);
--- 88,93
***
*** 191,206
return 0;
}
/*
* Destroy global context
*/
void
secure_destroy(void)
{
- #ifdef USE_SSL
- destroy_SSL();
- #endif
}
/*
* Attempt to negotiate secure session.
--- 190,204
return 0;
}
+ #ifdef NOT_USED
/*
* Destroy global context
*/
void
secure_destroy(void)
{
}
+ #endif
/*
* Attempt to negotiate secure session.
***
*** 805,815
}
}
/*
! * Destroy global SSL context.
*/
static void
! destroy_SSL(void)
{
if (SSL_context)
{
--- 803,814
}
}
+ #ifdef NOT_USED
/*
! * Destroy global SSL context
*/
static void
! destroy_global_SSL(void)
{
if (SSL_context)
{
***
*** 817,822
--- 816,822
SSL_context = NULL;
}
}
+ #endif
/*
* Attempt to negotiate SSL connection.
Index: src/interfaces/libpq/fe-secure.c
===
RCS file: /cvsroot/pgsql/src/interfaces/libpq/fe-secure.c,v
retrieving revision 1.106
diff -c -c -r1.106 fe-secure.c
*** src/interfaces/libpq/fe-secure.c 24 Oct 2008 12:29:11 - 1.106
--- src/interfaces/libpq/fe-secure.c 5 Nov 2008 04:21:16 -
***
*** 93,98
--- 93,99
static int verify_cb(int ok, X509_STORE_CTX *ctx);
static int client_cert_cb(SSL *, X509 **, EVP_PKEY **);
static int init_ssl_system(PGconn *conn);
+ static void destroy_ssl_system(void);
static int initialize_SSL(PGconn *);
static void destroy_SSL(void);
static PostgresPollingStatusType open_client_SSL(PGconn *);
***
*** 105,110
--- 106,122
static bool pq_initssllib = true;
static SSL_CTX *SSL_context = NULL;
+
+ #ifdef ENABLE_THREAD_SAFETY
+ static int ssl_open_connections = 0;
+
+ #ifndef WIN32
+ static pthread_mutex_t ssl_config_mutex = PTHREAD_MUTEX_INITIALIZER;
+ #else
+ static pthread_mutex_t ssl_config_mutex = NULL;
+ static long win32_ssl_create_mutex = 0;
+ #endif
+
#endif
/*
***
*** 760,805
init_ssl_system(PGconn *conn)
{
#ifdef ENABLE_THREAD_SAFETY
! #ifndef WIN32
! static pthread_mutex_t init_mutex = PTHREAD_MUTEX_INITIALIZER;
! #else
! static pthread_mutex_t init_mutex = NULL;
! static long mutex_initlock = 0;
!
! if (init_mutex == NULL)
{
! while
Re: [HACKERS] [WIP] In-place upgrade
Robert Haas [EMAIL PROTECTED] writes: No, that's not what I'm suggesting. My thought was that any V3 page would be treated as if it were completely full, with the exception of a completely empty page which can be reinitialized as a V4 page. So you would never add any tuples to a V3 page, but you would need to update xmax, hint bits, etc. Eventually when all the tuples were dead you could reuse the page. But there's no guarantee that will ever happen. Heikki claimed you would need a mechanism to convert the page some day and you said you proposed a system where that wasn't true. What's the scenario you're concerned about? An old snapshot that never goes away? An old page which never goes away. New page formats are introduced for a reason -- to support new features. An old page lying around indefinitely means some pages can't support those new features. Just as an example, DBAs may be surprised to find out that large swathes of their database are still not protected by CRC checksums months or years after having upgraded to 8.4 (or even 8.5 or 8.6 or ...). They would certainly want a way to ensure all their data is upgraded. Can we lock the old and new pages, move the tuple to a V4 page, and update index entries without changing xmin/xmax? Not exactly. But regardless -- the point is we need to do something. (And then the argument goes that since we *have* to do that then we needn't bother with doing anything else. At least if we do it's just an optimization over just doing the whole page right away.) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP] In-place upgrade
Gregory Stark wrote: Robert Haas [EMAIL PROTECTED] writes: An old page which never goes away. New page formats are introduced for a reason -- to support new features. An old page lying around indefinitely means some pages can't support those new features. Just as an example, DBAs may be surprised to find out that large swathes of their database are still not protected by CRC checksums months or years after having upgraded to 8.4 (or even 8.5 or 8.6 or ...). They would certainly want a way to ensure all their data is upgraded. Then provide a manual mechanism to convert all pages? Joshua D. Drake -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP] In-place upgrade
Joshua D. Drake [EMAIL PROTECTED] writes: Gregory Stark wrote: Robert Haas [EMAIL PROTECTED] writes: An old page which never goes away. New page formats are introduced for a reason -- to support new features. An old page lying around indefinitely means some pages can't support those new features. Just as an example, DBAs may be surprised to find out that large swathes of their database are still not protected by CRC checksums months or years after having upgraded to 8.4 (or even 8.5 or 8.6 or ...). They would certainly want a way to ensure all their data is upgraded. Then provide a manual mechanism to convert all pages? The origin of this thread was the dispute over this claim: 1. You *will* need a function to convert a page from old format to new format. We do want to get rid of the old format pages eventually, whether it's during VACUUM, whenever a page is read in, or by using an extra utility. And that process needs to online. Please speak up now if you disagree with that. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [WIP] In-place upgrade
Gregory Stark wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Gregory Stark wrote: Robert Haas [EMAIL PROTECTED] writes: An old page which never goes away. New page formats are introduced for a reason -- to support new features. An old page lying around indefinitely means some pages can't support those new features. Just as an example, DBAs may be surprised to find out that large swathes of their database are still not protected by CRC checksums months or years after having upgraded to 8.4 (or even 8.5 or 8.6 or ...). They would certainly want a way to ensure all their data is upgraded. Then provide a manual mechanism to convert all pages? The origin of this thread was the dispute over this claim: 1. You *will* need a function to convert a page from old format to new format. We do want to get rid of the old format pages eventually, whether it's during VACUUM, whenever a page is read in, or by using an extra utility. And that process needs to online. Please speak up now if you disagree with that. I agree. Joshua D. Drake -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for SQL-Standard Interval output and decoupling DateStyle from IntervalStyle
Brendan Jurd wrote: The changes to the documentation all look good. I did notice one final typo that I think was introduced in the latest version. doc/src/sgml/datatype.sgml:2270 has Nonstandardrd instead of Nonstandard. Just checked in a fix to that one; and updated my website at http://0ape.com/postgres_interval_patches/ and pushed it to my (hopefully fixed now) git server. If this'll be the final update to this patch should I be posting it to the mailing list too for the archives? But, apart from that I have no further feedback. I will sign off on this one and mark it Ready for committer in the commitfest. Cool. I'm not sure if anyone still wants to weigh in on the approach I took for mixed-sign intervals, where '-1 2:03:04' gets interpreted differently depending on the date style, and '-1 +2:03:04' and '-1 -2:03:04' are the way I'm using to disambiguate them. Review of the other two patches coming soon to a mail client near you. Feel free to do them one-at-a-time too; since no doubt any issues with the first one will probably affect the second one too. I think I updated the other patches for the missing whitespace style issues my first patch had; but no doubt there could be other bad habits I have as well. Ron -- 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] Synchronous replication patch v1
On Wed, Nov 5, 2008 at 12:51 AM, Heikki Linnakangas [EMAIL PROTECTED] wrote: Fujii Masao wrote: On Fri, Oct 31, 2008 at 11:12 PM, Heikki Linnakangas [EMAIL PROTECTED] wrote: AFAICS, there's no security, at all. Anyone that can log in, can become a WAL sender, and receive all WAL for the whole cluster. One simple solution is to define the database only for replication. In this solution, we can handle the authentication for replication like the usual database access. That is, pg_hba.conf, the cooperation with a database role, etc are supported also in replication. So, a user can set up the authentication rules easily. You mean like a pseudo database name in pg_hba.conf, and in the startup message, that actually means connect for replication? Yeah, something like that sounds reasonable to me. Yes, I would define a pseudo database name for replication. A backend works as walsender only if it received the startup packet including the database name for replication. But, authentication and initialization continue till ReadyForQuery is sent. So, I assume that walsender starts replication after sending ReadyForQuery and receiving a message for replication. In this design, some features (e.g. post_auth_delay) are supported as they are. Another advantage is that a client can use lipq, such as PQconnectdb, for the connection for replication as they are. Between ReadyForQuery and a message for replication, a client can issue some queries. At least, my walreceiver would query timeline ID and request xlog-switch (In my previous patch, they are exchanged after walsender starts, but it has little flexibility). Of course, I have to create new function which returns current timeline ID. Initial sequence of walsender 1) process the startup packet 1-1) if the database name for replication is specified, a backend would declare postmaster that I am walsender (remove its backend from BackendList, etc). 2) authentication and initialization (BackendRun, PostgresMain) 3) walsender sends ReadyForQuery 4) a client queries timeline ID and requests xlog-switch 6) a client requests the start of WAL streaming 6-1) if a backend is not walsender, it refuses the request. I correct the code and post it ASAP. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Hot standby v5 patch - restarted replica changes to warm standby mode
Simon Riggs wrote: On Tue, 2008-11-04 at 18:33 +1300, Mark Kirkwood wrote: While doing some tests yesterday I ran into the situation where the standby database would appear to go back into 'warm' mode after it was restarted. The set of steps to reproduce the behaviour is: 1/ Setup master and replica with replica using pg_standby 2/ Initialize pgbench schema with size 100 in database 'postgres' 3/ Connect to replica, then disconnect (this step is not necessary I *think* - just for checking that connection works at this point!) 4/ Shutdown and restart the replica - there is no database has now reached consistent state message in the log, and you cannot connect How did you shutdown the database? Fast? Immediate mode acts just as it does on an unpatched server. Can you give more details of exactly what you did? Thanks. Not saying there isn't a problem, just don't understand what happened. Not being able to connect after a restart is a design feature, to protect you from running potentially invalid queries. Yeah - I was doing it wrong (using immediate). However retesting with 'fast' gets the same result on this platform (Freebsd 7.1). However on Linux (Ubuntu 8.04) 'fast' shutdown and restart work fine - somewhat puzzling - I'll try a fresh checkout on the Freebsd boxes, as there may be something rotten with the src tree I'm using there... I must may, this is the coolest feature - (from my point of view) the simplest way to do replication with the minimum of fuss - 3 config parameters! (archive_mode,archive_command and restore_command). Really nice addition for 8.4! regards Mark -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
