[HACKERS] is there any reason why don't create context info for RAISE EXCEPTION?

2011-06-02 Thread Pavel Stehule
Hello

I am playing with context callback functions. I found so we leave
early this function, when exception is raised by RAISE statement.

I can understand it when level is NOTICE, but it is strange for other
levels. More we can emulate any exception now, but these exceptions
are hidden in context.

Is there some reason why we do it?

I am thinking so more practical is setting estate-err_text =
raise_skip_message only for level NOTICE.

Regards

Pavel Stehule

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Re: patch review : Add ability to constrain backend temporary file space

2011-06-02 Thread Jaime Casanova
On Wed, Jun 1, 2011 at 6:35 PM, Mark Kirkwood
mark.kirkw...@catalyst.net.nz wrote:
 On 01/06/11 09:24, Cédric Villemain wrote:

  Submission review
 

     * The patch is not in context diff format.
     * The patch apply, but contains some extra whitespace.
     * Documentation is here but not explicit about 'temp tables',
 maybe worth adding that this won't limit temporary table size ?
     * There is no test provided. One can be expected to check that the
 feature work.


 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

- why the calculations are done as double?
+   if (temporary_files_size / 1024.0  (double)work_disk)



- 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

-- 
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte y capacitación de PostgreSQL

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] SSI predicate locking on heap -- tuple or row?

2011-06-02 Thread Dan Ports
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

2011-06-02 Thread 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


Re: [HACKERS] Hacking gram.y Error syntax error at or near MERGEJOIN

2011-06-02 Thread Pavel Stehule
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 huangq...@gmail.com:
 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

2011-06-02 Thread 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.

On 2 June 2011 15:49, Pavel Stehule pavel.steh...@gmail.com 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 huangq...@gmail.com:
  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

2011-06-02 Thread Pavel Stehule
Hello

2011/6/2 HuangQi huangq...@gmail.com:
 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 pavel.steh...@gmail.com 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 huangq...@gmail.com:
  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

2011-06-02 Thread Pavel Golub
Hello, Merlin.

You wrote:

MM 2011/6/1 Pavel Golub pa...@microolap.com:
 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

2011-06-02 Thread Pavel Golub
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

2011-06-02 Thread Pavel Golub
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

2011-06-02 Thread Teodor Sigaev

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

2011-06-02 Thread Pavel Stehule
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:
  
  synopsis
! GET DIAGNOSTICS replaceablevariable/replaceable = replaceableitem/replaceable optional , ... /optional;
  /synopsis
  
   This command allows retrieval of system status indicators.  Each
--- 1387,1393 
   command, which has the form:
  
  synopsis
! GET optional CURRENT | STACKED /optional DIAGNOSTICS replaceablevariable/replaceable = replaceableitem/replaceable optional , ... /optional;
  /synopsis
  
   This command allows retrieval of system status indicators.  Each
***
*** 1486,1491 
--- 1486,1516 
   affect only the current function.
  /para
  
+ para
+   Inside a exception handler is possible to use a stacked diagnostics statement. It 
+   allows a access to exception's data: the varnameRETURNED_SQLSTATE/varname contains
+   a SQLSTATE of handled exception. varnameMESSAGE_TEXT/varname contains a message text,
+   varnamePG_EXCEPTION_DETAIL/varname has a text that is shown as exception detail,
+   varnamePG_EXCEPTION_HINT/varname has a hint related to catched exception.
+   varnamePG_EXCEPTION_CONTEXT/varname 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,
+ /para
+ 
+ para
+  An example:
+ programlisting
+ BEGIN
+   ...
+ EXCEPTION WHEN OTHERS THEN
+   GET STACKED DIAGNOSTICS text_var1 = MESSAGE_TEXT,
+   text_var2 = PG_EXCEPTION_DETAIL,
+   text_var3 = PG_EXCEPTION_HINT;
+ END;
+ /programlisting
+ /para
+ 
+ 
 /sect2
  
 sect2 id=plpgsql-statements-null
*** ./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 list	getdiag_list
  %type diagitem getdiag_list_item
  %type ival	getdiag_item getdiag_target
+ %type boolean	getdiag_opt
  
  %type ival	opt_scrollable
  %type fetch	opt_fetch_direction
***
*** 250,256 
--- 251,259 
  %token keyword	K_CLOSE
  %token keyword	K_COLLATE
  %token keyword	K_CONSTANT
+ %token keyword	K_CONTEXT
  %token keyword	K_CONTINUE
+ %token keyword	K_CURRENT
  %token keyword	K_CURSOR
  %token keyword	K_DEBUG
  %token keyword	K_DECLARE
***
*** 263,268 
--- 266,274 
  %token keyword	K_END
  %token keyword	K_ERRCODE
  %token keyword	K_ERROR
+ %token keyword	K_EXCEPTION_CONTEXT
+ %token keyword	K_EXCEPTION_DETAIL
+ %token keyword	K_EXCEPTION_HINT
  %token keyword	K_EXCEPTION

Re: [HACKERS] Re: patch review : Add ability to constrain backend temporary file space

2011-06-02 Thread Mark Kirkwood

On 02/06/11 18:34, Jaime Casanova wrote:

On Wed, Jun 1, 2011 at 6:35 PM, Mark Kirkwood
mark.kirkw...@catalyst.net.nz  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

2011-06-02 Thread Andrew Dunstan



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

2011-06-02 Thread 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?



-- 
Sent 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-06-02 Thread Pavel Stehule
2011/6/2 Peter Eisentraut pete...@gmx.net:
 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

2011-06-02 Thread Heikki Linnakangas

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

2011-06-02 Thread k...@rice.edu
On Thu, Jun 02, 2011 at 02:58:52PM +0200, Pavel Stehule wrote:
 2011/6/2 Peter Eisentraut pete...@gmx.net:
  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

2011-06-02 Thread Pavel Golub
Hello, Pavel.

You wrote:

PS 2011/6/2 Peter Eisentraut pete...@gmx.net:
 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

2011-06-02 Thread Radosław Smogura

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

2011-06-02 Thread Merlin Moncure
On Thu, Jun 2, 2011 at 3:24 AM, Pavel Golub pa...@microolap.com 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

2011-06-02 Thread Robert Haas
On Wed, Jun 1, 2011 at 7:35 PM, Mark Kirkwood
mark.kirkw...@catalyst.net.nz 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-06-02 Thread Pavel Stehule
2011/6/2 Pavel Golub pa...@microolap.com:
 Hello, Pavel.

 You wrote:

 PS 2011/6/2 Peter Eisentraut pete...@gmx.net:
 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

2011-06-02 Thread Robert Haas
On Thu, Jun 2, 2011 at 9:29 AM, Radosław Smogura
rsmog...@softperience.eu 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

2011-06-02 Thread Alexey Klyukin

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 n...@leadboat.com 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

2011-06-02 Thread Andrew Chernow

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

2011-06-02 Thread Robert Haas
On Wed, Jun 1, 2011 at 7:47 PM, Alvaro Herrera
alvhe...@commandprompt.com 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

2011-06-02 Thread Robert Haas
On Wed, Jun 1, 2011 at 6:53 PM, Tom Lane t...@sss.pgh.pa.us 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

2011-06-02 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes:
 On Wed, Jun 1, 2011 at 7:47 PM, Alvaro Herrera
 alvhe...@commandprompt.com 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

2011-06-02 Thread Pavel Golub
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

2011-06-02 Thread Andrew Chernow

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

2011-06-02 Thread Robert Haas
On Wed, Jun 1, 2011 at 7:07 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Alvaro Herrera alvhe...@commandprompt.com 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

2011-06-02 Thread Tom Lane
Robert Haas robertmh...@gmail.com 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

2011-06-02 Thread Tom Lane
Robert Haas robertmh...@gmail.com 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

2011-06-02 Thread Tom Lane
Robert Haas robertmh...@gmail.com 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

2011-06-02 Thread Tom Lane
Marko Kreen mark...@gmail.com 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-06-02 Thread Cédric Villemain
2011/6/2 Robert Haas robertmh...@gmail.com:
 On Wed, Jun 1, 2011 at 7:35 PM, Mark Kirkwood
 mark.kirkw...@catalyst.net.nz 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

2011-06-02 Thread Dave Page
On Wed, Jun 1, 2011 at 3:02 PM, Steve Singer ssin...@ca.afilias.info wrote:
 On 11-06-01 09:30 AM, Christopher Browne wrote:

 On Wed, Jun 1, 2011 at 8:29 AM, Dave Pagedp...@pgadmin.org  wrote:

 On Wed, Jun 1, 2011 at 12:27 PM, Andrew Dunstanand...@dunslane.net
  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

2011-06-02 Thread Alvaro Herrera
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 alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Please test peer (socket ident) auth on *BSD

2011-06-02 Thread Marko Kreen
On Wed, Jun 1, 2011 at 1:03 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Marko Kreen mark...@gmail.com 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 sys/param.h
  #include sys/socket.h
- #ifdef HAVE_UCRED_H
- #include ucred.h
- #endif
- #ifdef HAVE_SYS_UCRED_H
- #include sys/ucred.h
- #endif
  #include netinet/in.h
  #include arpa/inet.h
  #include unistd.h
--- 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 ctype.h
  #include time.h
  #include unistd.h
- #ifdef HAVE_UCRED_H
- #include ucred.h
- #endif
- #ifdef HAVE_SYS_UCRED_H
- #include sys/ucred.h
- #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) || 

Re: [HACKERS] pgpool versus sequences

2011-06-02 Thread Tom Lane
Alvaro Herrera alvhe...@commandprompt.com 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

2011-06-02 Thread Robert Haas
On Thu, Jun 2, 2011 at 10:31 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Robert Haas robertmh...@gmail.com writes:
 On Wed, Jun 1, 2011 at 7:47 PM, Alvaro Herrera
 alvhe...@commandprompt.com 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

2011-06-02 Thread Teodor Sigaev

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

2011-06-02 Thread Alvaro Herrera
Excerpts from Tom Lane's message of jue jun 02 10:31:58 -0400 2011:
 Robert Haas robertmh...@gmail.com writes:
  On Wed, Jun 1, 2011 at 7:47 PM, Alvaro Herrera
  alvhe...@commandprompt.com 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 alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] PQdeleteTuple function in libpq

2011-06-02 Thread Pavel Golub
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

2011-06-02 Thread Marko Kreen
On Thu, Jun 2, 2011 at 5:49 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Marko Kreen mark...@gmail.com 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 sys/param.h
  #include sys/socket.h
- #ifdef HAVE_UCRED_H
- #include ucred.h
- #endif
- #ifdef HAVE_SYS_UCRED_H
- #include sys/ucred.h
- #endif
  #include netinet/in.h
  #include arpa/inet.h
  #include unistd.h
--- 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 ctype.h
  #include time.h
  #include unistd.h
- #ifdef HAVE_UCRED_H
- #include ucred.h
- #endif
- #ifdef HAVE_SYS_UCRED_H
- #include sys/ucred.h
- #endif
  
  

Re: [HACKERS] PQdeleteTuple function in libpq

2011-06-02 Thread Alvaro Herrera
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
 alvhe...@commandprompt.com 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 alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Please test peer (socket ident) auth on *BSD

2011-06-02 Thread Andrew Dunstan



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

2011-06-02 Thread Alvaro Herrera
Excerpts from Tom Lane's message of jue jun 02 11:10:00 -0400 2011:
 Alvaro Herrera alvhe...@commandprompt.com 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 alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] PQdeleteTuple function in libpq

2011-06-02 Thread Merlin Moncure
On Thu, Jun 2, 2011 at 10:02 AM, Alvaro Herrera
alvhe...@commandprompt.com 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

2011-06-02 Thread Tom Lane
Teodor Sigaev teo...@sigaev.ru 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

2011-06-02 Thread HuangQi
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-06-02 Thread Pavel Stehule
2011/6/2 Tom Lane t...@sss.pgh.pa.us:
 Robert Haas robertmh...@gmail.com 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

2011-06-02 Thread Andrew Dunstan



On 06/02/2011 12:04 PM, Marko Kreen wrote:

On Thu, Jun 2, 2011 at 6:59 PM, Andrew Dunstanand...@dunslane.net  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

2011-06-02 Thread Tom Lane
Andrew Dunstan and...@dunslane.net writes:
 On 06/02/2011 12:04 PM, Marko Kreen wrote:
 On Thu, Jun 2, 2011 at 6:59 PM, Andrew Dunstanand...@dunslane.net  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

2011-06-02 Thread Andrew Chernow

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

2011-06-02 Thread Teodor Sigaev

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

2011-06-02 Thread Robert Haas
On Thu, Jun 2, 2011 at 10:47 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Robert Haas robertmh...@gmail.com 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 t...@sss.pgh.pa.us
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

2011-06-02 Thread Merlin Moncure
On Thu, Jun 2, 2011 at 10:57 AM, Alvaro Herrera
alvhe...@commandprompt.com 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
 alvhe...@commandprompt.com 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

2011-06-02 Thread Marko Kreen
On Thu, Jun 2, 2011 at 7:20 PM, Andrew Dunstan and...@dunslane.net wrote:
 On 06/02/2011 12:04 PM, Marko Kreen wrote:
 On Thu, Jun 2, 2011 at 6:59 PM, Andrew Dunstanand...@dunslane.net
  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

2011-06-02 Thread Radosław Smogura
Tom Lane t...@sss.pgh.pa.us Thursday 02 of June 2011 16:42:42
 Robert Haas robertmh...@gmail.com 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

2011-06-02 Thread Marko Kreen
On Thu, Jun 2, 2011 at 6:59 PM, Andrew Dunstan and...@dunslane.net 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

2011-06-02 Thread Tom Lane
Marko Kreen mark...@gmail.com 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

2011-06-02 Thread Robert Haas
On Thu, Jun 2, 2011 at 10:58 AM, Cédric Villemain
cedric.villemain.deb...@gmail.com 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

2011-06-02 Thread Robert Haas
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

2011-06-02 Thread Tom Lane
Teodor Sigaev teo...@sigaev.ru 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

2011-06-02 Thread Andrew Dunstan



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
alvhe...@commandprompt.com  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

2011-06-02 Thread Alvaro Herrera
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 alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] creating CHECK constraints as NOT VALID

2011-06-02 Thread Alvaro Herrera
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 alvhe...@commandprompt.com
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

2011-06-02 Thread Alvaro Herrera
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 alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Please test peer (socket ident) auth on *BSD

2011-06-02 Thread Alvaro Herrera
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
 alvhe...@commandprompt.com 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 alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] Please test peer (socket ident) auth on *BSD

2011-06-02 Thread Marko Kreen
On Thu, Jun 2, 2011 at 7:44 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Marko Kreen mark...@gmail.com writes:
 On Thu, Jun 2, 2011 at 7:20 PM, Andrew Dunstan and...@dunslane.net 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

2011-06-02 Thread Tom Lane
Robert Haas robertmh...@gmail.com 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

2011-06-02 Thread Marko Kreen
On Thu, Jun 2, 2011 at 7:31 PM, Alvaro Herrera
alvhe...@commandprompt.com 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

2011-06-02 Thread Tom Lane
Marko Kreen mark...@gmail.com writes:
 On Thu, Jun 2, 2011 at 7:20 PM, Andrew Dunstan and...@dunslane.net 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

2011-06-02 Thread Peter Eisentraut
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

2011-06-02 Thread Steve Crawford

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

2011-06-02 Thread Alvaro Herrera
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
 alvhe...@commandprompt.com 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 alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] BLOB support

2011-06-02 Thread Tom Lane
=?utf-8?q?Rados=C5=82aw_Smogura?= rsmog...@softperience.eu writes:
 Tom Lane t...@sss.pgh.pa.us 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?

2011-06-02 Thread Kevin Grittner
Dan Ports d...@csail.mit.edu 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

2011-06-02 Thread Robert Haas
On Thu, Jun 2, 2011 at 11:17 AM, HuangQi huangq...@gmail.com 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

2011-06-02 Thread Alexey Klyukin
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

2011-06-02 Thread Jeff Davis
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

2011-06-02 Thread Christopher Browne
On Thu, Jun 2, 2011 at 6:06 PM, Steve Crawford
scrawf...@pinpointresearch.com 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

2011-06-02 Thread Greg Stark
On Thu, Jun 2, 2011 at 7:36 AM, Tom Lane t...@sss.pgh.pa.us 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

2011-06-02 Thread Jeff Davis
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

2011-06-02 Thread Robert Haas
On Thu, Jun 2, 2011 at 1:53 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Robert Haas robertmh...@gmail.com 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

2011-06-02 Thread Merlin Moncure
On Thu, Jun 2, 2011 at 12:55 PM, Alvaro Herrera
alvhe...@commandprompt.com 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

2011-06-02 Thread Tom Lane
Alexey Klyukin al...@commandprompt.com 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

2011-06-02 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes:
 On Wed, Jun 1, 2011 at 6:53 PM, Tom Lane t...@sss.pgh.pa.us 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

2011-06-02 Thread Radosław Smogura
Tom Lane t...@sss.pgh.pa.us Thursday 02 of June 2011 19:43:16
 =?utf-8?q?Rados=C5=82aw_Smogura?= rsmog...@softperience.eu writes:
  Tom Lane t...@sss.pgh.pa.us 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

2011-06-02 Thread Robert Haas
On Tue, May 24, 2011 at 2:54 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 David E. Wheeler da...@kineticode.com 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

2011-06-02 Thread Robert Haas
On Thu, Jun 2, 2011 at 3:13 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Robert Haas robertmh...@gmail.com 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

2011-06-02 Thread Noah Misch
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

2011-06-02 Thread Tom Lane
Robert Haas robertmh...@gmail.com 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

2011-06-02 Thread Tomas Vondra
Dne 2.6.2011 15:49, Pavel Stehule napsal(a):
 2011/6/2 Pavel Golub pa...@microolap.com:
 Hello, Pavel.

 You wrote:

 PS 2011/6/2 Peter Eisentraut pete...@gmx.net:
 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):

?php

$db = new PDO('oci:', 'scott', 'tiger');
$stmt = $db-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)

2011-06-02 Thread Tom Lane
Robert Haas robertmh...@gmail.com 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

2011-06-02 Thread Tomas Vondra
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 pete...@gmx.net:
 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

2011-06-02 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes:
 On Tue, May 24, 2011 at 2:54 PM, Tom Lane t...@sss.pgh.pa.us 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

2011-06-02 Thread Ross J. Reedstrom
On Thu, Jun 02, 2011 at 01:43:16PM -0400, Tom Lane wrote:
 =?utf-8?q?Rados=C5=82aw_Smogura?= rsmog...@softperience.eu writes:
  Tom Lane t...@sss.pgh.pa.us 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


  1   2   >