Re: [HACKERS] SSI predicate locking on heap -- tuple or row?
On Wed, Jun 01, 2011 at 05:09:09PM -0500, Kevin Grittner wrote: > I won't be shocked if Dan can come up with a shorter proof, but I'm > confident this one is solid. Well, so happens I wrote a proof on the airplane today, before I saw your mail. It's actually quite straightforward... (well, at least more so than I was expecting) > From many academic papers, there is well-established proof that > serialization anomalies can only occur under snapshot isolation when > there is a cycle in the graph of apparent order of execution of the > transactions, and that in such a cycle the following pattern of > rw-dependencies always occurs: > > Tin - - -> Tpivot - - -> Tout > > A rw-dependency (also called a rw-conflict) exists when a read by > one transaction doesn't see the write of another transaction because > the two transactions overlap, regardless of whether the read or the > write actually happens first. Since the reader doesn't see the work > of the writer, the reader appears to have executed first, regardless > of the actual order of snapshot acquisition or commits. The arrows > show the apparent order of execution of the transactions -- Tin > first, Tout last. Published papers have further proven that the > transaction which appears to have executed last of these three must > actually commit before either of the others for an anomaly to occur. We can actually say something slightly stronger than that last sentence: Tout has to commit before *any* other transaction in the cycle. That doesn't help us implement SSI, because we never try to look at an entire cycle, but it's still true and useful for proofs like this. Now, supposing Tin is read-only... Since there's a cycle, there must also be a transaction that precedes Tin in the serial order. Call it T0. (T0 might be the same transaction as Tout, but that doesn't matter.) There's an edge in the graph from T0 to Tin. It can't be a rw-conflict, because Tin was read-only, so it must be a ww- or wr-dependency. Either means T0 committed before Tin started. Because Tout committed before any other transaction in the cycle, Tout has to commit before T0 commits -- and thus before Tin starts. Dan -- Dan R. K. Ports MIT CSAILhttp://drkp.net/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Hacking gram.y Error syntax error at or near "MERGEJOIN"
Hi, I'm doing a hacking project which hacks the gram.y file and some other so that postgres can execute some query plan language to run a particular plan. I did some modifications and try run it. It compiled and installed successfully. But when I was trying to run a MergeJoin operation with keyword MERGEJOIN, it says "ERROR: syntax error at or near 'MERGEJOIN'". It seems postgres didn't recognize this keyword. Is it caused by any error inside gram.y? Or how can I trace this error? -- Best Regards Huang Qi Victor
Re: [HACKERS] Hacking gram.y Error syntax error at or near "MERGEJOIN"
Hello you should to add new keywords to postgresql/src/include/parser/kwlist.h (depends on pg version) attention - keywords must be in alphabet order Regards Pavel Stehule 2011/6/2 HuangQi : > Hi, > I'm doing a hacking project which hacks the gram.y file and some other > so that postgres can execute some query plan language to run a particular > plan. I did some modifications and try run it. It compiled and installed > successfully. But when I was trying to run a MergeJoin operation with > keyword MERGEJOIN, it says "ERROR: syntax error at or near 'MERGEJOIN'". It > seems postgres didn't recognize this keyword. Is it caused by any error > inside gram.y? Or how can I trace this error? > > -- > Best Regards > Huang Qi Victor > -- 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] Hacking gram.y Error syntax error at or near "MERGEJOIN"
Yes, I've also added mergejoin to kwlist.h as unreserved_keyword. In order to build AST, I also keep a new node struct in planoperators.h and a new node tag inside nodes.h. On 2 June 2011 15:49, Pavel Stehule wrote: > Hello > > you should to add new keywords to > postgresql/src/include/parser/kwlist.h (depends on pg version) > > attention - keywords must be in alphabet order > > Regards > > Pavel Stehule > > 2011/6/2 HuangQi : > > Hi, > > I'm doing a hacking project which hacks the gram.y file and some > other > > so that postgres can execute some query plan language to run a particular > > plan. I did some modifications and try run it. It compiled and installed > > successfully. But when I was trying to run a MergeJoin operation with > > keyword MERGEJOIN, it says "ERROR: syntax error at or near 'MERGEJOIN'". > It > > seems postgres didn't recognize this keyword. Is it caused by any error > > inside gram.y? Or how can I trace this error? > > > > -- > > Best Regards > > Huang Qi Victor > > > -- Best Regards Huang Qi Victor
Re: [HACKERS] Hacking gram.y Error syntax error at or near "MERGEJOIN"
Hello 2011/6/2 HuangQi : > Yes, I've also added mergejoin to kwlist.h as unreserved_keyword. In order > to build AST, I also keep a new node struct in planoperators.h and a new > node tag inside nodes.h. > then recheck alphabet order of keywords, else recheck bison rules. Pavel > On 2 June 2011 15:49, Pavel Stehule wrote: >> >> Hello >> >> you should to add new keywords to >> postgresql/src/include/parser/kwlist.h (depends on pg version) >> >> attention - keywords must be in alphabet order >> >> Regards >> >> Pavel Stehule >> >> 2011/6/2 HuangQi : >> > Hi, >> > I'm doing a hacking project which hacks the gram.y file and some >> > other >> > so that postgres can execute some query plan language to run a >> > particular >> > plan. I did some modifications and try run it. It compiled and installed >> > successfully. But when I was trying to run a MergeJoin operation with >> > keyword MERGEJOIN, it says "ERROR: syntax error at or near 'MERGEJOIN'". >> > It >> > seems postgres didn't recognize this keyword. Is it caused by any error >> > inside gram.y? Or how can I trace this error? >> > >> > -- >> > Best Regards >> > Huang Qi Victor >> > > > > > -- > Best Regards > Huang Qi Victor > -- 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] PQdeleteTuple function in libpq
Hello, Merlin.
You wrote:
MM> 2011/6/1 Pavel Golub :
>> Hello.
>>
>> I'm some kind of PQdeleteTuple function will be very usefull in libpq.
>> Because right now after deleting some record I need refetch result
>> set, or mark tuple as deleted and this is headache for me.
>>
>> So I checked fe-exec.c sources and wrote this:
>>
>> int PQdeleteTuple(PGresult *src, int tup_num)
>> {
>> if (!src)
>> return NULL;
>>
>> int i,
>> field;
>>
>> /* Invalid tup_num, must be < ntups */
>> if (tup_num < 0 || tup_num >= src->ntups)
>> return FALSE;
>>
>> free(src->tuples[tup_num]);
>>
>> for (i = tup_num; i < src->ntups - 1; i++)
>> {
>> src->tuples[i] = src->tuples[i + 1];
>> }
>> src->ntups--;
>> return TRUE;
>> }
>>
>> But I'm pretty sure, that "free(src->tuples[tup_num])" is bullshit!
>> Because memory is allocated by pqResultAlloc, which in turn plays with
>> memory blocks and so on...
>>
>> Can anyone help me in this?
>>
>> PS I'm not a C guru, so don't please kick me hard. :)
MM> well, you have PQaddTuple, but this was exposed mainly for the purpose
MM> of building a PQresult from outside the libpq library -- not so much
MM> to remove the 'constness' property of the PGResult. I have no
MM> philosophical objection to making the PGresult able to be manipulated
MM> in that fashion (although others might).
From this point of view why we have PQmakeEmptyPGresult, PQcopyResult,
PQsetResultAttrs, PQsetvalue and PQresultAlloc? If we have these
functions I suppose we must have one more to delete (or hide) some
tuples/attributes.
MM> You could maybe just NULL
MM> out tuples[i] and add some logic to various places to check that, like
MM> in PQgetvalue.
This is what I call headache. In this case to know rows number I
cannot use PQntuples, but need to iterate through all tuples checking
them for NULL or smth.
MM> But before going down that road you need to make the case why this
MM> should be handled in the library and not in your code -- PGresult
MM> memory is slab allocated and therefore can only grow in size -- not
MM> shrink and as such is not so much designed as a general purpose client
MM> side dataset in the high level sense.
Thinking of this I propose to hide tuples and not to eliminate\free
them, because PQclear will free all PGResult resources.
MM> merlin
--
With best wishes,
Pavel mailto:pa...@gf.microolap.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] PQdeleteTuple function in libpq
Hello, Andrew. You wrote: AC> On 6/1/2011 11:43 AM, Pavel Golub wrote: >> Hello. >> >> I'm some kind of PQdeleteTuple function will be very usefull in libpq. >> Because right now after deleting some record I need refetch result >> set, or mark tuple as deleted and this is headache for me. >> AC> IMHO, this should be handled by the application. You could track tuples AC> removed in an int[] or copy the result set into an application defined AC> array of C structures. I've always been under the impression that AC> PGresult objects are immutable once delivered to the application. Andrew, why we have PQmakeEmptyPGresult, PQcopyResult, PQsetResultAttrs, PQsetvalue and PQresultAlloc in this case? Of course there's no big deal with their absence but let's be consistent. -- With best wishes, Pavel mailto:pa...@gf.microolap.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] PQdeleteTuple function in libpq
Hello.
So having studied the fe-exec.c sources, I came to this conclusion:
we may just ignore deleted tuple and it will be destroyed by
PQclear automatically, becuase PQclear deals with memory blocks.
int PQdeleteTuple(PGresult *src, int tup_num)
{
if (!src)
return NULL;
int i,
field;
/* Invalid tup_num, must be < ntups */
if (tup_num < 0 || tup_num >= src->ntups)
return FALSE;
for (i = tup_num; i < src->ntups - 1; i++)
{
src->tuples[i] = src->tuples[i + 1];
}
src->ntups--;
return TRUE;
}
I also checked pqAddTuple, PQcopyResult and PQSetValue, they are OK with this
solution.
Am I correct with this?
You wrote:
PG> Hello.
PG> I'm some kind of PQdeleteTuple function will be very usefull in libpq.
PG> Because right now after deleting some record I need refetch result
PG> set, or mark tuple as deleted and this is headache for me.
PG> So I checked fe-exec.c sources and wrote this:
PG> int PQdeleteTuple(PGresult *src, int tup_num)
PG> {
PG> if (!src)
PG> return NULL;
PG> int i,
PG> field;
PG>
PG> /* Invalid tup_num, must be < ntups */
PG> if (tup_num < 0 || tup_num >= src->ntups)
PG> return FALSE;
PG> free(src->tuples[tup_num]);
PG>
PG> for (i = tup_num; i < src->ntups - 1; i++)
PG> {
PG> src->tuples[i] = src->tuples[i + 1];
PG> }
PG> src->ntups--;
PG> return TRUE;
PG> }
PG> But I'm pretty sure, that "free(src->tuples[tup_num])" is bullshit!
PG> Because memory is allocated by pqResultAlloc, which in turn plays with
PG> memory blocks and so on...
PG> Can anyone help me in this?
PG> PS I'm not a C guru, so don't please kick me hard. :)
PG> Thanks.
--
With best wishes,
Pavel mailto:pa...@gf.microolap.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] Cube Index Size
Can we figure out some information about index i.e. whet is the height of index tree, how many values are placed in one leaf node and one non leaf level node? http://www.sigaev.ru/cvsweb/cvsweb.cgi/gevel/ -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ -- 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: enhanced get diagnostics statement 2
Hello This patch enhances a GET DIAGNOSTICS statement functionality. It adds a possibility of access to exception's data. These data are stored on stack when exception's handler is activated - and these data are access-able everywhere inside handler. It has a different behave (the content is immutable inside handler) and therefore it has modified syntax (use keyword STACKED). This implementation is in conformance with ANSI SQL and SQL/PSM - implemented two standard fields - RETURNED_SQLSTATE and MESSAGE_TEXT and three PostgreSQL specific fields - PG_EXCEPTION_DETAIL, PG_EXCEPTION_HINT and PG_EXCEPTION_CONTEXT. The GET STACKED DIAGNOSTICS statement is allowed only inside exception's handler. When it is used outside handler, then diagnostics exception 0Z002 is raised. This patch has no impact on performance. It is just interface to existing stacked 'edata' structure. This patch doesn't change a current behave of GET DIAGNOSTICS statement. CREATE OR REPLACE FUNCTION public.stacked_diagnostics_test02() RETURNS void LANGUAGE plpgsql AS $function$ declare _detail text; _hint text; _message text; begin perform ... exception when others then get stacked diagnostics _message = message_text, _detail = pg_exception_detail, _hint = pg_exception_hint; raise notice 'message: %, detail: %, hint: %', _message, _detail, _hint; end; $function$ All regress tests was passed. Regards Pavel Stehule *** ./doc/src/sgml/plpgsql.sgml.orig 2011-05-18 14:22:28.0 +0200 --- ./doc/src/sgml/plpgsql.sgml 2011-06-02 09:43:49.682013158 +0200 *** *** 1387,1393 command, which has the form: ! GET DIAGNOSTICS variable = item , ... ; This command allows retrieval of system status indicators. Each --- 1387,1393 command, which has the form: ! GET CURRENT | STACKED DIAGNOSTICS variable = item , ... ; This command allows retrieval of system status indicators. Each *** *** 1486,1491 --- 1486,1516 affect only the current function. + + Inside a exception handler is possible to use a stacked diagnostics statement. It + allows a access to exception's data: the RETURNED_SQLSTATE contains + a SQLSTATE of handled exception. MESSAGE_TEXT contains a message text, + PG_EXCEPTION_DETAIL has a text that is shown as exception detail, + PG_EXCEPTION_HINT has a hint related to catched exception. + PG_EXCEPTION_CONTEXT contains a lines that describes call stack. These + variables holds a text value. When some field of exception are not filled, then related + variable contains a empty string, + + + + An example: + + BEGIN + ... + EXCEPTION WHEN OTHERS THEN + GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT, + text_var2 = PG_EXCEPTION_DETAIL, + text_var3 = PG_EXCEPTION_HINT; + END; + + + + *** ./src/backend/utils/errcodes.txt.orig 2011-05-18 14:22:29.0 +0200 --- ./src/backend/utils/errcodes.txt 2011-06-01 20:43:16.128831780 +0200 *** *** 132,137 --- 132,141 0P000EERRCODE_INVALID_ROLE_SPECIFICATION invalid_role_specification + Section: Class 0Z - Diagnostics Exception + 0Z000EERRCODE_DIAGNOSTICS_EXCEPTION diagnostics_exception + 0Z002EERRCODE_STACKED_DIAGNOSTICS_ACCESSED_WITHOUT_ACTIVE_HANDLERstacked_diagnostics_accessed_without_active_handler + Section: Class 20 - Case Not Found 2EERRCODE_CASE_NOT_FOUND case_not_found *** ./src/pl/plpgsql/src/gram.y.orig 2011-05-18 19:41:56.755678378 +0200 --- ./src/pl/plpgsql/src/gram.y 2011-06-02 08:52:31.687830966 +0200 *** *** 206,211 --- 206,212 %type getdiag_list %type getdiag_list_item %type getdiag_item getdiag_target + %type getdiag_opt %type opt_scrollable %type opt_fetch_direction *** *** 250,256 --- 251,259 %token K_CLOSE %token K_COLLATE %token K_CONSTANT + %token K_CONTEXT %token K_CONTINUE + %token K_CURRENT %token K_CURSOR %token K_DEBUG %token K_DECLARE *** *** 263,268 --- 266,274 %token K_END %token K_ERRCODE %token K_ERROR + %token K_EXCEPTION_CONTEXT + %token K_EXCEPTION_DETAIL + %token K_EXCEPTION_HINT %token K_EXCEPTION %token K_EXECUTE %token K_EXIT *** *** 284,289 --- 290,296 %token K_LOG %token K_LOOP %token K_MESSAGE + %token K_MESSAGE_TEXT %token K_MOVE %token K_NEXT %token K_NO *** *** 300,311 --- 307,320 %token K_RELATIVE %token K_RESULT_OID %token K_RETURN + %token K_RETURNED_SQLSTATE %token K_REVERSE %token K_ROWTYPE %token K_ROW_COUNT %token K_SCROLL %token K_SLICE %token K_SQLSTATE + %token
Re: [HACKERS] Re: patch review : Add ability to constrain backend temporary file space
On 02/06/11 18:34, Jaime Casanova wrote: On Wed, Jun 1, 2011 at 6:35 PM, Mark Kirkwood wrote: I've created a new patch (attached) Hi Mark, A few comments: - why only superusers can set this? if this is a per-backend setting, i don't see the problem in allowing normal users to restrict their own queries Yeah, that's a good point, I was thinking that as a resource control parameter it might be good to prevent casual users increasing their limit. However the most likely use case would be ad-hoc query tools that don't have the ability to do SET anyway. Hmm - what do you think? - why the calculations are done as double? + if (temporary_files_size / 1024.0> (double)work_disk) I originally coded this with the idea that the guc would (or could) be a double - to allow for seriously big limits in data warehousing situations etc. But subsequent discussion led to that being discarded. However work_disk can go to INT_MAX * 1024 bytes so I need to make sure that whatever datatype I use can handle that - double seemed sufficient. - the patch adds this to serial_schedule but no test has been added... diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index bb654f9..325cb3d 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -127,3 +127,4 @@ test: largeobject test: with test: xml test: stats +test: resource Bugger - I think I forgot to 'git add' em before doing the diff. I can sense a v5 coming on. -- 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] Hacking gram.y Error syntax error at or near "MERGEJOIN"
On 06/02/2011 03:28 AM, HuangQi wrote: Hi, I'm doing a hacking project which hacks the gram.y file and some other so that postgres can execute some query plan language to run a particular plan. I did some modifications and try run it. It compiled and installed successfully. But when I was trying to run a MergeJoin operation with keyword MERGEJOIN, it says "ERROR: syntax error at or near 'MERGEJOIN'". It seems postgres didn't recognize this keyword. Is it caused by any error inside gram.y? Or how can I trace this error? Without seeing your grammar changes and the statement you're trying to execute it's pretty much impossible to tell. 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] BLOB support
On ons, 2011-06-01 at 22:00 +0200, Radosław Smogura wrote: > I partialy implemented following missing LOBs types. Requirement for this was > to give ability to create (B/C)LOB columns and add casting functionality e.g. > SET my_clob = 'My long text'. > > Idea is as follow: > 0. Blob is two state object: 1st in memory contains just bytea, serialized > contains Oid of large object. > 1. Each type has additional boolean haslobs, which is set recursivly. > 2. Relation has same bool haslobs (used to speed up tables without LOBs) > 3. When data are inserted/updated then "special" function is called and tuple > is modified in this way all LOBs are serialized to (old) LOB table and just > Oid is stored. > 4. When removed LOB is removed from (old) LOB table. Superficially, this looks like a reimplementation of TOAST. What functionality exactly do you envision that the BLOB and CLOB types would need to have that would warrant treating them different from, say, bytea and text? -- 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] BLOB support
2011/6/2 Peter Eisentraut : > On ons, 2011-06-01 at 22:00 +0200, Radosław Smogura wrote: >> I partialy implemented following missing LOBs types. Requirement for this was >> to give ability to create (B/C)LOB columns and add casting functionality e.g. >> SET my_clob = 'My long text'. >> >> Idea is as follow: >> 0. Blob is two state object: 1st in memory contains just bytea, serialized >> contains Oid of large object. >> 1. Each type has additional boolean haslobs, which is set recursivly. >> 2. Relation has same bool haslobs (used to speed up tables without LOBs) >> 3. When data are inserted/updated then "special" function is called and tuple >> is modified in this way all LOBs are serialized to (old) LOB table and just >> Oid is stored. >> 4. When removed LOB is removed from (old) LOB table. > > Superficially, this looks like a reimplementation of TOAST. What > functionality exactly do you envision that the BLOB and CLOB types would > need to have that would warrant treating them different from, say, bytea > and text? > a streaming for bytea could be nice. A very large bytea are limited by query size - processing long query needs too RAM, Pavel > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hacking gram.y Error syntax error at or near "MERGEJOIN"
On 02.06.2011 15:16, Andrew Dunstan wrote: On 06/02/2011 03:28 AM, HuangQi wrote: Hi, I'm doing a hacking project which hacks the gram.y file and some other so that postgres can execute some query plan language to run a particular plan. I did some modifications and try run it. It compiled and installed successfully. But when I was trying to run a MergeJoin operation with keyword MERGEJOIN, it says "ERROR: syntax error at or near 'MERGEJOIN'". It seems postgres didn't recognize this keyword. Is it caused by any error inside gram.y? Or how can I trace this error? Without seeing your grammar changes and the statement you're trying to execute it's pretty much impossible to tell. Yeah. One more guess is that you didn't include the keyword in the keyword list at the end of gram.y. There's script to check various keyword-related things at src/tools/check_keywords.pl -- 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] BLOB support
On Thu, Jun 02, 2011 at 02:58:52PM +0200, Pavel Stehule wrote: > 2011/6/2 Peter Eisentraut : > > On ons, 2011-06-01 at 22:00 +0200, Radosław Smogura wrote: > >> I partialy implemented following missing LOBs types. Requirement for this > >> was > >> to give ability to create (B/C)LOB columns and add casting functionality > >> e.g. > >> SET my_clob = 'My long text'. > >> > >> Idea is as follow: > >> 0. Blob is two state object: 1st in memory contains just bytea, serialized > >> contains Oid of large object. > >> 1. Each type has additional boolean haslobs, which is set recursivly. > >> 2. Relation has same bool haslobs (used to speed up tables without LOBs) > >> 3. When data are inserted/updated then "special" function is called and > >> tuple > >> is modified in this way all LOBs are serialized to (old) LOB table and just > >> Oid is stored. > >> 4. When removed LOB is removed from (old) LOB table. > > > > Superficially, this looks like a reimplementation of TOAST. What > > functionality exactly do you envision that the BLOB and CLOB types would > > need to have that would warrant treating them different from, say, bytea > > and text? > > > > a streaming for bytea could be nice. A very large bytea are limited by > query size - processing long query needs too RAM, > > Pavel > +1 for a streaming interface to bytea/text. I do agree that there is no need to reinvent the TOAST architecture with another name, just improve the existing implementation. 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: [HACKERS] BLOB support
Hello, Pavel. You wrote: PS> 2011/6/2 Peter Eisentraut : >> On ons, 2011-06-01 at 22:00 +0200, Radosław Smogura wrote: >>> I partialy implemented following missing LOBs types. Requirement for this >>> was >>> to give ability to create (B/C)LOB columns and add casting functionality >>> e.g. >>> SET my_clob = 'My long text'. >>> >>> Idea is as follow: >>> 0. Blob is two state object: 1st in memory contains just bytea, serialized >>> contains Oid of large object. >>> 1. Each type has additional boolean haslobs, which is set recursivly. >>> 2. Relation has same bool haslobs (used to speed up tables without LOBs) >>> 3. When data are inserted/updated then "special" function is called and >>> tuple >>> is modified in this way all LOBs are serialized to (old) LOB table and just >>> Oid is stored. >>> 4. When removed LOB is removed from (old) LOB table. >> >> Superficially, this looks like a reimplementation of TOAST. What >> functionality exactly do you envision that the BLOB and CLOB types would >> need to have that would warrant treating them different from, say, bytea >> and text? >> PS> a streaming for bytea could be nice. A very large bytea are limited by PS> query size - processing long query needs too RAM, LO (oid) solves this, doesn't it? PS> Pavel >> >> >> -- >> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-hackers >> -- With best wishes, Pavel mailto:pa...@gf.microolap.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] BLOB support
On Thu, 02 Jun 2011 15:39:39 +0300, Peter Eisentraut wrote: On ons, 2011-06-01 at 22:00 +0200, Radosław Smogura wrote: I partialy implemented following missing LOBs types. Requirement for this was to give ability to create (B/C)LOB columns and add casting functionality e.g. SET my_clob = 'My long text'. Idea is as follow: 0. Blob is two state object: 1st in memory contains just bytea, serialized contains Oid of large object. 1. Each type has additional boolean haslobs, which is set recursivly. 2. Relation has same bool haslobs (used to speed up tables without LOBs) 3. When data are inserted/updated then "special" function is called and tuple is modified in this way all LOBs are serialized to (old) LOB table and just Oid is stored. 4. When removed LOB is removed from (old) LOB table. Superficially, this looks like a reimplementation of TOAST. May look similar, but Datums doesn't support more then 32bit length and size of any field is limited to 1GB, am I right? Serializations is only for casting simple values < 1GB, and simple operations, to do not overhead creation of hundreds LOBs. What functionality exactly do you envision that the BLOB and CLOB types would need to have that would warrant treating them different from, say, bytea and text? Actually I thought about less sophisticated support of LOBs, supporting casting and copying data, as well known form other databases idea that LOBs are not "downloaded" during normal query execution (just ids are taken). Currently, e.g. LOBs are not connected with tables, so deleting rows doesn't delete LOB, table actually holds Oid of large objects, no support for casting to/from LOB, no support for CLOBS. Some drivers try to emulate BLOBs/CLOBs, but it is not perfect, mainly from above reasons. Regards, Radek -- 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] PQdeleteTuple function in libpq
On Thu, Jun 2, 2011 at 3:24 AM, Pavel Golub wrote: > MM> well, you have PQaddTuple, but this was exposed mainly for the purpose > MM> of building a PQresult from outside the libpq library -- not so much > MM> to remove the 'constness' property of the PGResult. I have no > MM> philosophical objection to making the PGresult able to be manipulated > MM> in that fashion (although others might). > > From this point of view why we have PQmakeEmptyPGresult, PQcopyResult, > PQsetResultAttrs, PQsetvalue and PQresultAlloc? If we have these > functions I suppose we must have one more to delete (or hide) some > tuples/attributes. These functions were basically supported for libpqtypes -- a libpq wrapping library that needed to be able to construct a result outside of libpq...libpqtypes uses the result api to expose arrays and composite types sent over the wire from the server. However, once generated the result is basically immutable. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: patch review : Add ability to constrain backend temporary file space
On Wed, Jun 1, 2011 at 7:35 PM, Mark Kirkwood wrote: > Done - 'work_disk' it is to match 'work_mem'. I guess I'm bikeshedding here, but I'm not sure I really buy this parallel. work_mem is primarily a query planner parameter; it says, if you're going to need more memory than this, then you have to execute the plan some other way. This new parameter is not a query planner paramater AIUI - its job is to KILL things if they exceed the limit. In that sense it's more like statement_timeout. I can imagine us wanting more parameters like this too. Kill the query if it... ...takes too long (statement_timeout) ...uses too much temporary file space (the current patch) ...uses too much CPU time ...uses too much RAM ...generates too much disk I/O ...has too high an estimated cost ...others? So I'm not sure work_disk is a great name. Actually, work_mem is already not a great name even for what it is, but at any rate I think this is something different. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] BLOB support
2011/6/2 Pavel Golub : > Hello, Pavel. > > You wrote: > > PS> 2011/6/2 Peter Eisentraut : >>> On ons, 2011-06-01 at 22:00 +0200, Radosław Smogura wrote: I partialy implemented following missing LOBs types. Requirement for this was to give ability to create (B/C)LOB columns and add casting functionality e.g. SET my_clob = 'My long text'. Idea is as follow: 0. Blob is two state object: 1st in memory contains just bytea, serialized contains Oid of large object. 1. Each type has additional boolean haslobs, which is set recursivly. 2. Relation has same bool haslobs (used to speed up tables without LOBs) 3. When data are inserted/updated then "special" function is called and tuple is modified in this way all LOBs are serialized to (old) LOB table and just Oid is stored. 4. When removed LOB is removed from (old) LOB table. >>> >>> Superficially, this looks like a reimplementation of TOAST. What >>> functionality exactly do you envision that the BLOB and CLOB types would >>> need to have that would warrant treating them different from, say, bytea >>> and text? >>> > > PS> a streaming for bytea could be nice. A very large bytea are limited by > PS> query size - processing long query needs too RAM, > > LO (oid) solves this, doesn't it? partially There is a few disadvantages LO against bytea, so there are requests for "smarter" API for bytea. Significant problem is different implementation of LO for people who have to port application to PostgreSQL from Oracle, DB2. There are some JDBC issues too. For me - main disadvantage of LO in one space for all. Bytea removes this disadvantage, but it is slower for lengths > 20 MB. It could be really very practical have a possibility insert some large fields in second NON SQL stream. Same situation is when large bytea is read. Pavel > > PS> Pavel > >>> >>> >>> -- >>> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-hackers >>> > > > > > -- > With best wishes, > Pavel mailto:pa...@gf.microolap.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] BLOB support
On Thu, Jun 2, 2011 at 9:29 AM, Radosław Smogura wrote: >> What functionality exactly do you envision that the BLOB and CLOB types >> would >> need to have that would warrant treating them different from, say, bytea >> and text? > > Actually I thought about less sophisticated support of LOBs, supporting > casting and copying data, as well known form other databases idea that LOBs > are not "downloaded" during normal query execution (just ids are taken). > Currently, e.g. LOBs are not connected with tables, so deleting rows doesn't > delete LOB, table actually holds Oid of large objects, no support for > casting to/from LOB, no support for CLOBS. Some drivers try to emulate > BLOBs/CLOBs, but it is not perfect, mainly from above reasons. But these problems can be fixed without inventing a completely new system, I think. Or at least we should try. I can see the point of a data type that is really a pointer to a LOB, and the LOB gets deleted when the pointer is removed, but I don't think that should require far-reaching changes all over the system (like relhaslobs) to make it work efficiently. I think you need to start with a problem statement, get agreement that it is a problem and on what the solution should be, and then go write the code to implement that solution. This is a classic example of writing the code first and then working backwards toward the problem you're trying to solve, and that rarely works out well for the reasons that you're now finding out: people may not agree with your proposed solution, they may want things done differently, and now you're stuck reworking code that you've already written. It's much easier to change a design document than it is to rewrite code. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Identifying no-op length coercions
On May 24, 2011, at 12:15 AM, Noah Misch wrote: > On Mon, May 23, 2011 at 03:01:40PM -0400, Tom Lane wrote: >> Noah Misch writes: >>> Good deal. Given that conclusion, the other policy decision I anticipate >>> affecting this particular patch is the choice of syntax. Presumably, it >>> will be >>> a new common_func_opt_item. When I last looked at the keywords list and >>> tried >>> to come up with something, these were the best I could do: >> >>> CREATE FUNCTION ... PARSER MAPPING helperfunc(args) >>> CREATE FUNCTION ... PLANS CONVERSION helperfunc(args) >> >> We could go with your previous idea of not bothering to expose this in >> the SQL syntax. Given that the helper function is going to have a >> signature along the lines of "(internal, internal) -> internal", it's >> going to be difficult for anyone to use it for non-builtin functions >> anyhow. >> >> But if you really don't like that, what about > > That would be just fine with me. We can always expose it later. > >> >> TRANSFORM helperfunctionname >> >> Although TRANSFORM isn't currently a keyword for us, it is a >> non-reserved keyword in SQL:2008, and it seems possible that we might >> someday think about implementing the associated features. > > I was thinking of that word too, along the lines of "PLAN TRANSFORM FUNCTION > helperfunctionname". Then again, that wrongly sounds somewhat like it's > transforming planner nodes. Perhaps plain TRANSFORM or TRANSFORM FUNCTION > would > be the way to go. Looks like this thread has silently died out. Is there an agreement on the syntax and implementation part? We (CMD) have a customer, who is interested in pushing this through, so, if we have a patch, I'd be happy to assist in reviewing it. -- Alexey Klyukin 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] PQdeleteTuple function in libpq
On 6/2/2011 4:28 AM, Pavel Golub wrote: Hello, Andrew. You wrote: AC> On 6/1/2011 11:43 AM, Pavel Golub wrote: Hello. I'm some kind of PQdeleteTuple function will be very usefull in libpq. Because right now after deleting some record I need refetch result set, or mark tuple as deleted and this is headache for me. AC> IMHO, this should be handled by the application. You could track tuples AC> removed in an int[] or copy the result set into an application defined AC> array of C structures. I've always been under the impression that AC> PGresult objects are immutable once delivered to the application. Andrew, why we have PQmakeEmptyPGresult, PQcopyResult, PQsetResultAttrs, PQsetvalue and PQresultAlloc in this case? Of course there's no big deal with their absence but let's be consistent. I'm not entirely sure what you are trying to do, but can't you use PQmakeEmptyPGresult, PQsetResultAttrs and PQsetvalue to construct a result that excludes the tuples you don't want followed by a PQclear(initial_result)? -- Andrew Chernow eSilo, LLC global backup http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgpool versus sequences
On Wed, Jun 1, 2011 at 7:47 PM, Alvaro Herrera wrote: > Excerpts from Tatsuo Ishii's message of mié jun 01 19:08:16 -0400 2011: >> What pgpool really wanted to do was locking sequence tables, not >> locking rows in sequences. I wonder why the former is not allowed. > > Yeah -- why is LOCK SEQUENCE foo_seq not allowed? Seems a simple thing > to have. It cause a grammar conflict. Since SEQUENCE and NOWAIT are both unreserved keywords, it's not clear to the parser whether "LOCK SEQUENCE NOWAIT" means to lock a table called SEQUENCE without waiting, or whether it means to lock a sequence called NOWAIT. Tom and I discussed possible ways of fixing this on -hackers a few months ago. Currently the syntax for LOCK is: LOCK [ TABLE ] [ ONLY ] name [, ...] [ IN lockmode MODE ] [ NOWAIT ]; I suggested fixing this by making TABLE required, thus: LOCK TABLE [ ONLY ] name [, ...] [ IN lockmode MODE ] [ NOWAIT ]; Tom suggested fixing it by making NOWAIT require IN lockmode MODE, thus: LOCK [ TABLE ] [ ONLY ] name [,...] [ IN lockmode MODE [ NOWAIT ]]; My proposed fix is probably more likely to break people's applications, but Tom's isn't completely free from that possibility either. It's also somewhat counterintuitive IMV. The best option might be to come up with some completely new syntax that is a little better designed than the current one, maybe along the lines of the extensible-options syntax used by EXPLAIN. The trouble is that the first word of the command would probably have to be something other than LOCK if we don't want to break backward compatibility with the existing syntax in some way, and there aren't too many good synonyms for LOCK. LATCH? FASTEN? Blech. We're probably going to end up having to make a compatibility break here if we want to support this. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgpool versus sequences
On Wed, Jun 1, 2011 at 6:53 PM, Tom Lane wrote: > Please note also that what pgpool users have got right now is a time > bomb, which is not better than immediately-visible breakage. I would > prefer to try to get this change out ahead of widespread adoption of the > broken pgpool version. Hmm, I gather from what Tatsuo is saying at the web site that this has only been broken since the release of 3.0 on February 23rd, so given that I think your approach makes sense. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgpool versus sequences
Robert Haas writes: > On Wed, Jun 1, 2011 at 7:47 PM, Alvaro Herrera > wrote: >> Yeah -- why is LOCK SEQUENCE foo_seq not allowed? Seems a simple thing >> to have. > It cause a grammar conflict. That's a lot of work for a purely cosmetic issue, though. What would be trivial is to let this work: regression=# create sequence s1; CREATE SEQUENCE regression=# begin; BEGIN regression=# lock table s1; ERROR: "s1" is not a table We should do that anyway, even if we put in the effort to support the other 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] PQdeleteTuple function in libpq
Hello, Andrew. You wrote: AC> On 6/2/2011 4:28 AM, Pavel Golub wrote: >> Hello, Andrew. >> >> You wrote: >> >> AC> On 6/1/2011 11:43 AM, Pavel Golub wrote: Hello. I'm some kind of PQdeleteTuple function will be very usefull in libpq. Because right now after deleting some record I need refetch result set, or mark tuple as deleted and this is headache for me. >> >> AC> IMHO, this should be handled by the application. You could track tuples >> AC> removed in an int[] or copy the result set into an application defined >> AC> array of C structures. I've always been under the impression that >> AC> PGresult objects are immutable once delivered to the application. >> >> >> Andrew, why we have PQmakeEmptyPGresult, PQcopyResult, >> PQsetResultAttrs, PQsetvalue and PQresultAlloc in this case? Of course >> there's no big deal with their absence but let's be consistent. >> AC> I'm not entirely sure what you are trying to do, but can't you use AC> PQmakeEmptyPGresult, PQsetResultAttrs and PQsetvalue to construct a AC> result that excludes the tuples you don't want followed by a AC> PQclear(initial_result)? Well, yes. I can. But don't you think it's too complex? Plus such approach uses twice as much memory. -- With best wishes, Pavel mailto:pa...@gf.microolap.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] PQdeleteTuple function in libpq
AC> IMHO, this should be handled by the application. You could track tuples AC> removed in an int[] or copy the result set into an application defined AC> array of C structures. I've always been under the impression that AC> PGresult objects are immutable once delivered to the application. Andrew, why we have PQmakeEmptyPGresult, PQcopyResult, PQsetResultAttrs, PQsetvalue and PQresultAlloc in this case? Of course there's no big deal with their absence but let's be consistent. I'm not entirely sure what you are trying to do, but can't you use PQmakeEmptyPGresult, PQsetResultAttrs and PQsetvalue to construct a result that excludes the tuples you don't want followed by a PQclear(initial_result)? Actually the best solution would be to call PQcopyResult with all PG_COPYRES_XXX flags enabled except PG_COPYRES_TUPLES. Now call PQsetvalue for each tuple you want to add. -- Andrew Chernow eSilo, LLC global backup http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bad UI design: pg_ctl and data_directory
On Wed, Jun 1, 2011 at 7:07 PM, Tom Lane wrote: > Alvaro Herrera writes: >> Excerpts from Robert Haas's message of mié jun 01 18:22:56 -0400 2011: >>> ISTM that it would be useful to run postgres in a mode where it >>> doesn't actually try to start up the database, but parses >>> postgresql.conf and then exits, perhaps printing out the value of a >>> certain GUC as it does so. In this case, data_directory. > >> I had the same thought, and wondered if we could use the feature >> elsewhere. > > This was suggested quite some time ago, IIRC, but we never got round to it. > > The main problem in the current context is that it only fixes the issue > so long as you ignore the possibility that relevant values were > specified on the command line or via environment variables, rather than > coming directly from the config file. PGDATA is thus a particular > hazard here: all you need is to be running with a different PGDATA > setting in your environment than was used when "pg_ctl start" was > issued, and you're hosed. I guess I'm missing something here. If you change PGDATA, you're going to be working on a different cluster, but that's what you asked for. I guess there could be a problem if you used pg_ctl -D dir start, and postgres --tell-me-the-data-dir relied on PGDATA in telling you what the data directory should be, but that seems simple to work around: just have -D dir set $PGDATA before invoking postgres. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: patch review : Add ability to constrain backend temporary file space
Robert Haas writes: > So I'm not sure work_disk is a great name. I agree. Maybe something along the lines of "temp_file_limit"? Also, once you free yourself from the analogy to work_mem, you could adopt some more natural unit than KB. I'd think MB would be a practical unit size, and would avoid (at least for the near term) the need to make the parameter a float. 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] BLOB support
Robert Haas writes: > But these problems can be fixed without inventing a completely new > system, I think. Or at least we should try. I can see the point of a > data type that is really a pointer to a LOB, and the LOB gets deleted > when the pointer is removed, but I don't think that should require > far-reaching changes all over the system (like relhaslobs) to make it > work efficiently. I think you need to start with a problem statement, > get agreement that it is a problem and on what the solution should be, > and then go write the code to implement that solution. Yes. I think the appropriate problem statement is "provide streaming access to large field values, as an alternative to just fetching/storing the entire value at once". I see no good reason to import the entire messy notion of LOBS/CLOBS. (The fact that other databases have done it is not a good reason.) For primitive types like text or bytea it seems pretty obvious what "streaming access" should entail, but it might be interesting to consider what it should mean for structured types. For instance, if I have an array field with umpteen zillion elements, it might be nice to fetch them one at a time using the streaming access mechanism. I don't say that that has to be in the first version, but it'd be a good idea to keep that in the back of your head so you don't design a dead-end solution that can't be extended in that direction. 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] pgpool versus sequences
Robert Haas writes: > Ugh. We are already stuck supporting all kinds of backward > compatibility cruft in tablecmds.c as a result of the fact that you > used to have to use ALTER TABLE to operate on views and sequences. > The whole thing is confusing and a mess. [ shrug... ] I don't find it so. We have a convention that TABLE is an umbrella term for all applicable relation types. End of story. Even if you disagree with that, the convention does exist, and making LOCK the one command type that disobeys it doesn't seem like a good plan. 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] Please test peer (socket ident) auth on *BSD
Marko Kreen writes: > Here's my attempt for it. As conditional port module seems trouble, > I set up an unconditional pgGetpeereid() that is always defined. -1 ... why would you think that a conditional substitution is trouble? We have plenty of others. 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] Re: patch review : Add ability to constrain backend temporary file space
2011/6/2 Robert Haas : > On Wed, Jun 1, 2011 at 7:35 PM, Mark Kirkwood > wrote: >> Done - 'work_disk' it is to match 'work_mem'. > > I guess I'm bikeshedding here, but I'm not sure I really buy this > parallel. work_mem is primarily a query planner parameter; it says, > if you're going to need more memory than this, then you have to > execute the plan some other way. This new parameter is not a query > planner paramater AIUI - its job is to KILL things if they exceed the > limit. In that sense it's more like statement_timeout. I can imagine > us wanting more parameters like this too. Kill the query if it... > > ...takes too long (statement_timeout) > ...uses too much temporary file space (the current patch) > ...uses too much CPU time > ...uses too much RAM > ...generates too much disk I/O > ...has too high an estimated cost > ...others? you're sorting limits for 'executor' and limits for 'planner': uses too much CPU time VS has too high an estimated cost. (backend)_work_(disk|mem) looks good also for the 'has too high an estimated cost' series: limiter at the planner level should allow planner to change its strategy, I think... But probably not something to consider too much right now. > > So I'm not sure work_disk is a great name. Actually, work_mem is > already not a great name even for what it is, but at any rate I think > this is something different. I am not specially attached to a name, idea was not to use work_disk but backend_work_disk. I agree with you anyway, and suggestion from Tom is fine for me (temp_file_limit). > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_listener in 9.0
On Wed, Jun 1, 2011 at 3:02 PM, Steve Singer wrote: > On 11-06-01 09:30 AM, Christopher Browne wrote: >> >> On Wed, Jun 1, 2011 at 8:29 AM, Dave Page wrote: >>> >>> On Wed, Jun 1, 2011 at 12:27 PM, Andrew Dunstan >>> wrote: The whole point of the revamp was that pg_listener was a major performance bottleneck and needed to go, and without it being gone we would not have got notification payloads. >>> >>> Yeah, I know why it was replaced. That doesn't mean we cannot provide >>> an alternative interface to the same info though (other things might >>> of course). >>> I suspect you're pretty much out of luck. >>> >>> Not me - our users. >> >> Note that in Slony 2.1, there's a table called sl_components, which is >> used to capture the state of the various database connections, >> checking in as the various threads do their various actions. >> >> Also, slon and slonik try to report their respective application, so >> it can be reported on pg_stat_activity. > > Slony 2.1 also sets application_name. > > If this were a big deal for pgAdmin we could consider backporting the > application_name change to 2.0.x for users running against 9.0. > > Slony also has a table called sl_nodelock that each slon process writes adds > a row for on startup. This includes the backend pid() for one of the > connections. Slony 1.2, 2.0 and 2.1 all use sl_nodelock Thanks - I've committed changes that use pg_stat_activity and sl_nodelock to try to figure out what's currently going on. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PQdeleteTuple function in libpq
Excerpts from Andrew Chernow's message of jue jun 02 10:12:40 -0400 2011: > > Andrew, why we have PQmakeEmptyPGresult, PQcopyResult, > > PQsetResultAttrs, PQsetvalue and PQresultAlloc in this case? Of course > > there's no big deal with their absence but let's be consistent. > > I'm not entirely sure what you are trying to do, but can't you use > PQmakeEmptyPGresult, PQsetResultAttrs and PQsetvalue to construct a > result that excludes the tuples you don't want followed by a > PQclear(initial_result)? Seems pretty wasteful if you want to delete a single tuple from a large result. I think if you desired to compact the result to free some memory after deleting a large fraction of the tuples in the result it could be useful to do that, otherwise just live with the unused holes in the storage area as suggested by Pavel. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Please test peer (socket ident) auth on *BSD
On Wed, Jun 1, 2011 at 1:03 AM, Tom Lane wrote:
> Marko Kreen writes:
>> My suggestion would be to use getpeereid() everywhere.
>> And just have compat getpeereid() implementation on non-BSD
>> platforms. This would minimize ifdeffery in core core.
>
> Hm, maybe. I'd be for this if we had more than two call sites, but
> as things stand I'm not sure it's worth the trouble to set up a src/port
> module for it.
Here's my attempt for it. As conditional port module seems trouble,
I set up an unconditional pgGetpeereid() that is always defined.
The result seems nice. It also fixes broken ifdeffery where
"#error missing implementation" is unreachable, instead
pqGetpwuid() can be reached with undefined uid.
It does drop 2 error messages for HAVE_UNIX_SOCKET but no method
for getting peer id. Now it will give plain ENOSYS in that case.
If really required, the message can be picked based on errno,
but it does not seem worth it.
--
marko
*** a/src/backend/libpq/auth.c
--- b/src/backend/libpq/auth.c
***
*** 17,28
#include
#include
- #ifdef HAVE_UCRED_H
- #include
- #endif
- #ifdef HAVE_SYS_UCRED_H
- #include
- #endif
#include
#include
#include
--- 17,22
***
*** 1757,1839 auth_peer(hbaPort *port)
{
char ident_user[IDENT_USERNAME_MAX + 1];
uid_t uid = 0;
struct passwd *pass;
! #if defined(HAVE_GETPEEREID)
! /* Most BSDen, including OS X: use getpeereid() */
! gid_t gid;
!
! errno = 0;
! if (getpeereid(port->sock, &uid, &gid) != 0)
{
- /* We didn't get a valid credentials struct. */
ereport(LOG,
(errcode_for_socket_access(),
errmsg("could not get peer credentials: %m")));
return STATUS_ERROR;
}
- #elif defined(SO_PEERCRED)
- /* Linux: use getsockopt(SO_PEERCRED) */
- struct ucred peercred;
- ACCEPT_TYPE_ARG3 so_len = sizeof(peercred);
-
- errno = 0;
- if (getsockopt(port->sock, SOL_SOCKET, SO_PEERCRED, &peercred, &so_len) != 0 ||
- so_len != sizeof(peercred))
- {
- /* We didn't get a valid credentials struct. */
- ereport(LOG,
- (errcode_for_socket_access(),
- errmsg("could not get peer credentials: %m")));
- return STATUS_ERROR;
- }
- uid = peercred.uid;
- #elif defined(LOCAL_PEERCRED)
- /* Debian with FreeBSD kernel: use getsockopt(LOCAL_PEERCRED) */
- struct xucred peercred;
- ACCEPT_TYPE_ARG3 so_len = sizeof(peercred);
-
- errno = 0;
- if (getsockopt(port->sock, 0, LOCAL_PEERCRED, &peercred, &so_len) != 0 ||
- so_len != sizeof(peercred) ||
- peercred.cr_version != XUCRED_VERSION)
- {
- /* We didn't get a valid credentials struct. */
- ereport(LOG,
- (errcode_for_socket_access(),
- errmsg("could not get peer credentials: %m")));
- return STATUS_ERROR;
- }
- uid = peercred.cr_uid;
- #elif defined(HAVE_GETPEERUCRED)
- /* Solaris: use getpeerucred() */
- ucred_t*ucred;
-
- ucred = NULL;/* must be initialized to NULL */
- if (getpeerucred(port->sock, &ucred) == -1)
- {
- ereport(LOG,
- (errcode_for_socket_access(),
- errmsg("could not get peer credentials: %m")));
- return STATUS_ERROR;
- }
-
- if ((uid = ucred_geteuid(ucred)) == -1)
- {
- ereport(LOG,
- (errcode_for_socket_access(),
- errmsg("could not get effective UID from peer credentials: %m")));
- return STATUS_ERROR;
- }
-
- ucred_free(ucred);
- #else
- ereport(LOG,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("Peer authentication is not supported on local connections on this platform")));
-
- return STATUS_ERROR;
- #endif
pass = getpwuid(uid);
--- 1751,1766
{
char ident_user[IDENT_USERNAME_MAX + 1];
uid_t uid = 0;
+ gid_t gid = 0;
struct passwd *pass;
! if (pgGetpeereid(port->sock, &uid, &gid) != 0)
{
ereport(LOG,
(errcode_for_socket_access(),
errmsg("could not get peer credentials: %m")));
return STATUS_ERROR;
}
pass = getpwuid(uid);
*** a/src/include/port.h
--- b/src/include/port.h
***
*** 470,473 extern int pg_check_dir(const char *dir);
--- 470,476
/* port/pgmkdirp.c */
extern int pg_mkdir_p(char *path, int omode);
+ /* port/pggetpeereid.c */
+ extern int pgGetpeereid(int sock, uid_t *uid, gid_t *gid);
+
#endif /* PG_PORT_H */
*** a/src/interfaces/libpq/fe-connect.c
--- b/src/interfaces/libpq/fe-connect.c
***
*** 21,32
#include
#include
#include
- #ifdef HAVE_UCRED_H
- #include
- #endif
- #ifdef HAVE_SYS_UCRED_H
- #include
- #endif
#include "libpq-fe.h"
#include "libpq-int.h"
--- 21,26
***
*** 1866,1928 keep_going: /* We will come back to here until there is
if (conn->requirepeer && conn->requirepeer[0] &&
IS_AF_UNIX(conn->raddr.addr.ss_family))
{
- #if defined(HAVE_GETPEEREID) || defined(SO_PEERCRED) || defined(LOCAL_PEERCRED) || defined(HAVE_GETPEERUCRED)
char pwdbuf[BUFSIZ];
struct passwd pass_buf;
struct passwd *pass;
Re: [HACKERS] pgpool versus sequences
Alvaro Herrera writes: > Excerpts from Tom Lane's message of jue jun 02 10:31:58 -0400 2011: >> That's a lot of work for a purely cosmetic issue, though. What would be >> trivial is to let this work: >> regression=# lock table s1; >> ERROR: "s1" is not a table > Yeah, though it'd be nice to avoid this: > alvherre=# create schema public_too; > CREATE SCHEMA > alvherre=# set search_path to 'public_too', 'public'; > SET > alvherre=# create table public_too.s1 (); > CREATE TABLE > alvherre=# create sequence public.s1; > CREATE SEQUENCE > alvherre=# begin; > BEGIN > alvherre=# lock s1; > LOCK TABLE > At this point we have a lock on the table, but if we change LOCK to also > look for sequences, the behavior would change. No it wouldn't. You seem to be imagining that sequences live in a different namespace from tables, but they don't. There can only be one relation that "s1" will refer to for any search_path setting. 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] pgpool versus sequences
On Thu, Jun 2, 2011 at 10:31 AM, Tom Lane wrote: > Robert Haas writes: >> On Wed, Jun 1, 2011 at 7:47 PM, Alvaro Herrera >> wrote: >>> Yeah -- why is LOCK SEQUENCE foo_seq not allowed? Seems a simple thing >>> to have. > >> It cause a grammar conflict. > > That's a lot of work for a purely cosmetic issue, though. What would be > trivial is to let this work: > > regression=# create sequence s1; > CREATE SEQUENCE > regression=# begin; > BEGIN > regression=# lock table s1; > ERROR: "s1" is not a table > > We should do that anyway, even if we put in the effort to support the > other syntax. Ugh. We are already stuck supporting all kinds of backward compatibility cruft in tablecmds.c as a result of the fact that you used to have to use ALTER TABLE to operate on views and sequences. The whole thing is confusing and a mess. -1 from me on extending that mess to more places. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] vacuum and row type
isn't really specific to ANALYZE. I'm inclined to think that the most reasonable fix is to make get_sort_group_operators() and related Hm, patch is in attach but it doesn't solve all problems. Initial bug is still here for array of row type, but when I tried to change that with recursive call get_sort_group_operators() as it done for row type then 'gmake check' fails because lookup_rowtype_tupdesc fails to find anonymous composite type. As I can see anonymous composite type are identified by (RECORD_OID, typmod) pair and typmod aren't available here. So, my plan was to add typmod to get_sort_group_operators() but I have no idea where is typmod value for element type. In runtime problems are solved by using HeapTupleHeaderGetTypMod() for record / element of array. With modified get_sort_group_operators() for arrays check actually fails for query 'select * from search_graph order by path;' at file src/test/regress/sql/with.sql. get_sort_group_operators() is called from addTargetToSortList() and fails. It seems to me that anonymous composite type could force us to teach vacuum/analyze code to fallback to simpler analyze algorithm. -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ get_sort_group_operators-0.1.gz Description: Unix tar archive -- 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] pgpool versus sequences
Excerpts from Tom Lane's message of jue jun 02 10:31:58 -0400 2011: > Robert Haas writes: > > On Wed, Jun 1, 2011 at 7:47 PM, Alvaro Herrera > > wrote: > >> Yeah -- why is LOCK SEQUENCE foo_seq not allowed? Seems a simple thing > >> to have. > > > It cause a grammar conflict. > > That's a lot of work for a purely cosmetic issue, though. What would be > trivial is to let this work: > > regression=# create sequence s1; > CREATE SEQUENCE > regression=# begin; > BEGIN > regression=# lock table s1; > ERROR: "s1" is not a table Yeah, though it'd be nice to avoid this: alvherre=# create schema public_too; CREATE SCHEMA alvherre=# set search_path to 'public_too', 'public'; SET alvherre=# create table public_too.s1 (); CREATE TABLE alvherre=# create sequence public.s1; CREATE SEQUENCE alvherre=# begin; BEGIN alvherre=# lock s1; LOCK TABLE At this point we have a lock on the table, but if we change LOCK to also look for sequences, the behavior would change. At the very least, the command tag should be different. Hopefully few people name sequences the same as tables ... -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PQdeleteTuple function in libpq
Hello, Alvaro. You wrote: AH> Excerpts from Andrew Chernow's message of jue jun 02 10:12:40 -0400 2011: >> > Andrew, why we have PQmakeEmptyPGresult, PQcopyResult, >> > PQsetResultAttrs, PQsetvalue and PQresultAlloc in this case? Of course >> > there's no big deal with their absence but let's be consistent. >> >> I'm not entirely sure what you are trying to do, but can't you use >> PQmakeEmptyPGresult, PQsetResultAttrs and PQsetvalue to construct a >> result that excludes the tuples you don't want followed by a >> PQclear(initial_result)? AH> Seems pretty wasteful if you want to delete a single tuple from a large AH> result. I think if you desired to compact the result to free some AH> memory after deleting a large fraction of the tuples in the result it AH> could be useful to do that, otherwise just live with the unused holes in AH> the storage area as suggested by Pavel. Totally! Thanks Alvaro. -- With best wishes, Pavel mailto:pa...@gf.microolap.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] Please test peer (socket ident) auth on *BSD
On Thu, Jun 2, 2011 at 5:49 PM, Tom Lane wrote:
> Marko Kreen writes:
>> Here's my attempt for it. As conditional port module seems trouble,
>> I set up an unconditional pgGetpeereid() that is always defined.
>
> -1 ... why would you think that a conditional substitution is trouble?
> We have plenty of others.
Because it required touching autoconf. ;)
So now I did it. I hope it was that simple.
As there was no going back now, I even touched msvc.pm.
--
marko
*** a/configure.in
--- b/configure.in
***
*** 1191,1197 PGAC_VAR_INT_TIMEZONE
AC_FUNC_ACCEPT_ARGTYPES
PGAC_FUNC_GETTIMEOFDAY_1ARG
! AC_CHECK_FUNCS([cbrt dlopen fcvt fdatasync getifaddrs getpeereid getpeerucred getrlimit memmove poll pstat readlink scandir setproctitle setsid sigprocmask symlink sysconf towlower utime utimes waitpid wcstombs wcstombs_l])
AC_REPLACE_FUNCS(fseeko)
case $host_os in
--- 1191,1199
AC_FUNC_ACCEPT_ARGTYPES
PGAC_FUNC_GETTIMEOFDAY_1ARG
! AC_CHECK_FUNCS([cbrt dlopen fcvt fdatasync getifaddrs getpeerucred getrlimit memmove poll pstat readlink scandir setproctitle setsid sigprocmask symlink sysconf towlower utime utimes waitpid wcstombs wcstombs_l])
!
! AC_REPLACE_FUNCS(getpeereid)
AC_REPLACE_FUNCS(fseeko)
case $host_os in
*** a/src/backend/libpq/auth.c
--- b/src/backend/libpq/auth.c
***
*** 17,28
#include
#include
- #ifdef HAVE_UCRED_H
- #include
- #endif
- #ifdef HAVE_SYS_UCRED_H
- #include
- #endif
#include
#include
#include
--- 17,22
***
*** 1757,1839 auth_peer(hbaPort *port)
{
char ident_user[IDENT_USERNAME_MAX + 1];
uid_t uid = 0;
struct passwd *pass;
- #if defined(HAVE_GETPEEREID)
- /* Most BSDen, including OS X: use getpeereid() */
- gid_t gid;
-
- errno = 0;
if (getpeereid(port->sock, &uid, &gid) != 0)
{
- /* We didn't get a valid credentials struct. */
ereport(LOG,
(errcode_for_socket_access(),
errmsg("could not get peer credentials: %m")));
return STATUS_ERROR;
}
- #elif defined(SO_PEERCRED)
- /* Linux: use getsockopt(SO_PEERCRED) */
- struct ucred peercred;
- ACCEPT_TYPE_ARG3 so_len = sizeof(peercred);
-
- errno = 0;
- if (getsockopt(port->sock, SOL_SOCKET, SO_PEERCRED, &peercred, &so_len) != 0 ||
- so_len != sizeof(peercred))
- {
- /* We didn't get a valid credentials struct. */
- ereport(LOG,
- (errcode_for_socket_access(),
- errmsg("could not get peer credentials: %m")));
- return STATUS_ERROR;
- }
- uid = peercred.uid;
- #elif defined(LOCAL_PEERCRED)
- /* Debian with FreeBSD kernel: use getsockopt(LOCAL_PEERCRED) */
- struct xucred peercred;
- ACCEPT_TYPE_ARG3 so_len = sizeof(peercred);
-
- errno = 0;
- if (getsockopt(port->sock, 0, LOCAL_PEERCRED, &peercred, &so_len) != 0 ||
- so_len != sizeof(peercred) ||
- peercred.cr_version != XUCRED_VERSION)
- {
- /* We didn't get a valid credentials struct. */
- ereport(LOG,
- (errcode_for_socket_access(),
- errmsg("could not get peer credentials: %m")));
- return STATUS_ERROR;
- }
- uid = peercred.cr_uid;
- #elif defined(HAVE_GETPEERUCRED)
- /* Solaris: use getpeerucred() */
- ucred_t*ucred;
-
- ucred = NULL;/* must be initialized to NULL */
- if (getpeerucred(port->sock, &ucred) == -1)
- {
- ereport(LOG,
- (errcode_for_socket_access(),
- errmsg("could not get peer credentials: %m")));
- return STATUS_ERROR;
- }
-
- if ((uid = ucred_geteuid(ucred)) == -1)
- {
- ereport(LOG,
- (errcode_for_socket_access(),
- errmsg("could not get effective UID from peer credentials: %m")));
- return STATUS_ERROR;
- }
-
- ucred_free(ucred);
- #else
- ereport(LOG,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("Peer authentication is not supported on local connections on this platform")));
-
- return STATUS_ERROR;
- #endif
pass = getpwuid(uid);
--- 1751,1766
{
char ident_user[IDENT_USERNAME_MAX + 1];
uid_t uid = 0;
+ gid_t gid = 0;
struct passwd *pass;
if (getpeereid(port->sock, &uid, &gid) != 0)
{
ereport(LOG,
(errcode_for_socket_access(),
errmsg("could not get peer credentials: %m")));
return STATUS_ERROR;
}
pass = getpwuid(uid);
*** a/src/include/port.h
--- b/src/include/port.h
***
*** 470,473 extern int pg_check_dir(const char *dir);
--- 470,478
/* port/pgmkdirp.c */
extern int pg_mkdir_p(char *path, int omode);
+ /* port/getpeereid.c */
+ #ifndef HAVE_GETPEEREID
+ extern int getpeereid(int sock, uid_t *uid, gid_t *gid);
+ #endif
+
#endif /* PG_PORT_H */
*** a/src/interfaces/libpq/fe-connect.c
--- b/src/interfaces/libpq/fe-connect.c
***
*** 21,32
#include
#include
#include
- #ifdef HAVE_UCRED_H
- #include
- #endif
- #ifdef HAVE_SYS_UCRED_H
- #include
- #endif
#include "libpq-fe.h"
#include "libpq-int.h"
--- 21,26
***
*** 1866,1928 keep_going: /
Re: [HACKERS] PQdeleteTuple function in libpq
Excerpts from Merlin Moncure's message of jue jun 02 11:33:28 -0400 2011: > On Thu, Jun 2, 2011 at 10:02 AM, Alvaro Herrera > wrote: > > Seems pretty wasteful if you want to delete a single tuple from a large > > result. I think if you desired to compact the result to free some > > memory after deleting a large fraction of the tuples in the result it > > could be useful to do that, otherwise just live with the unused holes in > > the storage area as suggested by Pavel. > > That would work, but it would potentially invalidate external pointers > to internal result data. If you wanted to do this, it might be better > to expose a compaction feature which can be invoked directly. Wouldn't that also invalidate external pointers? -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Please test peer (socket ident) auth on *BSD
On 06/02/2011 11:29 AM, Marko Kreen wrote: As there was no going back now, I even touched msvc.pm. Why? Windows doesn't have Unix domain sockets at all. 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] pgpool versus sequences
Excerpts from Tom Lane's message of jue jun 02 11:10:00 -0400 2011:
> Alvaro Herrera writes:
> > Excerpts from Tom Lane's message of jue jun 02 10:31:58 -0400 2011:
> >> That's a lot of work for a purely cosmetic issue, though. What would be
> >> trivial is to let this work:
> >> regression=# lock table s1;
> >> ERROR: "s1" is not a table
>
> > Yeah, though it'd be nice to avoid this:
>
> > alvherre=# create schema public_too;
> > CREATE SCHEMA
> > alvherre=# set search_path to 'public_too', 'public';
> > SET
> > alvherre=# create table public_too.s1 ();
> > CREATE TABLE
> > alvherre=# create sequence public.s1;
> > CREATE SEQUENCE
> > alvherre=# begin;
> > BEGIN
> > alvherre=# lock s1;
> > LOCK TABLE
>
> > At this point we have a lock on the table, but if we change LOCK to also
> > look for sequences, the behavior would change.
>
> No it wouldn't. You seem to be imagining that sequences live in a
> different namespace from tables, but they don't. There can only be one
> relation that "s1" will refer to for any search_path setting.
Doh, I see that I messed up and reversed the schemas in the search_path
line above. If I fix that I get the expected error:
alvherre=# set search_path to 'public', 'public_too';
SET
alvherre=# lock s1;
ERROR: «s1» no es una tabla
("s1" is not a table). What I was imagining was that LOCK was using
search path to look only for tables and ignoring sequences.
--
Álvaro Herrera
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PQdeleteTuple function in libpq
On Thu, Jun 2, 2011 at 10:02 AM, Alvaro Herrera wrote: > Excerpts from Andrew Chernow's message of jue jun 02 10:12:40 -0400 2011: > >> > Andrew, why we have PQmakeEmptyPGresult, PQcopyResult, >> > PQsetResultAttrs, PQsetvalue and PQresultAlloc in this case? Of course >> > there's no big deal with their absence but let's be consistent. >> >> I'm not entirely sure what you are trying to do, but can't you use >> PQmakeEmptyPGresult, PQsetResultAttrs and PQsetvalue to construct a >> result that excludes the tuples you don't want followed by a >> PQclear(initial_result)? > > Seems pretty wasteful if you want to delete a single tuple from a large > result. I think if you desired to compact the result to free some > memory after deleting a large fraction of the tuples in the result it > could be useful to do that, otherwise just live with the unused holes in > the storage area as suggested by Pavel. That would work, but it would potentially invalidate external pointers to internal result data. If you wanted to do this, it might be better to expose a compaction feature which can be invoked directly. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] vacuum and row type
Teodor Sigaev writes: >> isn't really specific to ANALYZE. I'm inclined to think that the most >> reasonable fix is to make get_sort_group_operators() and related > Hm, patch is in attach but it doesn't solve all problems. Initial bug is > still > here for array of row type, but when I tried to change that with recursive > call > get_sort_group_operators() as it done for row type then 'gmake check' fails > because lookup_rowtype_tupdesc fails to find anonymous composite type. I think we could just let this code assume success for type RECORD. It won't affect VACUUM/ANALYZE, since there are (for reasons that should now be obvious) no table or index columns of anonymous composite types. What I was thinking last night is that it'd be smart to move all this logic into the typcache, instead of repeating all the work each time we make the check. I'm not convinced that get_sort_group_operators is the only place we'd have to change if we keep the logic outside the typcache, anyway. (I seem to recall there is someplace in the planner that has a similar check.) 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] Hacking gram.y Error syntax error at or near "MERGEJOIN"
Hi, thanks a lot for your ideas. But I've done all these things. I've checked the gram.y and kwlist.h files many times but can not find what's wrong. So is there any possibility that the problem comes from something after parser, though it seems it should comes from parser? On 2 June 2011 21:14, Heikki Linnakangas < heikki.linnakan...@enterprisedb.com> wrote: > On 02.06.2011 15:16, Andrew Dunstan wrote: > >> On 06/02/2011 03:28 AM, HuangQi wrote: >> >>> Hi, >>> I'm doing a hacking project which hacks the gram.y file and some other >>> so that postgres can execute some query plan language to run a >>> particular plan. I did some modifications and try run it. It compiled >>> and installed successfully. But when I was trying to run a MergeJoin >>> operation with keyword MERGEJOIN, it says "ERROR: syntax error at or >>> near 'MERGEJOIN'". It seems postgres didn't recognize this keyword. Is >>> it caused by any error inside gram.y? Or how can I trace this error? >>> >> >> Without seeing your grammar changes and the statement you're trying to >> execute it's pretty much impossible to tell. >> > > Yeah. One more guess is that you didn't include the keyword in the keyword > list at the end of gram.y. There's script to check various keyword-related > things at src/tools/check_keywords.pl > > -- > Heikki Linnakangas > EnterpriseDB http://www.enterprisedb.com > -- Best Regards Huang Qi Victor
Re: [HACKERS] BLOB support
2011/6/2 Tom Lane : > Robert Haas writes: >> But these problems can be fixed without inventing a completely new >> system, I think. Or at least we should try. I can see the point of a >> data type that is really a pointer to a LOB, and the LOB gets deleted >> when the pointer is removed, but I don't think that should require >> far-reaching changes all over the system (like relhaslobs) to make it >> work efficiently. I think you need to start with a problem statement, >> get agreement that it is a problem and on what the solution should be, >> and then go write the code to implement that solution. > > Yes. I think the appropriate problem statement is "provide streaming > access to large field values, as an alternative to just fetching/storing > the entire value at once". I see no good reason to import the entire > messy notion of LOBS/CLOBS. (The fact that other databases have done it > is not a good reason.) > > For primitive types like text or bytea it seems pretty obvious what > "streaming access" should entail, but it might be interesting to > consider what it should mean for structured types. For instance, if I > have an array field with umpteen zillion elements, it might be nice to > fetch them one at a time using the streaming access mechanism. I don't > say that that has to be in the first version, but it'd be a good idea to > keep that in the back of your head so you don't design a dead-end > solution that can't be extended in that direction. +1 Pavel > > regards, tom lane > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Please test peer (socket ident) auth on *BSD
On 06/02/2011 12:04 PM, Marko Kreen wrote: On Thu, Jun 2, 2011 at 6:59 PM, Andrew Dunstan wrote: On 06/02/2011 11:29 AM, Marko Kreen wrote: As there was no going back now, I even touched msvc.pm. Why? Windows doesn't have Unix domain sockets at all. Because the function is still referenced in the code. Then maybe we need to use "#ifndef WIN32" in those places. That's what we do for similar cases. 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] Please test peer (socket ident) auth on *BSD
Andrew Dunstan writes: > On 06/02/2011 12:04 PM, Marko Kreen wrote: >> On Thu, Jun 2, 2011 at 6:59 PM, Andrew Dunstan wrote: >>> On 06/02/2011 11:29 AM, Marko Kreen wrote: As there was no going back now, I even touched msvc.pm. >>> Why? Windows doesn't have Unix domain sockets at all. >> Because the function is still referenced in the code. > Then maybe we need to use "#ifndef WIN32" in those places. That's what > we do for similar cases. Seems reasonable, since the whole code chunk is within IS_AF_UNIX anyway. Will adjust and apply. 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] PQdeleteTuple function in libpq
On 6/2/2011 11:02 AM, Alvaro Herrera wrote: Excerpts from Andrew Chernow's message of jue jun 02 10:12:40 -0400 2011: Andrew, why we have PQmakeEmptyPGresult, PQcopyResult, PQsetResultAttrs, PQsetvalue and PQresultAlloc in this case? Of course there's no big deal with their absence but let's be consistent. I'm not entirely sure what you are trying to do, but can't you use PQmakeEmptyPGresult, PQsetResultAttrs and PQsetvalue to construct a result that excludes the tuples you don't want followed by a PQclear(initial_result)? Seems pretty wasteful if you want to delete a single tuple from a large result. I think if you desired to compact the result to free some memory after deleting a large fraction of the tuples in the result it could be useful to do that, otherwise just live with the unused holes in the storage area as suggested by Pavel. Another solution is to manually cursor through the set (like grab 1000 tuples at a time) and copy the set to your own structure. That way, the temporary double memory to perform the copy is not as big of a hit. By using your own structure, you can organize the memory in a fashion that is optimized for your requirement. -- Andrew Chernow eSilo, LLC global backup http://www.esilo.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] vacuum and row type
I think we could just let this code assume success for type RECORD. It won't affect VACUUM/ANALYZE, since there are (for reasons that should now be obvious) no table or index columns of anonymous composite types. Of course, it's impossible to store anonymous composite type anywhere, but we still have possibility to use it in ORDER BY at least, following query works on HEAD but fails with patch: select ROW(1, n) as r from generate_series(1,5) as n order by r; What I was thinking last night is that it'd be smart to move all this logic into the typcache, instead of repeating all the work each time we Agree -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ -- 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] pgpool versus sequences
On Thu, Jun 2, 2011 at 10:47 AM, Tom Lane wrote: > Robert Haas writes: >> Ugh. We are already stuck supporting all kinds of backward >> compatibility cruft in tablecmds.c as a result of the fact that you >> used to have to use ALTER TABLE to operate on views and sequences. >> The whole thing is confusing and a mess. > > [ shrug... ] I don't find it so. We have a convention that TABLE is > an umbrella term for all applicable relation types. End of story. > > Even if you disagree with that, the convention does exist, and making > LOCK the one command type that disobeys it doesn't seem like a good > plan. I agree that wouldn't be a good plan to make LOCK inconsistent with everything else, but LOCK is not the only case that's like this: rhaas=# drop table v1; ERROR: "v1" is not a table HINT: Use DROP VIEW to remove a view. rhaas=# comment on table v1 is 'v1 is a view'; ERROR: "v1" is not a table rhaas=# load 'dummy_seclabel'; LOAD rhaas=# security label on table v1 is 'classified'; ERROR: "v1" is not a table As far as I can see, ALTER TABLE is just about the only place where we allow this; and only for certain command types. Your commit message seems to indicate that we continue to allow that stuff only for backward-compatibility: commit a0b012a1ab85ae115f30e5e4fe09922b4885fdad Author: Tom Lane Date: Sun Jun 15 01:25:54 2008 + Rearrange ALTER TABLE syntax processing as per my recent proposal: the grammar allows ALTER TABLE/INDEX/SEQUENCE/VIEW interchangeably for all subforms of those commands, and then we sort out what's really legal at execution time. This allows the ALTER SEQUENCE/VIEW reference pages to fully document all the ALTER forms available for sequences and views respectively, and eliminates a longstanding cause of confusion for users. The net effect is that the following forms are allowed that weren't before: ALTER SEQUENCE OWNER TO ALTER VIEW ALTER COLUMN SET/DROP DEFAULT ALTER VIEW OWNER TO ALTER VIEW SET SCHEMA (There's no actual functionality gain here, but formerly you had to say ALTER TABLE instead.) Interestingly, the grammar tables actually get smaller, probably because there are fewer special cases to keep track of. I did not disallow using ALTER TABLE for these operations. Perhaps we should, but there's a backwards-compatibility issue if we do; in fact it would break existing pg_dump scripts. I did however tighten up ALTER SEQUENCE and ALTER VIEW to reject non-sequences and non-views in the new cases as well as a couple of cases where they didn't before. The patch doesn't change pg_dump to use the new syntaxes, either. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PQdeleteTuple function in libpq
On Thu, Jun 2, 2011 at 10:57 AM, Alvaro Herrera wrote: > Excerpts from Merlin Moncure's message of jue jun 02 11:33:28 -0400 2011: >> On Thu, Jun 2, 2011 at 10:02 AM, Alvaro Herrera >> wrote: > >> > Seems pretty wasteful if you want to delete a single tuple from a large >> > result. I think if you desired to compact the result to free some >> > memory after deleting a large fraction of the tuples in the result it >> > could be useful to do that, otherwise just live with the unused holes in >> > the storage area as suggested by Pavel. >> >> That would work, but it would potentially invalidate external pointers >> to internal result data. If you wanted to do this, it might be better >> to expose a compaction feature which can be invoked directly. > > Wouldn't that also invalidate external pointers? sure -- but at least they are being deliberately invalidated instead of automatically through some other function (i'm thinking about the stl vector pointer invalidation issues here). also since compaction could be fairly slow, it would be hard to exactly judge when it should be done. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Please test peer (socket ident) auth on *BSD
On Thu, Jun 2, 2011 at 7:20 PM, Andrew Dunstan wrote: > On 06/02/2011 12:04 PM, Marko Kreen wrote: >> On Thu, Jun 2, 2011 at 6:59 PM, Andrew Dunstan >> wrote: >>> On 06/02/2011 11:29 AM, Marko Kreen wrote: As there was no going back now, I even touched msvc.pm. >>> >>> Why? Windows doesn't have Unix domain sockets at all. >> >> Because the function is still referenced in the code. >> > > Then maybe we need to use "#ifndef WIN32" in those places. That's what we do > for similar cases. No, that would be a bad idea - uglifies code for no good reason. The function is referenced undef IS_AF_UNIX() check, so it would not be run anyway. Even if it would run somehow, there is only 2 lines to return ENOSYS. With #ifdef you would need some additional error message under #ifdef WIN32, just in case, so what exactly would be improved by that? -- marko -- 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] BLOB support
Tom Lane Thursday 02 of June 2011 16:42:42 > Robert Haas writes: > > But these problems can be fixed without inventing a completely new > > system, I think. Or at least we should try. I can see the point of a > > data type that is really a pointer to a LOB, and the LOB gets deleted > > when the pointer is removed, but I don't think that should require > > far-reaching changes all over the system (like relhaslobs) to make it > > work efficiently. I think you need to start with a problem statement, > > get agreement that it is a problem and on what the solution should be, > > and then go write the code to implement that solution. > > Yes. I think the appropriate problem statement is "provide streaming > access to large field values, as an alternative to just fetching/storing > the entire value at once". I see no good reason to import the entire > messy notion of LOBS/CLOBS. (The fact that other databases have done it > is not a good reason.) > > For primitive types like text or bytea it seems pretty obvious what > "streaming access" should entail, but it might be interesting to > consider what it should mean for structured types. For instance, if I > have an array field with umpteen zillion elements, it might be nice to > fetch them one at a time using the streaming access mechanism. I don't > say that that has to be in the first version, but it'd be a good idea to > keep that in the back of your head so you don't design a dead-end > solution that can't be extended in that direction. > > regards, tom lane In context of LOBs streaming is resolved... I use current LO functionallity (so driver may be able to read LOBs as psql \lo_export does it or using COPY subprotocol) and client should get just LO's id. BLOBs in this implementation, like Robert wanted are just wrapper for core LO, with some extensions for special situations Adding of relhaslob in this impl is quite importnat to do not examine tupledesc for each table operation, but this value may be deduced during relation open (with performance penatly). I saw simillar is made few lines above when triggers are fired, and few lines below when indices are updated. Currently BLOBs may be emulated using core LO (JDBC driver does it), but among everything else, other problems are, if you look from point of view of application developing: 1. No tracking of unused LO (you store just id of such object). You may leak LO after row remove/update. User may write triggers for this, but it is not argument - BLOB type is popular, and it's simplicity of use is quite important. When I create app this is worst thing. 2. No support for casting in UPDATE/INSERT. So there is no way to simple migrate data (e.g. from too long varchars). Or to copy BLOBs. 3. Limitation of field size to 1GB. Other solution, I was think about, is to introduce system triggers (such triggers can't be disabled or removed). So there will be new flag in triggers table. Now I think, we should try to mix both aproches, as system triggers may give interesting API for other developers. Other databases (may) store LOBs, Arrays, and Composites in external tables, so user get's just id of such object. I think about two weaks about streaming, I have some concepts about this, but from point of view of memory consumption and performance. I will send concept later, I want to think a little bit about it once more, and search what can be actually done. Regards, Radek -- 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] Please test peer (socket ident) auth on *BSD
On Thu, Jun 2, 2011 at 6:59 PM, Andrew Dunstan wrote: > On 06/02/2011 11:29 AM, Marko Kreen wrote: >> As there was no going back now, I even touched msvc.pm. > > Why? Windows doesn't have Unix domain sockets at all. Because the function is still referenced in the code. -- marko -- 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] Please test peer (socket ident) auth on *BSD
Marko Kreen writes: >> -1 ... why would you think that a conditional substitution is trouble? >> We have plenty of others. > Because it required touching autoconf. ;) > So now I did it. I hope it was that simple. Applied with minor adjustments --- notably, I didn't agree with removing the special-case error messages for platforms that lack support for this. 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] Re: patch review : Add ability to constrain backend temporary file space
On Thu, Jun 2, 2011 at 10:58 AM, Cédric Villemain wrote: > I am not specially attached to a name, idea was not to use work_disk > but backend_work_disk. I agree with you anyway, and suggestion from > Tom is fine for me (temp_file_limit). Yeah, I like that too. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] InitProcGlobal cleanup
While working on my patch to reduce the overhead of frequent table locks, I had cause to monkey with InitProcGlobal() and noticed that it's sort of a mess. For reasons that are not clear to me, it allocates one of the three PGPROC arrays using ShemInitStruct() and the other two using ShmemAlloc(). I'm not clear on why we should use different functions for different allocations, and it also seems like it would make sense to do the whole allocation at once instead of doing three separate ones. Also, the setup of AuxiliaryProcs is strangely split into two parts, one at the top of the function (where we allocate the memory) and the other at the bottom (where we initialize it), but there's no clear reason to break it up like that. Any reason not to instead do something like the attached? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company init-proc-global-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] vacuum and row type
Teodor Sigaev writes: >> I think we could just let this code assume success for type RECORD. It >> won't affect VACUUM/ANALYZE, since there are (for reasons that should >> now be obvious) no table or index columns of anonymous composite types. > Of course, it's impossible to store anonymous composite type anywhere, but > we still have possibility to use it in ORDER BY at least, following query > works > on HEAD but fails with patch: > select ROW(1, n) as r from generate_series(1,5) as n order by r; Right, so for type RECORD we should let the parser assume that comparisons will work. If the anonymous composite type isn't actually sortable, it'll fail at runtime, same as now. 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] Please test peer (socket ident) auth on *BSD
On 06/02/2011 01:04 PM, Alvaro Herrera wrote: Excerpts from Marko Kreen's message of jue jun 02 12:45:04 -0400 2011: On Thu, Jun 2, 2011 at 7:31 PM, Alvaro Herrera wrote: Excerpts from Andrew Dunstan's message of jue jun 02 11:59:02 -0400 2011: On 06/02/2011 11:29 AM, Marko Kreen wrote: As there was no going back now, I even touched msvc.pm. Why? Windows doesn't have Unix domain sockets at all. So much for being thorough :-P Well, there is 2 approaches to portable C code: 1) You #ifdef the main code portable 2) You #ifdef common platform in headers, then main code is written against common platform, without ifdefs. I'm from the camp #2. I don't disagree, just saying that you seem to have gone out of your way to produce something that doesn't seem to be necessary. Yeah, I'm from the camp that says "don't compile code that's guaranteed to be dead." 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] Please test peer (socket ident) auth on *BSD
Excerpts from Andrew Dunstan's message of jue jun 02 11:59:02 -0400 2011: > > On 06/02/2011 11:29 AM, Marko Kreen wrote: > > > > As there was no going back now, I even touched msvc.pm. > > Why? Windows doesn't have Unix domain sockets at all. So much for being thorough :-P -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating CHECK constraints as NOT VALID
Excerpts from Alvaro Herrera's message of mié jun 01 20:56:12 -0400 2011: > Excerpts from Thom Brown's message of mié jun 01 19:48:44 -0400 2011: > > > Is this expected? > > [ pg_dump fails to preserve not-valid status of constraints ] > > Certainly not. > > > Shouldn't the constraint be dumped as not valid too?? > > Sure, I'll implement that tomorrow. Actually, it turns out that NOT VALID foreign keys were already buggy here, and fixing them automatically fixes this case as well, because the fix involves touching pg_get_constraintdef to dump the flag. This also gets it into psql's \d. Patch attached. (Maybe the changes in psql's describe.c should be reverted, not sure.) -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support 0001-Fix-pg_get_constraintdef-to-cope-with-NOT-VALID-cons.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] Hacking gram.y Error syntax error at or near "MERGEJOIN"
Excerpts from HuangQi's message of jue jun 02 11:17:21 -0400 2011: > Hi, thanks a lot for your ideas. But I've done all these things. I've > checked the gram.y and kwlist.h files many times but can not find what's > wrong. So is there any possibility that the problem comes from something > after parser, though it seems it should comes from parser? If you want more input, post the patch. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Please test peer (socket ident) auth on *BSD
Excerpts from Marko Kreen's message of jue jun 02 12:45:04 -0400 2011: > On Thu, Jun 2, 2011 at 7:31 PM, Alvaro Herrera > wrote: > > Excerpts from Andrew Dunstan's message of jue jun 02 11:59:02 -0400 2011: > >> On 06/02/2011 11:29 AM, Marko Kreen wrote: > >> > As there was no going back now, I even touched msvc.pm. > >> > >> Why? Windows doesn't have Unix domain sockets at all. > > > > So much for being thorough :-P > > Well, there is 2 approaches to portable C code: > 1) You #ifdef the main code portable > 2) You #ifdef common platform in headers, then main code > is written against common platform, without ifdefs. > > I'm from the camp #2. I don't disagree, just saying that you seem to have gone out of your way to produce something that doesn't seem to be necessary. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Please test peer (socket ident) auth on *BSD
On Thu, Jun 2, 2011 at 7:44 PM, Tom Lane wrote: > Marko Kreen writes: >> On Thu, Jun 2, 2011 at 7:20 PM, Andrew Dunstan wrote: >>> Then maybe we need to use "#ifndef WIN32" in those places. That's what we do >>> for similar cases. > >> No, that would be a bad idea - uglifies code for no good reason. > >> The function is referenced undef IS_AF_UNIX() check, so it would >> not be run anyway. Even if it would run somehow, there is only >> 2 lines to return ENOSYS. > > Yeah, but not compiling thirty lines in fe-connect.c is worthwhile. > > The auth_peer code in the backend is #ifdef HAVE_UNIX_SOCKETS, and > I see no reason why this chunk in libpq shouldn't be as well. ip.h: #ifdef HAVE_UNIX_SOCKETS #define IS_AF_UNIX(fam) ((fam) == AF_UNIX) #else #define IS_AF_UNIX(fam) (0) #endif This the #ifdefs-in-headers-only approach to the problem... -- marko -- 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] InitProcGlobal cleanup
Robert Haas writes: > While working on my patch to reduce the overhead of frequent table > locks, I had cause to monkey with InitProcGlobal() and noticed that > it's sort of a mess. For reasons that are not clear to me, it > allocates one of the three PGPROC arrays using ShemInitStruct() and > the other two using ShmemAlloc(). I'm not clear on why we should use > different functions for different allocations, and it also seems like > it would make sense to do the whole allocation at once instead of > doing three separate ones. Also, the setup of AuxiliaryProcs is > strangely split into two parts, one at the top of the function (where > we allocate the memory) and the other at the bottom (where we > initialize it), but there's no clear reason to break it up like that. > Any reason not to instead do something like the attached? I find this a whole lot less readable, because you've largely obscured the fact that there are three or four different groups of PGPROC structures being built here and then linked into several different lists/arrays. The code might be okay but it desperately needs more comments. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Please test peer (socket ident) auth on *BSD
On Thu, Jun 2, 2011 at 7:31 PM, Alvaro Herrera wrote: > Excerpts from Andrew Dunstan's message of jue jun 02 11:59:02 -0400 2011: >> On 06/02/2011 11:29 AM, Marko Kreen wrote: >> > As there was no going back now, I even touched msvc.pm. >> >> Why? Windows doesn't have Unix domain sockets at all. > > So much for being thorough :-P Well, there is 2 approaches to portable C code: 1) You #ifdef the main code portable 2) You #ifdef common platform in headers, then main code is written against common platform, without ifdefs. I'm from the camp #2. -- marko -- 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] Please test peer (socket ident) auth on *BSD
Marko Kreen writes: > On Thu, Jun 2, 2011 at 7:20 PM, Andrew Dunstan wrote: >> Then maybe we need to use "#ifndef WIN32" in those places. That's what we do >> for similar cases. > No, that would be a bad idea - uglifies code for no good reason. > The function is referenced undef IS_AF_UNIX() check, so it would > not be run anyway. Even if it would run somehow, there is only > 2 lines to return ENOSYS. Yeah, but not compiling thirty lines in fe-connect.c is worthwhile. The auth_peer code in the backend is #ifdef HAVE_UNIX_SOCKETS, and I see no reason why this chunk in libpq shouldn't be as well. 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] \d missing tab completion for composite types
Tab completion for \d currently does not complete composite types, even though \d works for composite types. That's easy to be fixed, but I have two more general questions: Since \d is happy to describe any kind of pg_class entry, should we also remove the relkind restriction in what tab-complete.c currently calls Query_for_list_of_tisvf? (TOAST tables would also be affected, mainly.) It looks like this functionality of using \d to show a composite type's details is not documented at all. Should it be? describeOneTableDetails() accounts for it, so probably yes. -- 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] storing TZ along timestamps
On 06/01/2011 05:18 PM, Alvaro Herrera wrote: Excerpts from Jeff Davis's message of mié jun 01 19:57:40 -0400 2011: On Fri, 2011-05-27 at 16:43 -0400, Alvaro Herrera wrote: Hi, One of our customers is interested in being able to store original timezone along with a certain timestamp. I assume that you're talking about a new data type, not augmenting the current types, correct? Yes That eliminates many of my issues - I just didn't want the type changed underneath me. But some considerations remain - including some new that have crossed my mind: 1. How would the time-zone be defined in this composite? Offset from GMT? Timezone (well, link thereto) with all DST rules intact? Would "extract" need to be modified to include the ability to grab the timezone? 2. What would be the precedence for defining originating timezone? Default? Set timezone to? ...at time zone...? Based on the timestamp (2011-06-02 12:34:56-07)? 3. Would indexing/sorting include the originating zone? If so, how would time zones collate (base offset, actual offset based on the timestamp, name)? 4. What would be the corresponding type when used with Perl/PHP/Python/... applications - would they require special non-standard handling? Since this isn't going to alter my current beloved timestamptz and I don't have a use-case I leave the decisions on the above to others. But in my imagined use-cases I still see the originating zone as a separate piece of information better handled as a different column - for example sorting by timestamp plus priority or selecting everything for a specific time zone. Cheers, Steve -- 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] storing TZ along timestamps
Excerpts from Merlin Moncure's message of mié jun 01 21:36:32 -0400 2011: > On Wed, Jun 1, 2011 at 8:18 PM, Alvaro Herrera > wrote: > > Excerpts from Jeff Davis's message of mié jun 01 19:57:40 -0400 2011: > >> On Fri, 2011-05-27 at 16:43 -0400, Alvaro Herrera wrote: > >> > Hi, > >> > > >> > One of our customers is interested in being able to store original > >> > timezone along with a certain timestamp. > >> > >> I assume that you're talking about a new data type, not augmenting the > >> current types, correct? > > > > Yes > > why not use a composite type for that? performance maybe? To avoid having to implement all the operators and lookup tables (of timezones) in userland, mainly. Probably performance would be affected too, not sure, but that's not the main point. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] BLOB support
=?utf-8?q?Rados=C5=82aw_Smogura?= writes: > Tom Lane Thursday 02 of June 2011 16:42:42 >> Yes. I think the appropriate problem statement is "provide streaming >> access to large field values, as an alternative to just fetching/storing >> the entire value at once". I see no good reason to import the entire >> messy notion of LOBS/CLOBS. (The fact that other databases have done it >> is not a good reason.) > In context of LOBs streaming is resolved... I use current LO functionallity > (so driver may be able to read LOBs as psql \lo_export does it or using COPY > subprotocol) and client should get just LO's id. Just to be clear: I do not want to expose a concept of object IDs for field values in the first place. All of the problems you enumerate stem from the idea that LOBs ought to be a distinct kind of field, and I don't buy 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
Re: [HACKERS] SSI predicate locking on heap -- tuple or row?
Dan Ports wrote: > On Wed, Jun 01, 2011 at 05:09:09PM -0500, Kevin Grittner wrote: >> Published papers have further proven that the transaction which >> appears to have executed last of these three must actually commit >> before either of the others for an anomaly to occur. > > We can actually say something slightly stronger than that last > sentence: Tout has to commit before *any* other transaction in the > cycle. That doesn't help us implement SSI, because we never try to > look at an entire cycle, but it's still true and useful for proofs > like this. I didn't know that, although it doesn't seem too surprising. With that as a given, the proof can be quite short and straightforward. > Now, supposing Tin is read-only... > > Since there's a cycle, there must also be a transaction that > precedes Tin in the serial order. Call it T0. (T0 might be the > same transaction as Tout, but that doesn't matter.) There's an > edge in the graph from T0 to Tin. It can't be a rw-conflict, > because Tin was read-only, so it must be a ww- or wr-dependency. > Either means T0 committed before Tin started. > > Because Tout committed before any other transaction in the cycle, > Tout has to commit before T0 commits -- and thus before Tin > starts. If we're going to put this into the README-SSI as the proof of the validity of this optimization, I'd like to have a footnote pointing to a paper describing the "first commit in the cycle" aspect of a dangerous structure. Got any favorites, or should I fall back on a google search? -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] Hacking gram.y Error syntax error at or near "MERGEJOIN"
On Thu, Jun 2, 2011 at 11:17 AM, HuangQi wrote: > Hi, thanks a lot for your ideas. But I've done all these things. I've > checked the gram.y and kwlist.h files many times but can not find what's > wrong. So is there any possibility that the problem comes from something > after parser, though it seems it should comes from parser? It seems very unlikely, but you could probably find out the answer yourself by using a debugger. Set a breakpoint on errfinish and see where the error gets thrown from. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Estimating total amount of shared memory required by postmaster
Hello, We've recently come across the task of estimating the size of shared memory required for PostgreSQL to start. This comes from the problem of validating postgresql.conf files (http://archives.postgresql.org/pgsql-hackers/2011-03/msg01831.php), i.e. checking that the server will be able to start with new configuration options without actually performing the restart. Currently, I see a couple of ways to get the estimate: - Use the code from ipci.c to get the total size of the shared memory segment that Postmaster would be allocating with the given configuration options (shared_buffers, etc.). This would require getting the actual amount of available shared memory somehow, which is platform dependent and might not be very reliable. The other downside is that the code would need to be updated if the original estimates in ipci.c changes. - Try to actually allocate the shared memory in a way postmaster does this nowadays, if the process fails - analyze the error code to check whether the failure is due to the shmmax or shmmall limits being too low. This would need to be run as a separate process (not postmaster's child) to avoid messing with the postmaster's own shared memory, which means that this would be hard to implement as a user-callable stored function. I'm also looking for other ideas. Any suggestions? Thank you, Alexey -- Command Prompt, Inc. http://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] storing TZ along timestamps
On Thu, 2011-06-02 at 18:46 +, Christopher Browne wrote:
> > 1. How would the time-zone be defined in this composite? Offset from GMT?
> > Timezone (well, link thereto) with all DST rules intact? Would "extract"
> > need to be modified to include the ability to grab the timezone?
>
> That doesn't seem appropriate, because timezones are not always
> represented by strict offsets from GMT. Some frequently-used
> timezones represent variable offsets. ("EDT/EST", I'm looking at
> you!)
In conjunction with a specific timestamp, a timezone does strictly map
to a single offset.
That is, unless it's a timestamp in the future, and someone decides to
adjust a timezone before the timestamp actually occurs. But that's a
problem with the current timestamptz implementation anyway...
> > Since this isn't going to alter my current beloved timestamptz and I don't
> > have a use-case I leave the decisions on the above to others. But in my
> > imagined use-cases I still see the originating zone as a separate piece of
> > information better handled as a different column - for example sorting by
> > timestamp plus priority or selecting everything for a specific time zone.
I have a similar inclination. ">" seems like the fundamental operation
you'd want to perform on any timestamp (perhaps more so than equality),
and that's not well-defined if there is no total order (but several
meaningful partial orders).
However, I do see some nice benefits, too. The main one is that you can
easily get either local time or GMT out of it. So you can answer queries
such as "which of these activities occurred outside of normal business
hours" as well as "which of these events happened first". It would take
a little care to use properly, however.
Regards,
Jeff Davis
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] storing TZ along timestamps
On Thu, Jun 2, 2011 at 6:06 PM, Steve Crawford
wrote:
> On 06/01/2011 05:18 PM, Alvaro Herrera wrote:
>>
>> Excerpts from Jeff Davis's message of mié jun 01 19:57:40 -0400 2011:
>>>
>>> On Fri, 2011-05-27 at 16:43 -0400, Alvaro Herrera wrote:
Hi,
One of our customers is interested in being able to store original
timezone along with a certain timestamp.
>>>
>>> I assume that you're talking about a new data type, not augmenting the
>>> current types, correct?
>>
>> Yes
>>
> That eliminates many of my issues - I just didn't want the type changed
> underneath me. But some considerations remain - including some new that have
> crossed my mind:
>
> 1. How would the time-zone be defined in this composite? Offset from GMT?
> Timezone (well, link thereto) with all DST rules intact? Would "extract"
> need to be modified to include the ability to grab the timezone?
That doesn't seem appropriate, because timezones are not always
represented by strict offsets from GMT. Some frequently-used
timezones represent variable offsets. ("EDT/EST", I'm looking at
you!)
> 2. What would be the precedence for defining originating timezone? Default?
> Set timezone to? ...at time zone...? Based on the timestamp (2011-06-02
> 12:34:56-07)?
>
> 3. Would indexing/sorting include the originating zone? If so, how would
> time zones collate (base offset, actual offset based on the timestamp,
> name)?
Some timezones contain discontinuities, so that the notion of sorting
them seems implausible, as there isn't properly an "ordering."
> 4. What would be the corresponding type when used with Perl/PHP/Python/...
> applications - would they require special non-standard handling?
>
> Since this isn't going to alter my current beloved timestamptz and I don't
> have a use-case I leave the decisions on the above to others. But in my
> imagined use-cases I still see the originating zone as a separate piece of
> information better handled as a different column - for example sorting by
> timestamp plus priority or selecting everything for a specific time zone.
I'd tend to think that this is best captured by having two pieces of
information:
a) The timestamp in UTC terms, so that it's a totally stable value,
which is amenable to comparison against other timestamps (irrespective
of timezone)
b) A symbolic representation of the timezone, perhaps its name.
It's not at all obvious that these ought to be treated as a singular data type.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"
--
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: patch review : Add ability to constrain backend temporary file space
On Thu, Jun 2, 2011 at 7:36 AM, Tom Lane wrote: > Also, once you free yourself from the analogy to work_mem, you could > adopt some more natural unit than KB. I'd think MB would be a practical > unit size, and would avoid (at least for the near term) the need to make > the parameter a float. As long as users can specify any unit when they input the parameter it doesn't really matter what unit the variable is stored in. I'm not sure the GUC infrastructure can currently handle megabytes as the native units for a guc though. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] storing TZ along timestamps
On Fri, 2011-05-27 at 16:43 -0400, Alvaro Herrera wrote: > One of our customers is interested in being able to store original > timezone along with a certain timestamp. Another thing to consider is that this will eliminate any useful total order. You could define an arbitrary total order, of course, just to allow BTrees for equality searches. However, I don't think you should define ">" (and other non-equality comparator operators) according to that total order -- they should be more hidden like "~>~". ">" should not exist as an operator over this type at all. I also do not like the idea of having "=" mean "equivalent after timezone adjustment". If we're making a distinction between "2000-01-01 10:00:00 +03" and "2000-01-01 9:00:00 +02", then "=" should not obscure that distinction. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] InitProcGlobal cleanup
On Thu, Jun 2, 2011 at 1:53 PM, Tom Lane wrote: > Robert Haas writes: >> While working on my patch to reduce the overhead of frequent table >> locks, I had cause to monkey with InitProcGlobal() and noticed that >> it's sort of a mess. For reasons that are not clear to me, it >> allocates one of the three PGPROC arrays using ShemInitStruct() and >> the other two using ShmemAlloc(). I'm not clear on why we should use >> different functions for different allocations, and it also seems like >> it would make sense to do the whole allocation at once instead of >> doing three separate ones. Also, the setup of AuxiliaryProcs is >> strangely split into two parts, one at the top of the function (where >> we allocate the memory) and the other at the bottom (where we >> initialize it), but there's no clear reason to break it up like that. > >> Any reason not to instead do something like the attached? > > I find this a whole lot less readable, because you've largely obscured > the fact that there are three or four different groups of PGPROC > structures being built here and then linked into several different > lists/arrays. The code might be okay but it desperately needs more > comments. OK, here's a version with more comments. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company init-proc-global-cleanup-v2.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] storing TZ along timestamps
On Thu, Jun 2, 2011 at 12:55 PM, Alvaro Herrera wrote: >> >> > One of our customers is interested in being able to store original >> >> > timezone along with a certain timestamp. >> >> >> >> I assume that you're talking about a new data type, not augmenting the >> >> current types, correct? >> > >> > Yes >> >> why not use a composite type for that? performance maybe? > > To avoid having to implement all the operators and lookup tables (of > timezones) in userland, mainly. Probably performance would be affected > too, not sure, but that's not the main point. right -- I see where you are going with this. ok, some random questions: *) what about making a 'timezone' type in addition to (or even instead of) the timezonetz_inputtz? Then you could in theory treat the your proposed type as a composite of timezonetz and timezone, just as timestamptz is a 'composite' of date and timetz. (note I'm not necessarily arguing against the creation of a specific unified type -- performance is important for time types). *) in/out formats...what would be the wire formats of your type -- in particular, the binary format? *) do you see your type interacting with various datetime function (like extract) or will a cast be required? Interval math? *) how does ordering and uniqueness apply to same timestamps with unique input time zones? merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Estimating total amount of shared memory required by postmaster
Alexey Klyukin writes: > We've recently come across the task of estimating the size of shared memory > required for PostgreSQL to start. > ... > - Try to actually allocate the shared memory in a way postmaster does this > nowadays, if the process fails - analyze the error code to check whether the > failure is due to the shmmax or shmmall limits being too low. This would > need to be run as a separate process (not postmaster's child) to avoid > messing with the postmaster's own shared memory, which means that this would > be hard to implement as a user-callable stored function. The results of such a test wouldn't be worth the electrons they're written on anyway: you're ignoring the likelihood that two instances of shared memory would overrun the kernel's SHMALL limit, when a single instance would be fine. Given that you can't do it in the context of a live installation, just trying to start the postmaster and seeing if it works (same as initdb does) seems as good as anything else. 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] pgpool versus sequences
Robert Haas writes: > On Wed, Jun 1, 2011 at 6:53 PM, Tom Lane wrote: >> Please note also that what pgpool users have got right now is a time >> bomb, which is not better than immediately-visible breakage. I would >> prefer to try to get this change out ahead of widespread adoption of the >> broken pgpool version. > Hmm, I gather from what Tatsuo is saying at the web site that this has > only been broken since the release of 3.0 on February 23rd, so given > that I think your approach makes sense. Done, and I also installed a kluge to clean up the damage retroactively during any nextval/setval operation. 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] BLOB support
Tom Lane Thursday 02 of June 2011 19:43:16 > =?utf-8?q?Rados=C5=82aw_Smogura?= writes: > > Tom Lane Thursday 02 of June 2011 16:42:42 > > > >> Yes. I think the appropriate problem statement is "provide streaming > >> access to large field values, as an alternative to just fetching/storing > >> the entire value at once". I see no good reason to import the entire > >> messy notion of LOBS/CLOBS. (The fact that other databases have done it > >> is not a good reason.) > > > > In context of LOBs streaming is resolved... I use current LO > > functionallity (so driver may be able to read LOBs as psql \lo_export > > does it or using COPY subprotocol) and client should get just LO's id. > > Just to be clear: I do not want to expose a concept of object IDs for > field values in the first place. All of the problems you enumerate stem > from the idea that LOBs ought to be a distinct kind of field, and I > don't buy that. > > regards, tom lane So do I understand good should We think about create bettered TOAST to support larger values then 30-bit length? I like this much more, but without Objects ID quering relation with lobs will require to lock relation for some time, as client will need to reference LOB in some way, I think using TID or some derivative of TID, am I right? Regards, Radek -- 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] Domains versus polymorphic functions, redux
On Tue, May 24, 2011 at 2:54 PM, Tom Lane wrote: > "David E. Wheeler" writes: >> On May 24, 2011, at 11:30 AM, Tom Lane wrote: >>> I guess that the question that's immediately at hand is sort of a >>> variant of that, because using a polymorphic function declared to take >>> ANYARRAY on a domain-over-array really is using a portion of the base >>> type's functionality. What we've learned from bug #5717 and the >>> subsequent issues is that using that base functionality without >>> immediately abandoning the notion that the domain has some life of its >>> own (ie, immediately casting to the base type) is harder than it looks. > >> Well, in the ANYELEMENT context (or ANYARRAY), what could be lost by >> "abandoning the notion that the domain has some life of its own"? > > I'm starting to think that maybe we should separate the two cases after > all. If we force a downcast for ANYARRAY matching, we will fix the loss > of functionality induced by the bug #5717 patch, and it doesn't seem > like anyone has a serious objection to that. What to do for ANYELEMENT > seems to be a bit more controversial, and at least some of the proposals > aren't reasonable to do in 9.1 at this stage. Maybe we should just > leave ANYELEMENT as-is for the moment, and reconsider that issue later? If we haven't lost any functionality with respect to ANYELEMENT in 9.1, then I don't think we ought to try to improve/change/break it in 9.1 either. But I do think we need to do something about ANYARRAY matching, and your proposed fix seems pretty reasonable to me. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] InitProcGlobal cleanup
On Thu, Jun 2, 2011 at 3:13 PM, Tom Lane wrote: > Robert Haas writes: >> OK, here's a version with more comments. > > Looks OK to me, assuming you've checked that the right number of PGPROCs > are getting created (in particular the AV launcher is no longer > accounted for explicitly). That should be fine, due to the way MaxBackends is initialized. See related comment around guc.c:103. I'll commit this to 9.2 after we branch. (When are we doing that, BTW?) -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Identifying no-op length coercions
Hi Alexey, On Thu, Jun 02, 2011 at 05:08:51PM +0300, Alexey Klyukin wrote: > Looks like this thread has silently died out. Is there an agreement on the > syntax and implementation part? We (CMD) have a customer, who is interested in > pushing this through, so, if we have a patch, I'd be happy to assist in > reviewing it. I think we have a consensus on the implementation. We didn't totally lock down the syntax. Tom and I seem happy to have no SQL exposure at all, so that's what I'm planning to submit. However, we were pretty close to a syntax consensus in the event that it becomes desirable to do otherwise. Is your interest in cheap varchar(N)->varchar(N+M) conversions specifically, or in some broader application of this facility? Thanks for volunteering to review; that will be a big help. Actually, I could especially use some feedback now on a related design and implementation: http://archives.postgresql.org/message-id/20110524104029.gb18...@tornado.gateway.2wire.net Note that the third and fifth sentences of that description are incorrect. The rest stands without them. Even just some feedback on the mundane issue noted in the last paragraph would help. Thanks, nm -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] InitProcGlobal cleanup
Robert Haas writes: > OK, here's a version with more comments. Looks OK to me, assuming you've checked that the right number of PGPROCs are getting created (in particular the AV launcher is no longer accounted for explicitly). 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] BLOB support
Dne 2.6.2011 15:49, Pavel Stehule napsal(a):
> 2011/6/2 Pavel Golub :
>> Hello, Pavel.
>>
>> You wrote:
>>
>> PS> 2011/6/2 Peter Eisentraut :
On ons, 2011-06-01 at 22:00 +0200, Radosław Smogura wrote:
> I partialy implemented following missing LOBs types. Requirement for this
> was
> to give ability to create (B/C)LOB columns and add casting functionality
> e.g.
> SET my_clob = 'My long text'.
>
> Idea is as follow:
> 0. Blob is two state object: 1st in memory contains just bytea, serialized
> contains Oid of large object.
> 1. Each type has additional boolean haslobs, which is set recursivly.
> 2. Relation has same bool haslobs (used to speed up tables without LOBs)
> 3. When data are inserted/updated then "special" function is called and
> tuple
> is modified in this way all LOBs are serialized to (old) LOB table and
> just
> Oid is stored.
> 4. When removed LOB is removed from (old) LOB table.
Superficially, this looks like a reimplementation of TOAST. What
functionality exactly do you envision that the BLOB and CLOB types would
need to have that would warrant treating them different from, say, bytea
and text?
>>
>> PS> a streaming for bytea could be nice. A very large bytea are limited by
>> PS> query size - processing long query needs too RAM,
>>
>> LO (oid) solves this, doesn't it?
>
> partially
>
> There is a few disadvantages LO against bytea, so there are requests
> for "smarter" API for bytea.
>
> Significant problem is different implementation of LO for people who
> have to port application to PostgreSQL from Oracle, DB2. There are
> some JDBC issues too.
>
> For me - main disadvantage of LO in one space for all. Bytea removes
> this disadvantage, but it is slower for lengths > 20 MB. It could be
> really very practical have a possibility insert some large fields in
> second NON SQL stream. Same situation is when large bytea is read.
Yes, being able to do this (without the need to use LOs as they have
their own set of problems - no FKs, etc.) would help a lot of people who
want/need to keep memory usage low.
What I'd like to see is the ability to stream BYTEA columns in both
directions - let's not reinvent the API, other databases already support
this. E.g. with Oracle you can do this using PDO (PHP):
prepare("insert into images (imagedata)".
"VALUES (EMPTY_BLOB())");
$fp = fopen('./myfile.data', 'rb');
$stmt->bindParam(1, $fp, PDO::PARAM_LOB);
$stmt->execute();
?>
If we could make it work in a similar way, that would be great. There
are some more details at http://cz2.php.net/manual/en/pdo.lobs.php.
Tomas
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
9.2 branch and 9.1beta2 timing (was Re: [HACKERS] InitProcGlobal cleanup)
Robert Haas writes: > I'll commit this to 9.2 after we branch. (When are we doing that, BTW?) Sometime in the next two weeks I guess ;-). At the PGCon meeting we said 1 June, but seeing that we still have a couple of open beta2 issues I'm not in a hurry. I think a reasonable plan would be to fix the currently known open issues, push out a beta2, and then branch. That would avoid double-patching. We'd want to get this done before the commitfest starts on the 15th, of course, so if we stick to usual release scheduling that would mean wrap next Thursday (June 9), beta2 announce on Monday the 13th, and make the branch somewhere around that date as well. Comments? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] BLOB support
Dne 2.6.2011 15:18, k...@rice.edu napsal(a): > On Thu, Jun 02, 2011 at 02:58:52PM +0200, Pavel Stehule wrote: >> 2011/6/2 Peter Eisentraut : >>> Superficially, this looks like a reimplementation of TOAST. What >>> functionality exactly do you envision that the BLOB and CLOB types would >>> need to have that would warrant treating them different from, say, bytea >>> and text? >>> >> >> a streaming for bytea could be nice. A very large bytea are limited by >> query size - processing long query needs too RAM, >> >> Pavel >> > > +1 for a streaming interface to bytea/text. I do agree that there is no need > to reinvent the TOAST architecture with another name, just improve the > existing > implementation. Building a "parallel" architecture that mimics TOAST is obviously a bad idea. But I do have a curious question - the current LO approach is based on splitting the data into small chunks (2kB) and storing those chunks in a bytea column of the pg_largeobject table. How much overhead does all this mean? What if there is a special kind of blocks for binary data, that limits the amount of chunks and TOAST? Actually this probably would not need a special type of block, but when writing a block there would be a single row with as much data as possible (and some metadata). I.e. there would be almost 8kB of compressed data. This would probably bring some restrictions (e.g. inability to update the data, but I don't think that's possible with the current LO anyway. Has anyone thought about this? regards Tomas -- 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] Domains versus polymorphic functions, redux
Robert Haas writes: > On Tue, May 24, 2011 at 2:54 PM, Tom Lane wrote: >> I'm starting to think that maybe we should separate the two cases after >> all. If we force a downcast for ANYARRAY matching, we will fix the loss >> of functionality induced by the bug #5717 patch, and it doesn't seem >> like anyone has a serious objection to that. What to do for ANYELEMENT >> seems to be a bit more controversial, and at least some of the proposals >> aren't reasonable to do in 9.1 at this stage. Maybe we should just >> leave ANYELEMENT as-is for the moment, and reconsider that issue later? > If we haven't lost any functionality with respect to ANYELEMENT in > 9.1, then I don't think we ought to try to improve/change/break it in > 9.1 either. But I do think we need to do something about ANYARRAY > matching, and your proposed fix seems pretty reasonable to me. Yeah, the thread seems to have died off without anyone having a better idea. I'll see about making this happen. 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] BLOB support
On Thu, Jun 02, 2011 at 01:43:16PM -0400, Tom Lane wrote: > =?utf-8?q?Rados=C5=82aw_Smogura?= writes: > > Tom Lane Thursday 02 of June 2011 16:42:42 > >> Yes. I think the appropriate problem statement is "provide streaming > >> access to large field values, as an alternative to just fetching/storing > >> the entire value at once". I see no good reason to import the entire > >> messy notion of LOBS/CLOBS. (The fact that other databases have done it > >> is not a good reason.) > > > In context of LOBs streaming is resolved... I use current LO functionallity > > (so driver may be able to read LOBs as psql \lo_export does it or using > > COPY > > subprotocol) and client should get just LO's id. > > Just to be clear: I do not want to expose a concept of object IDs for > field values in the first place. All of the problems you enumerate stem > from the idea that LOBs ought to be a distinct kind of field, and I > don't buy that. > I think you're saying no OIDs exposed to the SQL i.e. actually stored in a field and returned by a SELECT? (Which seems to be the proposal). As I mentioned recently on another list, I've wrapped a block-oriented "streaming" interface over bytea in python for a web app, specifically to deal with the latency and memory footprint issues of storing 'largish' files directly in the db. I find that with a 64K blocksize, latency is 'good enough' and substr() seems to be constant time for a given size, no matter what part of the bytea value I'm fetching: toast does a fine job of random access. I was musing about providing a way to use the existing client lo streaming interface (rather than the backend bits) for this type of access. The thing called an OID in the client interface is really just a nonce to tell the backend what data to send. With a single generator function: SELECT CASE WHEN is_lo THEN my_loid ELSE make_lo_oid(my_bytea) END FROM my_file_table WHERE id = 34534; Then plugging that back into the lo interface from the client side, would let me use bytea as I currently do for files under 1GB, lo for larger, and gain client side streaming that is transparent to the storage of that particular value. Admittedly, application software would still need to know how to _store_ different values, and manage large objects, with all the pain that entails. But there's some gain in unifying the reading part. Hard to not call it an oid, since that's what the client libraries already document it as (at least, python does) Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer & Admin, Research Scientistphone: 713-348-6166 Connexions http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: 9.2 branch and 9.1beta2 timing (was Re: [HACKERS] InitProcGlobal cleanup)
On Thu, Jun 2, 2011 at 4:42 PM, Tom Lane wrote: > Robert Haas writes: >> I'll commit this to 9.2 after we branch. (When are we doing that, BTW?) > > Sometime in the next two weeks I guess ;-). At the PGCon meeting we > said 1 June, but seeing that we still have a couple of open beta2 issues > I'm not in a hurry. > > I think a reasonable plan would be to fix the currently known open > issues, push out a beta2, and then branch. That would avoid > double-patching. We'd want to get this done before the commitfest > starts on the 15th, of course, so if we stick to usual release > scheduling that would mean wrap next Thursday (June 9), beta2 announce > on Monday the 13th, and make the branch somewhere around that date as > well. > > Comments? OK by me. It appears that the open items list is a bit stale: http://wiki.postgresql.org/wiki/PostgreSQL_9.1_Open_Items The first item listed there is, I believe, fixed. I'm not sure about the second. You just volunteered to fix the third, and the fourth is awaiting comments on -bugs. The larger problem is that there are likely some other things that should be listed there, but aren't. If anyone is aware of stuff we need to get done, please add it there... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSI predicate locking on heap -- tuple or row?
Heikki Linnakangas wrote: > On 30.05.2011 17:10, Kevin Grittner wrote: >> Heikki Linnakangas wrote: >>> * XXX: for an anomaly to occur, T2 must've committed >>> * before W. Couldn't we easily check that here, or does >>> * the fact that W committed already somehow imply it? >> >> The flags and macros should probably be renamed to make it more >> obvious that this is covered. The flag which SxactHasConflictOut >> is based on is only set when the conflict out is to a transaction >> which committed ahead of the flagged transaction. Regarding the >> other test -- since we have two transactions (Tin and Tout) which >> have not been summarized, and transactions are summarized in >> order of commit, SxactHasSummaryConflictOut implies that the >> transaction with the flag set has not committed before the >> transaction to which it has a rw-conflict out. > > Ah, got it. > >> The problem is coming up with a more accurate name which isn't >> really long. The best I can think of is >> SxactHasConflictOutToEarlierCommit, which is a little long. If >> nobody has a better idea, I guess it's better to have a long name >> than a misleading one. Do you think we need to rename >> SxactHasSummaryConflictOut or just add a comment on that use that >> a summarized transaction will always be committed ahead of any >> transactions which are not summarized? > > Dunno. I guess a comment would do. Can you write a separate patch > for that, please? Attached is a comments-only patch for this, along with one correction to the comments you added and a couple other typos. I'll submit a patch for the README-SSI file once I find a reference I like to a paper describing what Dan's proof uses as a premise -- that the transaction on the rw-conflict *out* side of the pivot must not only be the first of the three transactions in the dangerous structure to commit, but the first in the entire cycle of which the dangerous structure is a part. With that premise as a given, the proof is short, clear, and unassailable; but I think we need a cite to use that argument convincingly. -Kevin *** a/src/backend/storage/lmgr/predicate.c --- b/src/backend/storage/lmgr/predicate.c *** *** 245,250 --- 245,255 #define SxactIsReadOnly(sxact) (((sxact)->flags & SXACT_FLAG_READ_ONLY) != 0) #define SxactHasSummaryConflictIn(sxact) (((sxact)->flags & SXACT_FLAG_SUMMARY_CONFLICT_IN) != 0) #define SxactHasSummaryConflictOut(sxact) (((sxact)->flags & SXACT_FLAG_SUMMARY_CONFLICT_OUT) != 0) + /* + * The following macro actually means that the specified transaction has a + * conflict out *to a transaction which committed ahead of it*. It's hard + * to get that into a name of a reasonable length. + */ #define SxactHasConflictOut(sxact) (((sxact)->flags & SXACT_FLAG_CONFLICT_OUT) != 0) #define SxactIsDeferrableWaiting(sxact) (((sxact)->flags & SXACT_FLAG_DEFERRABLE_WAITING) != 0) #define SxactIsROSafe(sxact) (((sxact)->flags & SXACT_FLAG_RO_SAFE) != 0) *** *** 2708,2714 SetNewSxactGlobalXmin(void) * up in some relatively timely fashion. * * If this transaction is committing and is holding any predicate locks, ! * it must be added to a list of completed serializable transaction still * holding locks. */ void --- 2713,2719 * up in some relatively timely fashion. * * If this transaction is committing and is holding any predicate locks, ! * it must be added to a list of completed serializable transactions still * holding locks. */ void *** *** 2753,2764 ReleasePredicateLocks(const bool isCommit) LWLockAcquire(SerializableXactHashLock, LW_EXCLUSIVE); /* !* We don't hold a lock here, assuming that TransactionId is atomic! * * If this value is changing, we don't care that much whether we get the * old or new value -- it is just used to determine how far !* GlobalSerizableXmin must advance before this transaction can be cleaned !* fully cleaned up. The worst that could happen is we wait for ome more * transaction to complete before freeing some RAM; correctness of visible * behavior is not affected. */ --- 2758,2770 LWLockAcquire(SerializableXactHashLock, LW_EXCLUSIVE); /* !* We don't hold an XidGenLock lock here, assuming that TransactionId is !* atomic! * * If this value is changing, we don't care that much whether we get the * old or new value -- it is just used to determine how far !* GlobalSerizableXmin must advance before this transaction can be fully !* cleaned up. The worst that could happen is we wait for one more * transaction to complete before freeing some RAM; correctness of visible * behavior is not affected. */ *** *** 3860,3867 OnConflict_CheckForSerializationFailure(const SERIALIZABLEXA
