[HACKERS] orafce on 64 bit windows

[Pavel Stehule]

Hello

I released 3.0.3 version of PostgreSQL.

Please, can somebody to create package for 64 bit windows and test it?

Regards

Pavel Stehule

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

[HACKERS] Question regarding psql or libpq

[Tatsuo Ishii]

Hi,

It seems psql(or libpq) connects to PostgreSQL twice when md5 auth is
required. Here is a strace log on my Linux machine. Is there any
reason for this?  IMO frontend/backend protocol allows to send salt
after receiving AuthenticationMD5Password using the same socket. So
there's no reason to close the socket and make it again. It seems to
be just waste of resource.

:
:
socket(PF_FILE, SOCK_STREAM, 0) = 3 <-- create a socket
fcntl(3, F_SETFL, O_RDONLY|O_NONBLOCK)  = 0
fcntl(3, F_SETFD, FD_CLOEXEC)   = 0
connect(3, {sa_family=AF_FILE, path="/tmp/.s.PGSQL.5432"...}, 110) = 0
getsockopt(3, SOL_SOCKET, SO_ERROR, [28580018057641984], [4]) = 0
getsockname(3, {sa_family=AF_FILE, path=@""}, [2]) = 0
poll([{fd=3, events=POLLOUT|POLLERR}], 1, -1) = 1 ([{fd=3, revents=POLLOUT}])
sendto(3, "\0\0\0006\0\3\0\0user\0foo\0database\0test\0ap"..., 54, 
MSG_NOSIGNAL, NULL, 0) = 54
poll([{fd=3, events=POLLIN|POLLERR}], 1, -1) = 1 ([{fd=3, revents=POLLIN}])
recvfrom(3, "R\0\0\0\f\0\0\0\5\10\6N%"..., 16384, 0, NULL, NULL) = 13
close(3)  <-- close the socket
open("/dev/tty", O_RDONLY)  = 3
open("/dev/tty", O_WRONLY|O_CREAT|O_TRUNC, 0666) = 4
ioctl(3, SNDCTL_TMR_TIMEBASE or TCGETS, {B38400 opost isig icanon echo ...}) = 0
ioctl(3, SNDCTL_TMR_CONTINUE or TCSETSF, {B38400 opost isig icanon -echo ...}) 
= 0
fstat(4, {st_mode=S_IFCHR|0666, st_rdev=makedev(5, 0), ...}) = 0
ioctl(4, SNDCTL_TMR_TIMEBASE or TCGETS, {B38400 opost isig icanon -echo ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 
0x7feffbd21000
write(4, "Password for user foo: "..., 23Password for user foo: ) = 23
fstat(3, {st_mode=S_IFCHR|0666, st_rdev=makedev(5, 0), ...}) = 0
ioctl(3, SNDCTL_TMR_TIMEBASE or TCGETS, {B38400 opost isig icanon -echo ...}) = 0
mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 
0x7feffbd2
read(3, "bar\n"..., 4096)   = 4
ioctl(3, SNDCTL_TMR_CONTINUE or TCSETSF, {B38400 opost isig icanon echo ...}) = 0
write(4, "\n"..., 1
)= 1
close(3)= 0
munmap(0x7feffbd2, 4096)= 0
close(4)= 0
munmap(0x7feffbd21000, 4096)= 0
socket(PF_FILE, SOCK_STREAM, 0) = 3 <-- and create a socket again
fcntl(3, F_SETFL, O_RDONLY|O_NONBLOCK)  = 0
fcntl(3, F_SETFD, FD_CLOEXEC)   = 0
connect(3, {sa_family=AF_FILE, path="/tmp/.s.PGSQL.5432"...}, 110) = 0
getsockopt(3, SOL_SOCKET, SO_ERROR, [28580018057641984], [4]) = 0
getsockname(3, {sa_family=AF_FILE, path=@""}, [2]) = 0
poll([{fd=3, events=POLLOUT|POLLERR}], 1, -1) = 1 ([{fd=3, revents=POLLOUT}])
sendto(3, "\0\0\0006\0\3\0\0user\0foo\0database\0test\0ap"..., 54, 
MSG_NOSIGNAL, NULL, 0) = 54
poll([{fd=3, events=POLLIN|POLLERR}], 1, -1) = 1 ([{fd=3, revents=POLLIN}])
recvfrom(3, "R\0\0\0\f\0\0\0\5\264 \231\352"..., 16384, 0, NULL, NULL) = 13
sendto(3, "p\0\0\0(md5764161564364fab9083f39d97"..., 41, MSG_NOSIGNAL, NULL, 0) 
= 41
poll([{fd=3, events=POLLIN|POLLERR}], 1, -1) = 1 ([{fd=3, revents=POLLIN}])
recvfrom(3, "R\0\0\0\10\0\0\0\0S\0\0\0\32application_name\0ps"..., 16384, 0, 
NULL, NULL) = 325
:
:
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

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

Re: [HACKERS] Complier warnings on mingw gcc 4.5.0

[Tom Lane]

Andrew Dunstan  writes:
> On 12/15/2010 08:46 PM, Tom Lane wrote:
>> We can't change the meaning of HAVE_INT_OPTRESET because that would
>> break the declaration logic in getopt.c.  I'm thinking we have to
>> complicate the #if logic in postmaster.c and postgres.c.

> I agree.

>> Will look
>> into it as soon as I get done with the contrib/seg patch (ie in an
>> hour or so).

> OK. I'll test any patch you post ASAP.

OK, patch committed so we can get testing from the existing buildfarm
members, but please try on your new installation too.

regards, tom lane

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

Re: [HACKERS] Complier warnings on mingw gcc 4.5.0

[Andrew Dunstan]

On 12/15/2010 08:46 PM, Tom Lane wrote:



I believe #2 is in fact necessary. When I tried just #1 before it
failed. What's the best way to do #2 cleanly?

We can't change the meaning of HAVE_INT_OPTRESET because that would
break the declaration logic in getopt.c.  I'm thinking we have to
complicate the #if logic in postmaster.c and postgres.c.


I agree.


Will look
into it as soon as I get done with the contrib/seg patch (ie in an
hour or so).




OK. I'll test any patch you post ASAP.

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] getting composite types info from libpq

[Daniele Varrazzo]

On Wed, Dec 15, 2010 at 6:56 PM, Merlin Moncure  wrote:
> On Wed, Dec 15, 2010 at 1:25 PM, Daniele Varrazzo
>  wrote:
>> Hello,
>>
>> when a query returns a composite type, the libpq PQftype() function
>> reports the oid of the "record" type. In psycopg:
>>
>>    >>> cur.execute("select (1,2)")
>>    >>> cur.description
>>    (('row', 2249, None, -1, None, None, None),)
>>
>>    test=# select typname from pg_type where oid = 2249;
>>     typname
>>    -
>>     record
>>
>> Is there a way to recursively retrieve the types for the record components?
>
> not without talking to the server, unless you had previously pulled
> pg_attribute data.
>
> select * from pg_attribute where attrelid = 2249;

No, there is no such info in pg_attribute: 2249 is the oid for the
type of a "generic record", not for a specific type.

> This question is more appropriate for -general, but what are you trying to do?

Added -general in copy: please remove -hackers in your reply if you
think this thread is out of place.

I'm hacking on psycopg. Currently it uses PQftype, PQfname and related
functions to inspect the PQresult received after a query in order to
build the python representation of the record. But the inspection is
"flat": if the record contains a composite structure it is currently
returned as an unparsed string:

>>> cur.execute("select ('1'::int, current_date), current_date")
# the date outside the record is easily parsed, for the one inside
the record
>>> cur.fetchone()
('(1,2010-12-16)', datetime.date(2010, 12, 16))
>>> cur.description  # name and oid are the first two fields
(('row', 2249, None, -1, None, None, None),
 ('date', 1082, None, 4, None, None, None))

As the record is created on the fly, I assume there is no structure
left in the catalog for it. If I instead explicitly create the type I
see how to inspect it:

test=> create type intdate as (an_int integer, a_date date);
CREATE TYPE

>>> cur.execute("select (1, current_date)::intdate, current_date")
>>> cur.fetchone()
('(1,2010-12-16)', datetime.date(2010, 12, 16))
>>> cur.description
(('row', 650308, None, -1, None, None, None),
 ('date', 1082, None, 4, None, None, None))

test=> select attname, atttypid from pg_attribute where attrelid = 650306;
 attname | atttypid
-+--
 an_int  |   23
 a_date  | 1082

but even in this case it seems it would take a second query to inspect
the type and even here It doesn't seem I could use
PQgetvalue/PQgetlength to read the internal components of the
composite values.

The goal would be to have the query above translated into e.g. a
nested tuple in python:

((1, datetime.date(2010, 12, 16), datetime.date(2010, 12, 16))

and I'd like to know:

1. do I get enough info in the PGresult to inspect anonymous composite types?
2. do I get such info for composite types for which I have schema info
in the catalog, without issuing a second query? (which I don't feel it
is a driver's job)
3. is there any libpq facility to split the string returned after a
composite types into its single components, without having to write a
parser to deal with commas and quotes?

>>> cur.execute("select ('a'::text, 'b,c'::text, 'd''e'::text,
'f\"g'::text)")
>>> print cur.fetchone()[0]
(a,"b,c",d'e,"f""g")

4. are by any chance those info passed on the network, maybe available
in an internal libpq structure, but then not accessible from the libpq
interface?

Thank you very much.

-- Daniele

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

Re: [HACKERS] Complier warnings on mingw gcc 4.5.0

[Tom Lane]

Andrew Dunstan  writes:
> On 12/15/2010 07:24 PM, Tom Lane wrote:
>> Should we backpatch either of these things?

> Yes. We need it to back at least to 9.0.

On reflection I think we probably better fix it back to 8.2, since we're
supposedly supporting Windows on all those branches, and somebody might
try to build any of them on modern mingw.

> I believe #2 is in fact necessary. When I tried just #1 before it 
> failed. What's the best way to do #2 cleanly?

We can't change the meaning of HAVE_INT_OPTRESET because that would
break the declaration logic in getopt.c.  I'm thinking we have to
complicate the #if logic in postmaster.c and postgres.c.  Will look
into it as soon as I get done with the contrib/seg patch (ie in an
hour or so).

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] Complier warnings on mingw gcc 4.5.0

[Andrew Dunstan]

On 12/15/2010 07:24 PM, Tom Lane wrote:


Huh, I wonder why it doesn't match what's in sourceforge CVS?


Sourceforge's CVS is way out of date. CVS tip for the getopt.h is here: 



See 

That does mean we should also look at Cygwin, though, as they supposedly 
share the runtime.


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] Complier warnings on mingw gcc 4.5.0

[Andrew Dunstan]

On 12/15/2010 07:24 PM, Tom Lane wrote:


So I concur with the previous suggestions:

1. Make this problem go away by forcing use of our getopt code on
mingw.

2. Make sure we reset optreset when using our code.  (Probably not
really necessary, but let's just be careful.)

Should we backpatch either of these things?




Yes. We need it to back at least to 9.0.

I believe #2 is in fact necessary. When I tried just #1 before it 
failed. What's the best way to do #2 cleanly?


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] Complier warnings on mingw gcc 4.5.0

[Tom Lane]

Andrew Dunstan  writes:
> On 12/15/2010 06:42 PM, Tom Lane wrote:
>> Hm, where did you get this?

> I downloaded 
> 
>  
> which is allegedly the source for the latest released runtime.

Huh, I wonder why it doesn't match what's in sourceforge CVS?

Anyway, the short answer is that this code has got no visible
commonality with glibc's getopt(), so we need not fear that what
it's doing is likely to start happening elsewhere.  I didn't take
the time to trace through the glibc code exactly; I figure the lack
of trouble reports is sufficient proof that we're not doing anything
that it won't cope with.

So I concur with the previous suggestions:

1. Make this problem go away by forcing use of our getopt code on
mingw.

2. Make sure we reset optreset when using our code.  (Probably not
really necessary, but let's just be careful.)

Should we backpatch either of these things?

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] Complier warnings on mingw gcc 4.5.0

[Andrew Dunstan]

On 12/15/2010 06:42 PM, Tom Lane wrote:

Andrew Dunstan  writes:

Mingw code attached.

Hm, where did you get this?  Because it does have optreset, albeit in a
gratuitously ABI-incompatible fashion:


#ifdef _BSD_SOURCE
/*
  * BSD adds the non-standard `optreset' feature, for reinitialisation
  * of `getopt' parsing.  We support this feature, for applications which
  * proclaim their BSD heritage, before including this header; however,
  * to maintain portability, developers are advised to avoid it.
  */
# define optreset  __mingw_optreset

extern int optreset;
#endif

However, I pulled down the allegedly current mingw source tarball from
sourceforge, and what I found in it is an older version that has *not*
got that change.  The CVS tree there doesn't seem to have it either.
So I'm disinclined to want to rely on setting _BSD_SOURCE, as I first
thought might be the answer --- it looks to me like only some versions
of mingw will respond to that.




I downloaded 
 
which is allegedly the source for the latest released runtime.


The section you cite is indeed in my system's getopt.h.

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] Complier warnings on mingw gcc 4.5.0

[Tom Lane]

Andrew Dunstan  writes:
> Mingw code attached.

Hm, where did you get this?  Because it does have optreset, albeit in a
gratuitously ABI-incompatible fashion:

> #ifdef _BSD_SOURCE
> /*
>  * BSD adds the non-standard `optreset' feature, for reinitialisation
>  * of `getopt' parsing.  We support this feature, for applications which
>  * proclaim their BSD heritage, before including this header; however,
>  * to maintain portability, developers are advised to avoid it.
>  */
> # define optreset  __mingw_optreset
>
> extern int optreset;
> #endif

However, I pulled down the allegedly current mingw source tarball from
sourceforge, and what I found in it is an older version that has *not*
got that change.  The CVS tree there doesn't seem to have it either.
So I'm disinclined to want to rely on setting _BSD_SOURCE, as I first
thought might be the answer --- it looks to me like only some versions
of mingw will respond to 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] Fix for seg picksplit function

[Tom Lane]

Yeb Havinga  writes:
> I think it is time to mark this patch ready for committer:

> The unintuitive result thus far is that sorting outperforms the R-tree 
> bounding boxes style index, as Alexander has demonstrated with several 
> different distributions on 20-11 (uniform, natural (is that a bell 
> curve?), many distinct values)

I spent some time analyzing this patch today.  A few observations:

1. The figures of merit that we're interested in are minimizing the
overlap between the bounding boxes of the split pages, and trying to
divide the items more or less equally between the two pages.  The less
overlap, the fewer subsequent searches will have to descend to both
children.  If we divide the items unequally we risk ending up with a large
(low fill ratio) index, if we're unlucky enough for the less-populated
side to receive few additional entries.  (Which is quite likely, if the
subsequently-added data has distribution like the existing data's.)
Low fill ratio is of course bad for I/O and buffer space consumption.

Alexander's proposed algorithm wins on the equal-division front, since
it always produces an even split, while the existing Guttman algorithm
can produce very uneven splits (more on that below).  However, it's less
clear that Alexander's patch beats the existing code in terms of getting
good bounding boxes.

2. It's not really appropriate to compare the patch directly against git
HEAD, since we know that's broken.  I experimented with just fixing the
s/size_alpha/size_beta/ problem, and observed that search efficiency
(measured as the number of buffers touched in a single query) improved
markedly, but index size didn't, and index build time actually got worse.

3. I think that the test cases presented by Alexander aren't really that
useful, because they all consist of large numbers of small segments.
Almost any split algorithm can do reasonably well there, because you can
always find a split that has pretty minimal overlap between the bounding
boxes of the two child pages; and in particular, changing the distribution
of where the segments are doesn't change things much.  Yeb pointed this
out upthread but didn't pursue the point.  Of course, if there are only
large segments then it's impossible to find minimally-overlapping splits,
so any split algorithm will do poorly.  I think the interesting case is
where there are a few large segments among a population of small ones.
Accordingly, I experimented with test data built this way:

create table seg_test as
  select (a || ' .. ' || a + 0.25*b)::seg as a from
  (select random() as a, random() as b from generate_series(1,10)) x
  union all
  select (a || ' .. ' || a + 0.5*b)::seg as a from
  (select random() as a, random() as b from generate_series(1,100)) x;

(Note: order is important here, and it's also important to set
synchronize_seqscans off, else you won't get repeatable results.
We want to inject the large segments early in the index build process.)

What I got for this was

index build timeindex pages search buffers

fixed HEAD run A114 sec 1   16
fixed HEAD run B113 sec 16574   6
Alexander's 0.5 run A   15.5 sec625934
Alexander's 0.5 run B   16 sec  60163

("Search buffers" is the number of buffers touched in
select * from seg_test where a @> '0.5 .. 0.5'::seg
Run A and run B are two different data sets generated as per the
above recipe)

Unsurprisingly, the patch wins on index size, but its variance in
search efficiency seems a little worse than before.  Note however that
the search-buffers number is still a lot better than unpatched HEAD,
where I was seeing values of several hundred.

4. The main speed problem with Guttman's algorithm is that the initial
seed-finding loop is O(N^2) in the number of items on the page to be split
(which is ~260, on my machine anyway).  This is why Yeb found
significantly shorter index build time with 1K instead of 8K pages.
However, given the 1-D nature of the data it's not hard to think of
cheaper ways to select the seed items --- basically we want the two
"extremal" items.  I experimented with finding the smallest-lower and
largest-upper items, and also the smallest-upper and largest-lower,
which of course can be done in one pass with O(N) time.  Leaving the
second pass as-is, I got

index build timeindex pages search buffers

smallest_upper run A34 sec  16049   7
smallest_upper run B33 sec  15185   5
smallest_lower run A15 sec  732740
smallest_lower run B14 sec  72344

(I also tried smallest and largest center points, but that was worse than
these across the board.)  So there's more than one way to skin a cat here.

5. The *real* problem with Guttman's algorithm became obvious while I was
doing these experiments: it's unstable as heck.  If one of the seed items

Re: [HACKERS] BufFreelistLock

[Jim Nasby]

On Dec 15, 2010, at 2:40 PM, Jeff Janes wrote:
> On Tue, Dec 14, 2010 at 1:42 PM, Jim Nasby  wrote:
>> 
>> On Dec 14, 2010, at 11:08 AM, Jeff Janes wrote:
>>> I wouldn't expect an increase in shared_buffers to make contention on
>>> BufFreelistLock worse.  If the increased buffers are used to hold
>>> heavily-accessed data, then you will find the pages you want in
>>> shared_buffers more often, and so need to run the clock-sweep less
>>> often.  That should make up for longer sweeps.  But if the increased
>>> buffers are used to hold data that is just read once and thrown away,
>>> then the clock sweep shouldn't need to sweep very far before finding a
>>> candidate.
>> 
>> Well, we're talking about a working set that's between 96 and 192G, but
>> only 8G (or 28G) of shared buffers. So there's going to be a pretty
>> large amount of buffer replacement happening. We also have
>> 210 tables where the ratio of heap buffer hits to heap reads is
>> over 1000, so the stuff that is in shared buffers probably keeps
>> usage_count quite high. Put these two together, and we're probably
>> spending a fairly significant amount of time running the clock sweep.
> 
> The thing that makes me think the bottleneck is elsewhere is that
> increasing from 8G to 28G made it worse.  If buffer unpins are
> happening at about the same rate, then my gut feeling is that the
> clock sweep has to do about the same amount of decrementing before it
> gets to a free buffer under steady state conditions.  Whether it has
> to decrement 8G in buffers three and a half times each, or 28G of
> buffers one time each, it would do about the same amount of work.
> This is all hand waving, of course.

While we're waving hands... I think the issue is that our working set size is 
massive. That means that there will be a lot of activity driving usage_count up 
on buffers. Increasing shared buffers will help reduce that effect as they 
begin to contain more and more of the working set, but I suspect that going 
from 8G to 28G wouldn't have made much difference. That means that we now have 
*more* buffers with a high usage count that the sweep has to slog through.

Anyway, once I'm able to get the buffer stats contrib module installed we'll 
have a better idea of what's actually happening.

>> Even excluding our admittedly unusual workload, there is still significant 
>> overhead in running the clock sweep vs just grabbing something off of the 
>> free list (assuming we had separate locks for the two operations).
> 
> But do we actually know that?  Doing a clock sweep is only a lot of
> overhead if it has to pass over many buffers in order to find a good
> one, and we don't know the numbers on that.  I think you can sweep a
> lot of buffers for the overhead of a single contended lock.
> 
> If the sweep and the freelist had separate locks, you still need to
> lock the freelist to add to it things discovered during the sweep.

I'm hoping we could actually use separate locks for adding and removing, 
assuming we discover this is actually a consideration.

>> Does anyone know what the overhead of getting a block from the filesystem 
>> cache is?
> 
> I did tests on this a few days ago.  It took on average 20
> microseconds per row to select one row via primary key when everything
> was in shared buffers.
> When everything was in RAM but not shared buffers, it took 40
> microseconds.  Of this, about 10 microseconds were the kernel calls to
> seek and read from OS cache to shared_buffers, and the other 10
> microseconds is some kind of PG overhead, I don't know where.  The
> timings are per select, not per page, and one select usually reads two
> pages, one for the index leaf and one for the table.
> 
> This was all single-client usage on 2.8GHz AMD Opteron.  Not all the
> components of the timings will scale equally with additional clients
> on additional CPUs of course.  I think the time spent in the kernel
> calls to do the seek and read will scale better than most other parts.

Interesting info. I wonder if that 10us of unknown overhead was related to 
shared buffers. Do you know if you had room in shared buffers when you ran that 
test? It would be interesting to see the differences between having buffers on 
the free list, no buffers on the free list but buffers with 0 usage count 
(though, I'm not sure how you could set that up), and shared buffers with high 
usage count.

>> BTW, given our workload I can't see any way of running at debug2 without 
>> having a large impact on performance.
> 
> As long as you are adding #define BGW_DEBUG and recompiling, you might
> as well promote all the DEBUG2 in src/backend/storage/buffer/bufmgr.c
> to DEBUG1 or LOG.  I think this will only generate a couple log
> message per bgwriter_delay.  That should be tolerable, especially for
> testing purposes.

Good ideas; I'll try to get that in place once we can benchmark, though it'll 
be easier to get pg_buffercache in place, so I'll focus on that first.
--
Jim C. Nasby

Re: [HACKERS] range intervals in window function frames

[Alvaro Herrera]

Excerpts from Daniel Popowich's message of mié dic 15 15:02:05 -0300 2010:

>   1) Is there active work on window functions with frames over
>  interval ranges?

Yeah, we had a patch for that but it was rejected; only ROWS was
implemented.  RANGE needed more work; not sure if the patch author is
working on it.

>   2) If not, how can I help with that?

I think you should ping Hitoshi Harada directly.

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] Default mode for shutdown

[Magnus Hagander]

On Wed, Dec 15, 2010 at 16:11, Alvaro Herrera
 wrote:
> Excerpts from Robert Haas's message of mié dic 15 12:03:06 -0300 2010:
>
>> Certainly, if you have an environment where people are mostly logging
>> into the database directly (not through a connection pooler) and they
>> do a few important queries and then disconnect, smart is a better
>> default.  But if you have an environment where (for whatever reason)
>> long-lasting connections are common, smart is worse than useless.
>
> It occurs to me that we may need a new mode, which disconnects sessions
> that are not in a transaction (or as soon as they are) but leaves
> in-progress transactions alone; this could be the new default.  Of
> course, this is much more difficult to implement than the current modes.

Now that, however, would actually be a useful behavior... Where's your patch? ;)

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.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] Default mode for shutdown

[Magnus Hagander]

On Wed, Dec 15, 2010 at 15:47, Tom Lane  wrote:
> Robert Haas  writes:
>> On Wed, Dec 15, 2010 at 9:39 AM, Tom Lane  wrote:
>>> Really?  Personally I'm quite happy with that default.
>
>> Why?  It seems to me that just leads to, oh, gee, the database isn't
>> shutting down, where's the window where I failed to exit a session?
>
> Yeah, and more to the point, do I want to finish whatever I was doing in
> that window?  Fast-by-default is a nice hammer to swing, but one day
> you'll pound your finger.

The whole question "whatever I was doing in that window" indicates a
very limited deployment.

In most production deployments, that would mean different machiens,
and many different people... As a DBA, I certainly don't want to have
to wait around for everybody in my organization to get back from lunch
and close their clients..

In reality, more often than not I see the default shutdown turn into a
very efficient DOS - nobody can do anything in the database ,and a
restart (which is the usual case really - most people don't shut down
their db, they restart it - or shutdown/upgrade/start or something
like that) that could go in seconds turns into minutes or longer.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.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] hstores in pl/python

[Florian Pflug]

On Dec15, 2010, at 16:34 , Jan Urbański wrote:
> On 15/12/10 16:25, Dmitriy Igrishin wrote:
>> 2010/12/15 Jan Urbański 
>>> So how about just adding a text column to pg_type and a IDENTIFIER
>>> keywork to CREATE TYPE. It's not guaranteed to be unique, but isn't it
>>> pushing the argument to the extreme? Someone can change around bool and
>>> text type oids, too... And then hstore_plpython looks up the well-known
>>> identifier, sets up a RVV with the OID and everyone's happy.
>>> 
>> How IDENTIFIER differs from name ? org.postgresql.hstore vs hstore ?
> 
> Hm, now that I think of it, the only real difference is that you don't
> use search_path to look it up. So public.hstore is just as good an
> identifier...

Not if CREATE EXTENSION allows you install hstore into an arbitrary schema.
For pl/python's purposes, requiring the DBA to set plpython_hstore_type
accordingly might work, but clients need to be able to reliably find hstore
too. For them, having to specify the schema of every non-core type your
database adapter might support isn't exactly ideal...

best regards,
Florian Pflug


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

Re: [HACKERS] hstores in pl/python

[Pavel Stehule]

2010/12/15 Dmitriy Igrishin :
>
>
> 2010/12/15 Florian Pflug 
>>
>> On Dec15, 2010, at 18:33 , Dmitriy Igrishin wrote:
>> > 2010/12/15 Florian Pflug 
>> > On Dec15, 2010, at 16:18 , Dmitriy Igrishin wrote:
>> > >> 2010/12/15 Florian Pflug 
>> > >> On Dec15, 2010, at 02:14 , James William Pye wrote:
>> > >> > On Dec 13, 2010, at 6:16 PM, Tom Lane wrote:
>> > >> >> how do you identify which type OID is really hstore?
>> > >> >
>> > >> > How about an identification field on pg_type?
>> > >> >
>> > >> > CREATE TYPE hstore ..., IDENTIFIER 'org.postgresql.hstore';
>> > >> > -- Where the "identifier" is an arbitrary string.
>> > >>
>> > >> I've wanted something like this a few times when dealing
>> > >> with custom types within a client. A future protocol version
>> > >> might even transmit these identifiers instead a the type's OID,
>> > >> thereby removing the dependency on OID from clients entirely.
>> > >
>> > > In some another tread I've proposed CREATE TYPE ... WITH OID...
>> > Yeah, and I believe type identifiers are probably what you were
>> > really looking for ;-)
>> > Indeed, but why OID cannot serve as identifier in this case ? Why to
>> > encode the code ? :-)
>> Because there are only 2^32 OIDs, so if people start picking them at
>> random, sooner or later there will be collisions.
>
> Yes, but range of PostgreSQL's OIDs can be reserved. One or even ten
> millions, e.g. can be enough.
>
>>
>> > Type identifiers would solve
>> > this, by providing an easy and unambiguous way to find specific types.
>> > Agree with 1st assertion but disagree with 2nd. If I understand
>> > correctly,
>> > "identifier" is a second name for type (object), but Java-styled, right
>> > ?
>> > It probably does solve the problem if there are will be convention that
>> > types org.postgresql.* are reserved.
>> Yeah, that'd be the idea. If everyone uses reversed DNS-style names, and
>> everyone picks a name belonging to a DNS zone under his control, there
>> cannot be any collisions. At least for java packages, this seems to work
>> pretty nicely.
>>
>> > But why not reserve name of type
>> > "hstore" and prevent the user to create type with this reserved name ?
>> > All this tells me one thing - to avoid conflicts of naming of specific
>> > types
>> > it is necessary to make them built-in.
>> None of these solutions scale well.
>
> Well, If there are will be identifiers for each type, e.g.
> org.postgresql.integer, why
> they need to be built-in ? For "historical reasons" ? :-)
> Let them also be in contribs...

some types are used in system tables, so without support of these
types, then you can't to add a new types. It's a egg-chicken problem

Pavel

>>
>> best regards,
>> Florian Pflug
>>
>>
>
>
>
> --
> // Dmitriy.
>
>
>

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

Re: [HACKERS] hstores in pl/python

[Dmitriy Igrishin]

2010/12/15 Robert Haas 

> On Wed, Dec 15, 2010 at 2:14 PM, Robert Haas 
> wrote:
> > On Wed, Dec 15, 2010 at 1:22 PM, Dmitriy Igrishin 
> wrote:
> >> Yes, but range of PostgreSQL's OIDs can be reserved. One or even ten
> >> millions, e.g. can be enough.
> >
> > No, they can't.  PostgreSQL is already deployed without any such
> > restriction.  You can "reserve" those OIDs because they may already be
> > in use on any given system.
>
> Err, you CAN'T reserve these OIDs because blah blah.
>
Right.
Proposed identifiers wins in this case.

>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>



-- 
// Dmitriy.

Re: [HACKERS] pg_execute_from_file, patch v10

[Itagaki Takahiro]

On Wed, Dec 15, 2010 at 12:55, Robert Haas  wrote:
>>> It seems like pg_read_binary_file() is good to have regardless of
>>> whatever else we decide to do here.  Should we pull that part out and
>>> commit it separately?
>>
> The whole-file versions seem like a good idea - my only hesitation is,
> I'm not sure why we didn't include that functionality originally.  It
> seems obviously useful, so does that mean that it was omitted on
> purpose for some reason?

I applied the attached patch extracted from Dimitri's work.
One difference is 'offset' argument is removed from 'whole' mode.
So, we'll have (path, offset, length) and (path) versions.

Checking with convert_and_check_filename is left as-is.

-- 
Itagaki Takahiro


pg_read_binary_file.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] hstores in pl/python

[Dmitriy Igrishin]

2010/12/16 Florian Pflug 

> On Dec15, 2010, at 21:28 , Dimitri Fontaine wrote:
> > Florian Pflug  writes:
> >> Not if CREATE EXTENSION allows you install hstore into an arbitrary
> schema.
> >
> > It also allows you to change it after the fact, and to easily track it
> > down. Here's an updated version of the query to find the hstore type OID
> > reliably once we have extensions in:
> >
> > 
> That's certainly cool, but having to use different methods to find a type
> depending on how it has been installed isn't exactly ideal. And not every
> user-defined type will be installed via CREATE EXTENSION.
>
> Thus I still believe something like a type identifier that is independent
> from
> it's name and schema would be nice to have.
>
> In case you wonder if than means every object should have such a handle -
> they should *not*. What makes types special is that they are used on
> the protocol level, not only on the SQL level. Thus, handling them
> frequently falls into the real of client libraries, not individual
> client applications, making it more important to be able to handle them
> in an application-agnostic way.
>
Right! In particular this is one reason why I believe that hstore should be
built-in type.

>
> best regards,
> Florian Pflug
>
>


-- 
// Dmitriy.

Re: [HACKERS] hstores in pl/python

[Florian Pflug]

On Dec15, 2010, at 21:28 , Dimitri Fontaine wrote:
> Florian Pflug  writes:
>> Not if CREATE EXTENSION allows you install hstore into an arbitrary schema.
> 
> It also allows you to change it after the fact, and to easily track it
> down. Here's an updated version of the query to find the hstore type OID
> reliably once we have extensions in:
> 
> 
That's certainly cool, but having to use different methods to find a type
depending on how it has been installed isn't exactly ideal. And not every
user-defined type will be installed via CREATE EXTENSION.

Thus I still believe something like a type identifier that is independent from
it's name and schema would be nice to have.

In case you wonder if than means every object should have such a handle -
they should *not*. What makes types special is that they are used on
the protocol level, not only on the SQL level. Thus, handling them
frequently falls into the real of client libraries, not individual
client applications, making it more important to be able to handle them
in an application-agnostic way.

best regards,
Florian Pflug


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

Re: [HACKERS] Complier warnings on mingw gcc 4.5.0

[Andrew Dunstan]

On 12/15/2010 04:26 PM, Tom Lane wrote:

Anyway seems like the next step is to compare the
Fedora getopt code with mingw's ...




Mingw code attached.

cheers

andrew
#ifndef __GETOPT_H__
/* 
 * getopt.h
 *
 * $Id: getopt.h,v 1.4 2009/01/04 17:35:36 keithmarshall Exp $
 *
 * Defines constants and function prototypes required to implement
 * the `getopt', `getopt_long' and `getopt_long_only' APIs.
 *
 * This file is part of the MinGW32 package set.
 *
 * Contributed by Keith Marshall 
 *
 *
 * THIS SOFTWARE IS NOT COPYRIGHTED
 *
 * This source code is offered for use in the public domain. You may
 * use, modify or distribute it freely.
 *
 * This code is distributed in the hope that it will be useful but
 * WITHOUT ANY WARRANTY. ALL WARRANTIES, EXPRESS OR IMPLIED ARE HEREBY
 * DISCLAIMED. This includes but is not limited to warranties of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
 *
 * $Revision: 1.4 $
 * $Author: keithmarshall $
 * $Date: 2009/01/04 17:35:36 $
 *
 */
#define __GETOPT_H__

/* All the headers include this file. */
#include <_mingw.h>

#ifdef __cplusplus
extern "C" {
#endif

extern int optind;  /* index of first non-option in argv  */
extern int optopt;  /* single option character, as parsed */
extern int opterr;  /* flag to enable built-in diagnostics... */
/* (user may set to zero, to suppress)*/

extern char *optarg;/* pointer to argument of current option  */

extern int getopt( int, char * const [], const char * );

#ifdef _BSD_SOURCE
/*
 * BSD adds the non-standard `optreset' feature, for reinitialisation
 * of `getopt' parsing.  We support this feature, for applications which
 * proclaim their BSD heritage, before including this header; however,
 * to maintain portability, developers are advised to avoid it.
 */
# define optreset  __mingw_optreset

extern int optreset;
#endif
#ifdef __cplusplus
}
#endif
/*
 * POSIX requires the `getopt' API to be specified in `unistd.h';
 * thus, `unistd.h' includes this header.  However, we do not want
 * to expose the `getopt_long' or `getopt_long_only' APIs, when
 * included in this manner.  Thus, close the standard __GETOPT_H__
 * declarations block, and open an additional __GETOPT_LONG_H__
 * specific block, only when *not* __UNISTD_H_SOURCED__, in which
 * to declare the extended API.
 */
#endif /* !defined(__GETOPT_H__) */
#if !defined(__UNISTD_H_SOURCED__) && !defined(__GETOPT_LONG_H__)
#define __GETOPT_LONG_H__

#ifdef __cplusplus
extern "C" {
#endif

struct option   /* specification for a long form option...  */
{
  const char *name; /* option name, without leading hyphens */
  int has_arg;  /* does it take an argument?*/
  int*flag; /* where to save its status, or NULL*/
  int val;  /* its associated status value  */
};

enum/* permitted values for its `has_arg' field...  */
{
  no_argument = 0,  /* option never takes an argument   */
  required_argument,/* option always requires an argument   */
  optional_argument /* option may take an argument  */
};

extern int getopt_long( int, char * const [], const char *, const struct option 
*, int * );
extern int getopt_long_only( int, char * const [], const char *, const struct 
option *, int * );
/*
 * Previous MinGW implementation had...
 */
#ifndef HAVE_DECL_GETOPT
/*
 * ...for the long form API only; keep this for compatibility.
 */
# define HAVE_DECL_GETOPT   1
#endif

#ifdef __cplusplus
}
#endif

#endif /* !defined(__UNISTD_H_SOURCED__) && !defined(__GETOPT_LONG_H__) */
/* $RCSfile: getopt.h,v $Revision: 1.4 $: end of file */
/* 
 * getopt.c
 *
 * $Id: getopt.c,v 1.9 2009/02/08 18:02:17 keithmarshall Exp $
 *
 * Implementation of the `getopt', `getopt_long' and `getopt_long_only'
 * APIs, for inclusion in the MinGW runtime library.
 *
 * This file is part of the MinGW32 package set.
 *
 * Contributed by Keith Marshall 
 *
 *
 * THIS SOFTWARE IS NOT COPYRIGHTED
 *
 * This source code is offered for use in the public domain. You may
 * use, modify or distribute it freely.
 *
 * This code is distributed in the hope that it will be useful but
 * WITHOUT ANY WARRANTY. ALL WARRANTIES, EXPRESS OR IMPLIED ARE HEREBY
 * DISCLAIMED. This includes but is not limited to warranties of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
 *
 * $Revision: 1.9 $
 * $Author: keithmarshall $
 * $Date: 2009/02/08 18:02:17 $
 *
 */

#include 
#include 
#include 
#include 

/* Identify how to get the calling program name, for use in messages...
 */
#ifdef __CYGWIN__
/*
 * CYGWIN uses this DLL reference...
 */
# define PROGNAME  __progname
extern char __declspec(dllimport) *__progname;
#else
/*
 * ...while elsewhere, we simply use the first argument passed.
 */
# define PROGNAME  *argv
#endif

/* Initialise

Re: [HACKERS] mvcc & DML on the same row

[Nicolas Barbier]

2010/12/15 matteo durighetto :

> But why we need all these versions of the same row on table, if for
> rollback we need only the original row X (X0) ?

And the "previous" value of row X during the execution of a statement
(because statements don't see their own changes, think INSERT INTO a
SELECT * FROM a). And any values that we might need to ROLLBACK TO
SAVEPOINT to.

> So I think we need it in memory, not on physical space of table (ok
> there is the cache, but ..) or something similar

It must be possible to push out those changes from memory to disk
anyway, because there is no limit on how many rows a transaction can
update in PostgreSQL (vs. Oracle's "snapshot too old" problems). But
then, keeping the locally updated rows in some kind of special
per-transaction cache or in the global page cache isn't that
different.

Also, updating the same row many times in one transaction is probably
not regarded a very typical use case.

Note that other DBMSs may implement MVCC more along the lines you
specified; AFAIR, InnoDB uses such an approach. This may mean that
they don't need VACUUM.

I think that the consensus is that there is a trade-off between doing
VACUUM-like things synchronously, or having the possibility to do it
asynchronously at times when load is low. In PostgreSQL, the latter
was chosen.

Btw, this topic has been discussed at length in the past, please check
the archive.

> or this method is for transaction with isolation level at "read
> uncommited"?

PostgreSQL implements READ UNCOMMITTED as READ COMMITTED (providing a
higher level of isolation than requested is allowed by the standard),
so that is definitely not the reason.

Nicolas

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

Re: [HACKERS] mvcc & DML on the same row

[Robert Haas]

On Wed, Dec 15, 2010 at 2:50 PM, matteo durighetto
 wrote:
> Hi,
>    I have an idea about mvcc and different DML of the same row in the
> same transaction.
> Normally when a backend do an unpdate on a row ( call it X ) , we done
> an insert and logical delete on this row  (0,1,2..N are the "version
> of the row) :
>
>   X0  (delete "old" row)
>   X1  (insert  "new" row)
>
> if  we continue the transaction and we do for example another update
> on this row (X) , we again redo the same operation:
>
>   X0  (deleted "old" row)
>   X1  (row inserted, NOW deleted) => not needed for rollback
>   X2  (insert "new" row  )
>
>
> But why we need all these versions of the same row on table, if for
> rollback we need only the original row X (X0) ?

The fact that we can't get rid of X1 until after the transaction
commits is an implementation limitation.  But you obviously need both
X0 and X2, because the transaction might either commit or abort.

> So I think we need it in memory, not on physical space of table (ok
> there is the cache, but ..) or something similar, or this method is
> for transaction with isolation level at "read uncommited"?

I can't figure out exactly what this part is talking about.  It's
completely impractical to add rows to a table without writing them
into shared buffers, which means they will eventually get flushed to
disk if not vacuumed, dropped, etc. first.  We don't support read
uncommitted anyway (well, we do, but it's really still snapshot
isolation).

-- 
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] Complier warnings on mingw gcc 4.5.0

[Tom Lane]

Andrew Dunstan  writes:
> On my Fedora box, man 3 getopt says this:

> A program that scans multiple argument vectors, or rescans the same
> vector more than once, and wants to make use of GNU extensions such
> as '+'  and '-'  at  the start of optstring, or changes the value of
> POSIXLY_CORRECT between scans, must reinitialize getopt() by
> resetting optind to 0, rather than the traditional value of 1. 
> (Resetting to 0 forces the invocation of an internal initialization
> routine that rechecks POSIXLY_CORRECT and checks for GNU extensions
> in optstring.)

Hmm, mine says the same, but it's not entirely clear how to parse the
AND and OR conditions there.  The fact that it works on Fedora suggests
to me that the "multiple vectors" case is somehow ANDed with one of the
other conditions.  Anyway seems like the next step is to compare the
Fedora getopt code with mingw's ...

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] Complier warnings on mingw gcc 4.5.0

[Andrew Dunstan]

On 12/15/2010 03:52 PM, Tom Lane wrote:

Andrew Dunstan  writes:

And here is where it changed:

 * A replacement implementation for the getopt() family of functions,
adding support for the GNU getopt_long_only() function.  Users
should note that this intentionally *removes* support for the BSD
or Mac OS-X specific, and non-standard, `optreset' global variable;
to reset the getopt() scanner, use `optind = 0;' instead of relying
on this non-standard, non-portable and now-unsupported feature.

Great.  So instead of a nonstandard but pretty portable API, they
decided on a nonstandard interpretation of optind ... which absolutely
will not work for our usage, because we need to be able to tell getopt
to skip over --single, even if we were willing to figure out whether
getopt behaves this way or the more usual way.  Dolts.

While I don't mind forcing use of our getopt() on mingw, I'm a mite
concerned by the idea that this might represent an upstream change we'll
soon see elsewhere, rather than just mingw-specific brain damage.
Anybody know?




On my Fedora box, man 3 getopt says this:

   A program that scans multiple argument vectors, or rescans the same
   vector more than once, and wants to make use of GNU extensions such
   as '+'  and '-'  at  the start of optstring, or changes the value of
   POSIXLY_CORRECT between scans, must reinitialize getopt() by
   resetting optind to 0, rather than the traditional value of 1. 
   (Resetting to 0 forces the invocation of an internal initialization

   routine that rechecks POSIXLY_CORRECT and checks for GNU extensions
   in optstring.)

Modulo the --single issue, we don't have to force use of our getopt on 
Mingw. This patch seems to work, at least to get regression working:


   diff --git a/src/backend/postmaster/postmaster.c
   b/src/backend/postmaster/postmaster.c
   index 90854f4..9ae3767 100644
   --- a/src/backend/postmaster/postmaster.c
   +++ b/src/backend/postmaster/postmaster.c
   @@ -753,6 +753,8 @@ PostmasterMain(int argc, char *argv[])
 optind = 1;
 #ifdef HAVE_INT_OPTRESET
 optreset = 1;/* some systems need this too */
   +#elsif defined (WIN32) &&  !defined(_MSC_VER)
   +optind = 0; /* modern Mingw needs this instead */
 #endif

 /* For debugging: display postmaster environment */
   diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
   index ff2e9bd..ea4ae79 100644
   --- a/src/backend/tcop/postgres.c
   +++ b/src/backend/tcop/postgres.c
   @@ -3444,6 +3444,8 @@ process_postgres_switches(int argc, char
   *argv[], GucContext ctx)
 optind = 1;
 #ifdef HAVE_INT_OPTRESET
 optreset = 1;/* some systems need this too */
   +#elsif defined (WIN32) &&  !defined(_MSC_VER)
   +optind = 0; /* modern Mingw
   needs this instead */
 #endif

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] Complier warnings on mingw gcc 4.5.0

[Tom Lane]

Andrew Dunstan  writes:
> And here is where it changed: 
> 

> * A replacement implementation for the getopt() family of functions,
>adding support for the GNU getopt_long_only() function.  Users
>should note that this intentionally *removes* support for the BSD
>or Mac OS-X specific, and non-standard, `optreset' global variable;
>to reset the getopt() scanner, use `optind = 0;' instead of relying
>on this non-standard, non-portable and now-unsupported feature.

Great.  So instead of a nonstandard but pretty portable API, they
decided on a nonstandard interpretation of optind ... which absolutely
will not work for our usage, because we need to be able to tell getopt
to skip over --single, even if we were willing to figure out whether
getopt behaves this way or the more usual way.  Dolts.

While I don't mind forcing use of our getopt() on mingw, I'm a mite
concerned by the idea that this might represent an upstream change we'll
soon see elsewhere, rather than just mingw-specific brain damage.
Anybody know?

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] BufFreelistLock

[Jeff Janes]

On Tue, Dec 14, 2010 at 1:42 PM, Jim Nasby  wrote:
>
> On Dec 14, 2010, at 11:08 AM, Jeff Janes wrote:
>

>> I wouldn't expect an increase in shared_buffers to make contention on
>> BufFreelistLock worse.  If the increased buffers are used to hold
>> heavily-accessed data, then you will find the pages you want in
>> shared_buffers more often, and so need to run the clock-sweep less
>> often.  That should make up for longer sweeps.  But if the increased
>> buffers are used to hold data that is just read once and thrown away,
>> then the clock sweep shouldn't need to sweep very far before finding a
>> candidate.
>
> Well, we're talking about a working set that's between 96 and 192G, but
> only 8G (or 28G) of shared buffers. So there's going to be a pretty
> large amount of buffer replacement happening. We also have
> 210 tables where the ratio of heap buffer hits to heap reads is
> over 1000, so the stuff that is in shared buffers probably keeps
> usage_count quite high. Put these two together, and we're probably
> spending a fairly significant amount of time running the clock sweep.

The thing that makes me think the bottleneck is elsewhere is that
increasing from 8G to 28G made it worse.  If buffer unpins are
happening at about the same rate, then my gut feeling is that the
clock sweep has to do about the same amount of decrementing before it
gets to a free buffer under steady state conditions.  Whether it has
to decrement 8G in buffers three and a half times each, or 28G of
buffers one time each, it would do about the same amount of work.
This is all hand waving, of course.


> Even excluding our admittedly unusual workload, there is still significant 
> overhead in running the clock sweep vs just grabbing something off of the 
> free list (assuming we had separate locks for the two operations).

But do we actually know that?  Doing a clock sweep is only a lot of
overhead if it has to pass over many buffers in order to find a good
one, and we don't know the numbers on that.  I think you can sweep a
lot of buffers for the overhead of a single contended lock.

If the sweep and the freelist had separate locks, you still need to
lock the freelist to add to it things discovered during the sweep.


> Does anyone know what the overhead of getting a block from the filesystem 
> cache is?

I did tests on this a few days ago.  It took on average 20
microseconds per row to select one row via primary key when everything
was in shared buffers.
When everything was in RAM but not shared buffers, it took 40
microseconds.  Of this, about 10 microseconds were the kernel calls to
seek and read from OS cache to shared_buffers, and the other 10
microseconds is some kind of PG overhead, I don't know where.  The
timings are per select, not per page, and one select usually reads two
pages, one for the index leaf and one for the table.

This was all single-client usage on 2.8GHz AMD Opteron.  Not all the
components of the timings will scale equally with additional clients
on additional CPUs of course.  I think the time spent in the kernel
calls to do the seek and read will scale better than most other parts.


> BTW, given our workload I can't see any way of running at debug2 without 
> having a large impact on performance.

As long as you are adding #define BGW_DEBUG and recompiling, you might
as well promote all the DEBUG2 in src/backend/storage/buffer/bufmgr.c
to DEBUG1 or LOG.  I think this will only generate a couple log
message per bgwriter_delay.  That should be tolerable, especially for
testing purposes.

Cheers,

Jeff

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

Re: [HACKERS] hstores in pl/python

[Dimitri Fontaine]

Florian Pflug  writes:
> Not if CREATE EXTENSION allows you install hstore into an arbitrary schema.

It also allows you to change it after the fact, and to easily track it
down. Here's an updated version of the query to find the hstore type OID
reliably once we have extensions in:

dim=# SELECT t.oid
FROM pg_extension_objects('hstore') o
 JOIN pg_type t ON t.oid = o.objid 
   AND o.classid = 'pg_type'::regclass 
   WHERE t.typname = 'hstore';
  oid  
---
 16393
(1 row)

For listing all the hstore objects interactively, use \dx hstore.

> For pl/python's purposes, requiring the DBA to set plpython_hstore_type
> accordingly might work, but clients need to be able to reliably find hstore
> too. For them, having to specify the schema of every non-core type your
> database adapter might support isn't exactly ideal...

Another reason why you will like the extension's patch :)

If you think you need the schema where the extension's objects are
living, there it is (for interactive use, just issue \dx):

=# SELECT n.nspname, e.extname
 FROM pg_catalog.pg_extension e
  LEFT JOIN pg_catalog.pg_depend d ON d.objid = e.oid 
AND d.refclassid = 'pg_catalog.pg_namespace'::regclass 
  LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.refobjid 
WHERE extname = 'hstore';
 nspname | extname 
-+-
 utils   | hstore
(1 row)


-- 
Dimitri Fontaine
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] Complier warnings on mingw gcc 4.5.0

[Andrew Dunstan]

On 12/15/2010 02:22 PM, Andrew Dunstan wrote:



On 12/15/2010 02:06 PM, Tom Lane wrote:

Andrew Dunstan  writes:

And the attached hack allowed "make check" to succeed.
I think the logic in tcop/postgres.c and postmaster/postmaster.c is
probably wrong. If we are using our getopt/getopt_long, we want to be
setting optreset, whether or not configure found one in the system
libraries.

Yeah, that's what I suggested earlier; but if your build *wasn't* using
our versions before, we're still no closer to understanding why it was
failing then.  Another small problem is that a close inspection of our
getopt.c says that it does reset "place" to point at a constant before
returning -1, in every path except the "--" case which I doubt is being
invoked.  So my idea that we were clobbering argv underneath it doesn't
seem to hold up.  I'm still feeling that we don't understand what's
happening.




Sure we are closer to understanding it. It seems quite clear to me 
that Mingw's getopt, which we have been using, has changed between 
versions, as indicated by the fact that on my mingw optreset is not 
found, but on narwhal it is found.


And here is where it changed: 



   * A replacement implementation for the getopt() family of functions,
  adding support for the GNU getopt_long_only() function.  Users
  should note that this intentionally *removes* support for the BSD
  or Mac OS-X specific, and non-standard, `optreset' global variable;
  to reset the getopt() scanner, use `optind = 0;' instead of relying
  on this non-standard, non-portable and now-unsupported feature.


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] hstores in pl/python

[Peter Eisentraut]

On tis, 2010-12-14 at 11:52 -0500, Tom Lane wrote:
> Peter Eisentraut  writes:
> > On mån, 2010-12-13 at 08:50 +0100, Jan Urbański wrote:
> >> It would be cool to be able to transparently use hstores as Python
> >> dictionaries and vice versa. It would be easy enough with hstore as a
> >> core type, but with hstore as an addon it's not that easy.
> 
> > I have been thinking about this class of problems for a while.  I think
> > the proper fix is to have a user-definable mapping between types and
> > languages.  It would be another pair of input/output functions,
> > essentially.
> 
> Interesting thought, but it still leaves you needing to solve the
> problem of interconnecting two optional addons ...

First you create the language and the type (in any order), and then you
create an additional SQL-level designation that connects the two.

In fact, the SQL standard contains something very similar for connecting
user-defined types to host languages.  So adapting that syntax a little,
it could work like this:

CREATE LANGUAGE plpython;

CREATE FUNCTION ...
...
CREATE TYPE hstore ...;

CREATE FUNCTION hstore_to_plpython(hstore) RETURNS internal ...
CREATE FUNCTION plpython_to_hstore(internal) RETURNS plpython ...

CREATE TRANSFORMS FOR hstore (TO plpython WITH hstore_to_plpython, FROM
plpython WITH plpython_to_hstore);


A shorter term solution that avoids creating a whole lot of SQL
infrastructure might be to write out the same transform specification
using a configuration variable, for example

plpython.transforms = 
'hstore:public.hstore_to_plpython:public.plpython_to_hstore,...'



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

Re: [HACKERS] Segfault related to pg_authid when running initdb from git master

[Peter Geoghegan]

On 15 December 2010 19:43, Robert Haas  wrote:
> Gah.  I assumed you had cleaned out your tree.  Oh, well.
>
> If you don't use --enable-depend, you can get this kind of issue.
> Even if you do, it's worth trying a full clean out (I use git clean
> -dfx) if you get something weird.

Thanks for the tip. I guess it simply didn't occur to me to "make
distclean" because I made the rather questionable assumption that it's
only necessary when there are weird linking issues.


-- 
Regards,
Peter Geoghegan

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

Re: [HACKERS] Hot Standby: too many KnownAssignedXids

[Joachim Wieland]

On Tue, Dec 7, 2010 at 3:42 AM, Heikki Linnakangas
 wrote:
> Ok, I've committed this patch now.

I can confirm that I could continue replaying the logfiles on the
standby host with this patch.


Thanks a lot,
Joachim

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

Re: [HACKERS] Segfault related to pg_authid when running initdb from git master

[Alvaro Herrera]

Excerpts from Peter Geoghegan's message of mié dic 15 16:40:41 -0300 2010:
> > Before that, have you tried the old standby of "make distclean" and a
> > full rebuild/reinstall?  The lack of buildfarm confirmation makes me
> > highly suspicious that there's any real problem.
> 
> That's fixed both problems. I should have tried it much sooner. I
> guess that even though the binaries built were new, they were somehow
> linked with one or more older, "release" object files. Thanks.

This is probably caused by failure to use the --enable-depend configure
switch.  I think we should try to make that the default on platforms
that support it.  It seems silly not to use it.

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

[HACKERS] mvcc & DML on the same row

[matteo durighetto]

Hi,
I have an idea about mvcc and different DML of the same row in the
same transaction.
Normally when a backend do an unpdate on a row ( call it X ) , we done
an insert and logical delete on this row  (0,1,2..N are the "version
of the row) :

   X0  (delete "old" row)
   X1  (insert  "new" row)

if  we continue the transaction and we do for example another update
on this row (X) , we again redo the same operation:

   X0  (deleted "old" row)
   X1  (row inserted, NOW deleted) => not needed for rollback
   X2  (insert "new" row  )


But why we need all these versions of the same row on table, if for
rollback we need only the original row X (X0) ?

So I think we need it in memory, not on physical space of table (ok
there is the cache, but ..) or something similar, or this method is
for transaction with isolation level at "read uncommited"?

Kind Regards

Matteo Durighetto

---

desmodem...@gmail.com
m.durighe...@miriade.it

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

Re: [HACKERS] Segfault related to pg_authid when running initdb from git master

[Robert Haas]

On Wed, Dec 15, 2010 at 2:40 PM, Peter Geoghegan
 wrote:
>> Before that, have you tried the old standby of "make distclean" and a
>> full rebuild/reinstall?  The lack of buildfarm confirmation makes me
>> highly suspicious that there's any real problem.
>
> That's fixed both problems. I should have tried it much sooner. I
> guess that even though the binaries built were new, they were somehow
> linked with one or more older, "release" object files. Thanks.

Gah.  I assumed you had cleaned out your tree.  Oh, well.

If you don't use --enable-depend, you can get this kind of issue.
Even if you do, it's worth trying a full clean out (I use git clean
-dfx) if you get something weird.

-- 
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] Segfault related to pg_authid when running initdb from git master

[Peter Geoghegan]

> Before that, have you tried the old standby of "make distclean" and a
> full rebuild/reinstall?  The lack of buildfarm confirmation makes me
> highly suspicious that there's any real problem.

That's fixed both problems. I should have tried it much sooner. I
guess that even though the binaries built were new, they were somehow
linked with one or more older, "release" object files. Thanks.

-- 
Regards,
Peter Geoghegan

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

Re: [HACKERS] ALTER TABLE ... REPLACE WITH

[Kevin Grittner]

Heikki Linnakangas  wrote:
> On 14.12.2010 20:27, Simon Riggs wrote:
 
>> 1. Prepare new data into "new_table" and build indexes
>> 2. Swap old for new
>> BEGIN;
>> DROP TABLE "old_table";
>> ALTER TABLE "new_table" RENAME to "old_table";
>> COMMIT;
>>
>> Step (2) works, but any people queuing to access the table
>> will see ERROR: could not open relation with OID x
> 
> Could we make that work without error?
 
Well, that worked better for us than building up the new
contents in a temporary table and doing the sequence Tom
suggests, but to eliminate the above error we had to do:
 
BEGIN;
ALTER TABLE "old_table" RENAME TO "dead_table";
ALTER TABLE "new_table" RENAME TO "old_table";
COMMIT;
-- Wait for all references to old OID to expire.
DROP TABLE "dead_table";
 
We don't put foreign keys on the table we do this with;
it's rebuilt from the related tables weekly
 
-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] hstores in pl/python

[Dmitriy Igrishin]

2010/12/15 Dmitriy Igrishin 

>
>
> 2010/12/15 Pavel Stehule 
>
> 2010/12/15 Dmitriy Igrishin :
>> >
>> >
>> > 2010/12/15 Florian Pflug 
>> >>
>> >> On Dec15, 2010, at 18:33 , Dmitriy Igrishin wrote:
>> >> > 2010/12/15 Florian Pflug 
>> >> > On Dec15, 2010, at 16:18 , Dmitriy Igrishin wrote:
>> >> > >> 2010/12/15 Florian Pflug 
>> >> > >> On Dec15, 2010, at 02:14 , James William Pye wrote:
>> >> > >> > On Dec 13, 2010, at 6:16 PM, Tom Lane wrote:
>> >> > >> >> how do you identify which type OID is really hstore?
>> >> > >> >
>> >> > >> > How about an identification field on pg_type?
>> >> > >> >
>> >> > >> > CREATE TYPE hstore ..., IDENTIFIER 'org.postgresql.hstore';
>> >> > >> > -- Where the "identifier" is an arbitrary string.
>> >> > >>
>> >> > >> I've wanted something like this a few times when dealing
>> >> > >> with custom types within a client. A future protocol version
>> >> > >> might even transmit these identifiers instead a the type's OID,
>> >> > >> thereby removing the dependency on OID from clients entirely.
>> >> > >
>> >> > > In some another tread I've proposed CREATE TYPE ... WITH OID...
>> >> > Yeah, and I believe type identifiers are probably what you were
>> >> > really looking for ;-)
>> >> > Indeed, but why OID cannot serve as identifier in this case ? Why to
>> >> > encode the code ? :-)
>> >> Because there are only 2^32 OIDs, so if people start picking them at
>> >> random, sooner or later there will be collisions.
>> >
>> > Yes, but range of PostgreSQL's OIDs can be reserved. One or even ten
>> > millions, e.g. can be enough.
>> >
>> >>
>> >> > Type identifiers would solve
>> >> > this, by providing an easy and unambiguous way to find specific
>> types.
>> >> > Agree with 1st assertion but disagree with 2nd. If I understand
>> >> > correctly,
>> >> > "identifier" is a second name for type (object), but Java-styled,
>> right
>> >> > ?
>> >> > It probably does solve the problem if there are will be convention
>> that
>> >> > types org.postgresql.* are reserved.
>> >> Yeah, that'd be the idea. If everyone uses reversed DNS-style names,
>> and
>> >> everyone picks a name belonging to a DNS zone under his control, there
>> >> cannot be any collisions. At least for java packages, this seems to
>> work
>> >> pretty nicely.
>> >>
>> >> > But why not reserve name of type
>> >> > "hstore" and prevent the user to create type with this reserved name
>> ?
>> >> > All this tells me one thing - to avoid conflicts of naming of
>> specific
>> >> > types
>> >> > it is necessary to make them built-in.
>> >> None of these solutions scale well.
>> >
>> > Well, If there are will be identifiers for each type, e.g.
>> > org.postgresql.integer, why
>> > they need to be built-in ? For "historical reasons" ? :-)
>> > Let them also be in contribs...
>>
>> some types are used in system tables, so without support of these
>> types, then you can't to add a new types. It's a egg-chicken problem
>>
> So, the formal criterion to make the type built-in is "the type is must be
> primitive" ?
>
I.e. "the type for deploying system catalogs".

>
>> Pavel
>>
>> >>
>> >> best regards,
>> >> Florian Pflug
>> >>
>> >>
>> >
>> >
>> >
>> > --
>> > // Dmitriy.
>> >
>> >
>> >
>>
>
>
>
> --
> // Dmitriy.
>
>
>


-- 
// Dmitriy.

Re: [HACKERS] CommitFest wrap-up

[Tom Lane]

Robert Haas  writes:
> On Mon, Dec 13, 2010 at 12:37 PM, Robert Haas  wrote:
>> - fix for seg picksplit function - I don't have confidence this change
>> is for the best and can't take responsibility for it.  It needs review
>> by a committer who understands this stuff better than me and can
>> determine whether or not the change is really an improvement.

> Still outstanding.

I will take a look at that one --- it is a bug fix at bottom, so we
can't just drop it for lack of reviewers.

>> - Writeable CTEs - I think we need Tom to pick this one up.
>> - Fix snapshot taking inconsistencies - Ready for committer. Can any
>> committer pick this up?

Will take a look at these two also.

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] ALTER TABLE ... REPLACE WITH

[Robert Haas]

On Wed, Dec 15, 2010 at 10:50 AM, Simon Riggs  wrote:
> On Wed, 2010-12-15 at 12:17 +0100, Csaba Nagy wrote:
>
>> But one problem would be when the replaced table is the _parent_ for a
>> foreign key relationship. I don't think you can have that constraint
>> pre-verified on the replacement table and simply replacing the content
>> could leave the child relations with orphans.
>
> Good point.
>
> The only sensible way to handle this is by putting the FK checks into
> check pending state (as discussed on a different thread).
>
> We would probably need to disallow FKs with DELETE or UPDATE CASCADE
> since it would be difficult to execute those.

I'm still wondering if TRUNCATE CONCURRENTLY would be a more elegant solution.

-- 
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] Complier warnings on mingw gcc 4.5.0

[Andrew Dunstan]

On 12/15/2010 02:06 PM, Tom Lane wrote:

Andrew Dunstan  writes:

And the attached hack allowed "make check" to succeed.
I think the logic in tcop/postgres.c and postmaster/postmaster.c is
probably wrong. If we are using our getopt/getopt_long, we want to be
setting optreset, whether or not configure found one in the system
libraries.

Yeah, that's what I suggested earlier; but if your build *wasn't* using
our versions before, we're still no closer to understanding why it was
failing then.  Another small problem is that a close inspection of our
getopt.c says that it does reset "place" to point at a constant before
returning -1, in every path except the "--" case which I doubt is being
invoked.  So my idea that we were clobbering argv underneath it doesn't
seem to hold up.  I'm still feeling that we don't understand what's
happening.




Sure we are closer to understanding it. It seems quite clear to me that 
Mingw's getopt, which we have been using, has changed between versions, 
as indicated by the fact that on my mingw optreset is not found, but on 
narwhal it is found.


I haven't looked into our getopt.

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] hstores in pl/python

[Dmitriy Igrishin]

2010/12/15 Dmitriy Igrishin 

>
>
> 2010/12/15 Robert Haas 
>
> On Wed, Dec 15, 2010 at 2:14 PM, Robert Haas 
>> wrote:
>> > On Wed, Dec 15, 2010 at 1:22 PM, Dmitriy Igrishin 
>> wrote:
>> >> Yes, but range of PostgreSQL's OIDs can be reserved. One or even ten
>> >> millions, e.g. can be enough.
>> >
>> > No, they can't.  PostgreSQL is already deployed without any such
>> > restriction.  You can "reserve" those OIDs because they may already be
>> > in use on any given system.
>>
>> Err, you CAN'T reserve these OIDs because blah blah.
>>
> Right.
> Proposed identifiers wins in this case.
>
I mean Java-styled identifiers.

>
>> --
>>
>> Robert Haas
>> EnterpriseDB: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>
>
>
> --
> // Dmitriy.
>
>
>


-- 
// Dmitriy.

Re: [HACKERS] hstores in pl/python

[Dmitriy Igrishin]

2010/12/15 Pavel Stehule 

> 2010/12/15 Dmitriy Igrishin :
> >
> >
> > 2010/12/15 Florian Pflug 
> >>
> >> On Dec15, 2010, at 18:33 , Dmitriy Igrishin wrote:
> >> > 2010/12/15 Florian Pflug 
> >> > On Dec15, 2010, at 16:18 , Dmitriy Igrishin wrote:
> >> > >> 2010/12/15 Florian Pflug 
> >> > >> On Dec15, 2010, at 02:14 , James William Pye wrote:
> >> > >> > On Dec 13, 2010, at 6:16 PM, Tom Lane wrote:
> >> > >> >> how do you identify which type OID is really hstore?
> >> > >> >
> >> > >> > How about an identification field on pg_type?
> >> > >> >
> >> > >> > CREATE TYPE hstore ..., IDENTIFIER 'org.postgresql.hstore';
> >> > >> > -- Where the "identifier" is an arbitrary string.
> >> > >>
> >> > >> I've wanted something like this a few times when dealing
> >> > >> with custom types within a client. A future protocol version
> >> > >> might even transmit these identifiers instead a the type's OID,
> >> > >> thereby removing the dependency on OID from clients entirely.
> >> > >
> >> > > In some another tread I've proposed CREATE TYPE ... WITH OID...
> >> > Yeah, and I believe type identifiers are probably what you were
> >> > really looking for ;-)
> >> > Indeed, but why OID cannot serve as identifier in this case ? Why to
> >> > encode the code ? :-)
> >> Because there are only 2^32 OIDs, so if people start picking them at
> >> random, sooner or later there will be collisions.
> >
> > Yes, but range of PostgreSQL's OIDs can be reserved. One or even ten
> > millions, e.g. can be enough.
> >
> >>
> >> > Type identifiers would solve
> >> > this, by providing an easy and unambiguous way to find specific types.
> >> > Agree with 1st assertion but disagree with 2nd. If I understand
> >> > correctly,
> >> > "identifier" is a second name for type (object), but Java-styled,
> right
> >> > ?
> >> > It probably does solve the problem if there are will be convention
> that
> >> > types org.postgresql.* are reserved.
> >> Yeah, that'd be the idea. If everyone uses reversed DNS-style names, and
> >> everyone picks a name belonging to a DNS zone under his control, there
> >> cannot be any collisions. At least for java packages, this seems to work
> >> pretty nicely.
> >>
> >> > But why not reserve name of type
> >> > "hstore" and prevent the user to create type with this reserved name ?
> >> > All this tells me one thing - to avoid conflicts of naming of specific
> >> > types
> >> > it is necessary to make them built-in.
> >> None of these solutions scale well.
> >
> > Well, If there are will be identifiers for each type, e.g.
> > org.postgresql.integer, why
> > they need to be built-in ? For "historical reasons" ? :-)
> > Let them also be in contribs...
>
> some types are used in system tables, so without support of these
> types, then you can't to add a new types. It's a egg-chicken problem
>
So, the formal criterion to make the type built-in is "the type is must be
primitive" ?

>
> Pavel
>
> >>
> >> best regards,
> >> Florian Pflug
> >>
> >>
> >
> >
> >
> > --
> > // Dmitriy.
> >
> >
> >
>



-- 
// Dmitriy.

Re: [HACKERS] hstores in pl/python

[Robert Haas]

On Wed, Dec 15, 2010 at 2:14 PM, Robert Haas  wrote:
> On Wed, Dec 15, 2010 at 1:22 PM, Dmitriy Igrishin  wrote:
>> Yes, but range of PostgreSQL's OIDs can be reserved. One or even ten
>> millions, e.g. can be enough.
>
> No, they can't.  PostgreSQL is already deployed without any such
> restriction.  You can "reserve" those OIDs because they may already be
> in use on any given system.

Err, you CAN'T reserve these OIDs because blah blah.

-- 
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] hstores in pl/python

[Robert Haas]

On Wed, Dec 15, 2010 at 1:22 PM, Dmitriy Igrishin  wrote:
> Yes, but range of PostgreSQL's OIDs can be reserved. One or even ten
> millions, e.g. can be enough.

No, they can't.  PostgreSQL is already deployed without any such
restriction.  You can "reserve" those OIDs because they may already be
in use on any given system.

-- 
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] [PATCH] V3: Idle in transaction cancellation

[Robert Haas]

On Wed, Dec 15, 2010 at 10:02 AM, Andres Freund  wrote:
>> Is there a way that errstart() and/or errfinish() can know enough
>> about the state of the communication with the frontend to decide
>> whether to suppress edata->output_to_client?  In other words, instead
>> of explicitly passing in a flag that says whether to inform the
>> client, it would be better for the error-reporting machinery to
>> intrinsically know whether it's right to send_message_to_frontend().
>> Otherwise, an error thrown from an unexpected location might not have
>> the flag set correctly.
>
> You could use "DoingCommandRead" to solve that specific use-case, but the
> COMERROR ones I don't see as being replaced that easily.

Well, again, I'm not an expert on this, but why would we need to unify
the two mechanisms?  Asynchronous rollbacks (what we're trying to do
here) and protocol violations (which is what COMMERROR looks to be
used for) are really sort of different.  I'm not really sure we need
to handle them in the same way.  Let's think about a recovery conflict
where ProcessInterrupts() has been called.  Right now, if that
situation occurs and we are not DoingCommandRead, then we just throw
an error.  That's either safe, or an already-existing bug.  So the
question is what to do if we ARE DoingCommandRead.  Right now, we
throw a fatal error.  There's no comment explaining why, but I'm
guessing that the reason is the same problem we're trying to fix here:
the protocol state gets confused - but if we throw a FATAL then the
client goes away and we don't have to worry about it any more.  Our
goal here, as I understand it, is to handle that case without a FATAL.

So let's see... if we're DoingCommandRead at that point, and
whereToSendOutput == DestRemote then we set whereToSendOutput =
DestNone before throwing the error, and restore it just after we reset
DoingCommandRead?  

-- 
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] Complier warnings on mingw gcc 4.5.0

[Tom Lane]

Andrew Dunstan  writes:
> And the attached hack allowed "make check" to succeed.

> I think the logic in tcop/postgres.c and postmaster/postmaster.c is 
> probably wrong. If we are using our getopt/getopt_long, we want to be 
> setting optreset, whether or not configure found one in the system 
> libraries.

Yeah, that's what I suggested earlier; but if your build *wasn't* using
our versions before, we're still no closer to understanding why it was
failing then.  Another small problem is that a close inspection of our
getopt.c says that it does reset "place" to point at a constant before
returning -1, in every path except the "--" case which I doubt is being
invoked.  So my idea that we were clobbering argv underneath it doesn't
seem to hold up.  I'm still feeling that we don't understand what's
happening.

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] Complier warnings on mingw gcc 4.5.0

[Andrew Dunstan]

On 12/15/2010 12:28 PM, Andrew Dunstan wrote:




I think you're probably right. narwhal reports having optreset, but my 
Mingw reports not having it, so this looks like a likely culprit.



And the attached hack allowed "make check" to succeed.

I think the logic in tcop/postgres.c and postmaster/postmaster.c is 
probably wrong. If we are using our getopt/getopt_long, we want to be 
setting optreset, whether or not configure found one in the system 
libraries.


cheers

andrew



diff --git a/configure b/configure
index 08fd1c8..0cfcb9a 100755
--- a/configure
+++ b/configure
@@ -20758,6 +20758,11 @@ esac
 
 fi
 
+if test "$PORTNAME" = "win32" -a x"$pgac_cv_var_int_optreset" != x"yes"; then 
+  LIBOBJS="$LIBOBJS getopt.$ac_objext getopt_long.$ac_objext"
+fi
+
+
 # Cygwin's erand48() is broken (always returns zero) in some releases,
 # so force use of ours.
 if test "$PORTNAME" = "cygwin"; then
diff --git a/src/backend/postmaster/postmaster.c 
b/src/backend/postmaster/postmaster.c
index 90854f4..c2f0436 100644
--- a/src/backend/postmaster/postmaster.c
+++ b/src/backend/postmaster/postmaster.c
@@ -313,9 +313,7 @@ extern char *optarg;
 extern int optind,
opterr;
 
-#ifdef HAVE_INT_OPTRESET
 extern int optreset;   /* might not be declared by 
system headers */
-#endif
 
 #ifdef USE_BONJOUR
 static DNSServiceRef bonjour_sdref = NULL;
@@ -751,9 +749,7 @@ PostmasterMain(int argc, char *argv[])
 * getopt(3) library so that it will work correctly in subprocesses.
 */
optind = 1;
-#ifdef HAVE_INT_OPTRESET
optreset = 1;   /* some systems need this too */
-#endif
 
/* For debugging: display postmaster environment */
{
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index ff2e9bd..da7db16 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -78,9 +78,7 @@
 extern char *optarg;
 extern int optind;
 
-#ifdef HAVE_INT_OPTRESET
 extern int optreset;   /* might not be declared by 
system headers */
-#endif
 
 
 /* 
@@ -3442,9 +3440,7 @@ process_postgres_switches(int argc, char *argv[], 
GucContext ctx)
 * or when this function is called a second time with another array.
 */
optind = 1;
-#ifdef HAVE_INT_OPTRESET
optreset = 1;   /* some systems need this too */
-#endif
 
return dbname;
 }

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

Re: [HACKERS] getting composite types info from libpq

[Merlin Moncure]

On Wed, Dec 15, 2010 at 1:25 PM, Daniele Varrazzo
 wrote:
> Hello,
>
> when a query returns a composite type, the libpq PQftype() function
> reports the oid of the "record" type. In psycopg:
>
>    >>> cur.execute("select (1,2)")
>    >>> cur.description
>    (('row', 2249, None, -1, None, None, None),)
>
>    test=# select typname from pg_type where oid = 2249;
>     typname
>    -
>     record
>
> Is there a way to recursively retrieve the types for the record components?

not without talking to the server, unless you had previously pulled
pg_attribute data.

select * from pg_attribute where attrelid = 2249;

This question is more appropriate for -general, but what are you trying to do?

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] Segfault related to pg_authid when running initdb from git master

[Tom Lane]

Robert Haas  writes:
> What distro are you using?  This can't be broken across the board,
> given the lack of metoos.  Can you use git bisect to figure out which
> commit broke it?

Before that, have you tried the old standby of "make distclean" and a
full rebuild/reinstall?  The lack of buildfarm confirmation makes me
highly suspicious that there's any real problem.

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] getting composite types info from libpq

[Daniele Varrazzo]

Hello,

when a query returns a composite type, the libpq PQftype() function
reports the oid of the "record" type. In psycopg:

>>> cur.execute("select (1,2)")
>>> cur.description
(('row', 2249, None, -1, None, None, None),)

test=# select typname from pg_type where oid = 2249;
 typname
-
 record

Is there a way to recursively retrieve the types for the record components?

Thanks,

-- Daniele

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

Re: [HACKERS] hstores in pl/python

[Dmitriy Igrishin]

2010/12/15 Florian Pflug 

> On Dec15, 2010, at 18:33 , Dmitriy Igrishin wrote:
> > 2010/12/15 Florian Pflug 
> > On Dec15, 2010, at 16:18 , Dmitriy Igrishin wrote:
> > >> 2010/12/15 Florian Pflug 
> > >> On Dec15, 2010, at 02:14 , James William Pye wrote:
> > >> > On Dec 13, 2010, at 6:16 PM, Tom Lane wrote:
> > >> >> how do you identify which type OID is really hstore?
> > >> >
> > >> > How about an identification field on pg_type?
> > >> >
> > >> > CREATE TYPE hstore ..., IDENTIFIER 'org.postgresql.hstore';
> > >> > -- Where the "identifier" is an arbitrary string.
> > >>
> > >> I've wanted something like this a few times when dealing
> > >> with custom types within a client. A future protocol version
> > >> might even transmit these identifiers instead a the type's OID,
> > >> thereby removing the dependency on OID from clients entirely.
> > >
> > > In some another tread I've proposed CREATE TYPE ... WITH OID...
> > Yeah, and I believe type identifiers are probably what you were
> > really looking for ;-)
> > Indeed, but why OID cannot serve as identifier in this case ? Why to
> > encode the code ? :-)
> Because there are only 2^32 OIDs, so if people start picking them at
> random, sooner or later there will be collisions.
>
Yes, but range of PostgreSQL's OIDs can be reserved. One or even ten
millions, e.g. can be enough.


> > Type identifiers would solve
> > this, by providing an easy and unambiguous way to find specific types.
> > Agree with 1st assertion but disagree with 2nd. If I understand
> correctly,
> > "identifier" is a second name for type (object), but Java-styled, right ?
> > It probably does solve the problem if there are will be convention that
> > types org.postgresql.* are reserved.
> Yeah, that'd be the idea. If everyone uses reversed DNS-style names, and
> everyone picks a name belonging to a DNS zone under his control, there
> cannot be any collisions. At least for java packages, this seems to work
> pretty nicely.
>
> > But why not reserve name of type
> > "hstore" and prevent the user to create type with this reserved name ?
> > All this tells me one thing - to avoid conflicts of naming of specific
> types
> > it is necessary to make them built-in.
> None of these solutions scale well.
>
Well, If there are will be identifiers for each type, e.g.
org.postgresql.integer, why
they need to be built-in ? For "historical reasons" ? :-)
Let them also be in contribs...

>
> best regards,
> Florian Pflug
>
>
>


-- 
// Dmitriy.

Re: [HACKERS] Default mode for shutdown

[Tom Lane]

Robert Haas  writes:
> On Wed, Dec 15, 2010 at 9:47 AM, Tom Lane  wrote:
>> Yeah, and more to the point, do I want to finish whatever I was doing in
>> that window?  Fast-by-default is a nice hammer to swing, but one day
>> you'll pound your finger.

> I guess.  I've pounded my finger enough time with the current default
> that I'd be willing to try a different size hammer.  The scenario you
> describe has yet to occur in 10+ years of using the product, but
> obviously not everyone's experience will match on this point.

I think the ultimate basis for the way it's set up now is the mantra of
"be safe by default"; which I believe I've heard you repeating in other
contexts.  Between that principle and the backwards-compatibility
hazards, I really don't think there's adequate justification for
changing 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] hstores in pl/python

[Dimitri Fontaine]

Robert Haas  writes:
> Well then we need a reliable way to identify a type.  What would satisfy you?

An oid ?

=# select objid
 from pg_extension_objects('hstore') 
where   class = 'pg_type'::regclass 
  and objdesc ~ '(^|\.)hstore$';
 objid 
---
 16387
(1 row)

You have 4 types in there so you want to be somewhat careful here…

=# select * from pg_extension_objects('hstore') where class = 
'pg_type'::regclass;
  class  | classid | objid |   objdesc
-+-+---+--
 pg_type |1247 | 16387 | type utils.hstore
 pg_type |1247 | 16392 | type utils.hstore[]
 pg_type |1247 | 16466 | type utils.ghstore
 pg_type |1247 | 16469 | type utils.ghstore[]
(4 rows)

Regards,
-- 
Dimitri Fontaine
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] Default mode for shutdown

[Robert Haas]

On Wed, Dec 15, 2010 at 9:57 AM, Tom Lane  wrote:
> Robert Haas  writes:
>> On Wed, Dec 15, 2010 at 9:47 AM, Tom Lane  wrote:
>>> Yeah, and more to the point, do I want to finish whatever I was doing in
>>> that window?  Fast-by-default is a nice hammer to swing, but one day
>>> you'll pound your finger.
>
>> I guess.  I've pounded my finger enough time with the current default
>> that I'd be willing to try a different size hammer.  The scenario you
>> describe has yet to occur in 10+ years of using the product, but
>> obviously not everyone's experience will match on this point.
>
> I think the ultimate basis for the way it's set up now is the mantra of
> "be safe by default"; which I believe I've heard you repeating in other
> contexts.  Between that principle and the backwards-compatibility
> hazards, I really don't think there's adequate justification for
> changing this.

Backwards compatibility is, I think, a reasonable argument for
maintaining the current default.  However, I don't agree that the
current behavior is safe by default.  What often happens is that the
system gets stuck in a state where the existing connections will never
terminate (or not for a long time) but new connections aren't accepted
either.  So you're sitting there waiting for the database to shut down
- which it never does - meanwhile, half the people hitting your web
site are getting DOS'd.

Certainly, if you have an environment where people are mostly logging
into the database directly (not through a connection pooler) and they
do a few important queries and then disconnect, smart is a better
default.  But if you have an environment where (for whatever reason)
long-lasting connections are common, smart is worse than useless.

-- 
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] [PATCH] V3: Idle in transaction cancellation

[Andres Freund]

On Wednesday 15 December 2010 15:40:20 Robert Haas wrote:
> On Wed, Dec 15, 2010 at 7:47 AM, Andres Freund  wrote:
> > I thought about doing that first. Btw, LOG_NO_CLIENT is just a more
> > abstracted way of what COMERROR did before...
> 
> Hmm, but it must not be quite the same, because that didn't require
> the silent_error_while_idle flag.
True. Thats a separate thing.

> >> Yeah.  I'll try to find some time to think about this some more.  It
> >> would sure be nice if we could find a solution that's a bit
> >> conceptually cleaner, even if it basically works the same way as what
> >> you've done here.
> > 
> > I would like that as well. I am not sure you can achieve that in a
> > reasonable amount of work. At least I couldn't.
> Is there a way that errstart() and/or errfinish() can know enough
> about the state of the communication with the frontend to decide
> whether to suppress edata->output_to_client?  In other words, instead
> of explicitly passing in a flag that says whether to inform the
> client, it would be better for the error-reporting machinery to
> intrinsically know whether it's right to send_message_to_frontend().
> Otherwise, an error thrown from an unexpected location might not have
> the flag set correctly.
Currently there are no other locations where we errors could get thrown at 
that point but I see where youre going.

You could use "DoingCommandRead" to solve that specific use-case, but the 
COMERROR ones I don't see as being replaced that easily.
We could introduce something like

NoLogToClientBegin();
NoLogToClientEnd();
int NoLogToClientCntr = 0;

but that sounds like overdoing it for me.

Andres

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

Re: [HACKERS] Segfault related to pg_authid when running initdb from git master

[Robert Haas]

On Wed, Dec 15, 2010 at 12:39 PM, Peter Geoghegan
 wrote:
> Observe that the initdb and postgres timestamps are the same.

Hrm.

>>> I cannot find the coredump. Perhaps it's a permissions issue. What do you 
>>> think?
>>
>> It would presumably get dumped into the data directory.  So if
>> --noclean isn't used I expect it'll get nuked.
>
> It isn't there...it just looks like a virginal PGDATA directory.

Double hrm.

I have no idea how you can be getting line number information for
initdb but not postgres.  I think what you're getting from postgres is
normally what I'd expect to see without --enable-debug.  It sounds
like you are doing it right, but I have no explanation for the
results.

What distro are you using?  This can't be broken across the board,
given the lack of metoos.  Can you use git bisect to figure out which
commit broke it?

-- 
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] range intervals in window function frames

[Daniel Popowich]

Hello, all!

I first posted this to pgsql-general, but didn't get a definitive
answer to my question concerning if a window function feature is
scheduled or being worked on for 9.x.

--

I need to do moving averages over time series data and was hoping
window functions could solve the problem for me, but it doesn't look
like 8.4 or even 9.0 implementations are quite there, yet.

Currently, if I have this table:

  create table sample (
  tstimestamp,
  value integer
  );
  create index sample_ts on sample (ts);

and say I want a moving average of value over a fixed interval of five
minutes (note that this could mean varying numbers of records in each
"frame"), then I can do this:

  select *, avg_over_interval(ts, interval '5 min') from sample order by ts;
  
Where avg_over_interval() is defined like this:

  create or replace function avg_over_interval(timestamp, interval)
  returns numeric as $$
 select avg(value) from sample where (($1-$2) <= ts) and (ts <= $1);
  $$ language sql;

What I would LIKE to do is this:

  select *, avg(ts) over(order by ts range (interval '5 min') preceding)
 from sample order by ts;

Which is way cleaner and, I assume, more efficient.

Questions:

  1) Is there active work on window functions with frames over
 interval ranges?

  2) If not, how can I help with that?

  3) Until the functionality is in 9.x, can I make what I'm doing more
 efficient?  Is there a better way to do this without window
 functions?  (I tried an inline subquery instead of the function
 call, but it was twice as slow as the function.)

 
Thanks all for you help.

Dan


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

Re: [HACKERS] hstores in pl/python

[Florian Pflug]

On Dec15, 2010, at 18:33 , Dmitriy Igrishin wrote:
> 2010/12/15 Florian Pflug 
> On Dec15, 2010, at 16:18 , Dmitriy Igrishin wrote:
> >> 2010/12/15 Florian Pflug 
> >> On Dec15, 2010, at 02:14 , James William Pye wrote:
> >> > On Dec 13, 2010, at 6:16 PM, Tom Lane wrote:
> >> >> how do you identify which type OID is really hstore?
> >> >
> >> > How about an identification field on pg_type?
> >> >
> >> > CREATE TYPE hstore ..., IDENTIFIER 'org.postgresql.hstore';
> >> > -- Where the "identifier" is an arbitrary string.
> >>
> >> I've wanted something like this a few times when dealing
> >> with custom types within a client. A future protocol version
> >> might even transmit these identifiers instead a the type's OID,
> >> thereby removing the dependency on OID from clients entirely.
> >
> > In some another tread I've proposed CREATE TYPE ... WITH OID...
> Yeah, and I believe type identifiers are probably what you were
> really looking for ;-)
> Indeed, but why OID cannot serve as identifier in this case ? Why to
> encode the code ? :-)
Because there are only 2^32 OIDs, so if people start picking them at
random, sooner or later there will be collisions.

> Type identifiers would solve
> this, by providing an easy and unambiguous way to find specific types. 
> Agree with 1st assertion but disagree with 2nd. If I understand correctly,
> "identifier" is a second name for type (object), but Java-styled, right ?
> It probably does solve the problem if there are will be convention that
> types org.postgresql.* are reserved.
Yeah, that'd be the idea. If everyone uses reversed DNS-style names, and
everyone picks a name belonging to a DNS zone under his control, there
cannot be any collisions. At least for java packages, this seems to work
pretty nicely.

> But why not reserve name of type
> "hstore" and prevent the user to create type with this reserved name ?
> All this tells me one thing - to avoid conflicts of naming of specific types
> it is necessary to make them built-in.
None of these solutions scale well.

best regards,
Florian Pflug



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

Re: [HACKERS] Instrument checkpoint sync calls

[Robert Haas]

On Wed, Dec 15, 2010 at 9:22 AM, Greg Smith  wrote:
> patch I submit.  Doesn't seem worth going through the trouble of committing
> that minor rework on its own, I'll slip it into the next useful thing that
> touches this area I do.  Thanks for the hint, this would work better than
> what I did.

Well, if I'm the one committing it, I'll pull that part out again and
commit it separately.  Not sure if that affects your calculus, but I
much prefer patches that don't try to do ancillary things along the
way.

-- 
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] Segfault related to pg_authid when running initdb from git master

[Peter Geoghegan]

On 15 December 2010 16:26, Robert Haas  wrote:
> On Wed, Dec 15, 2010 at 6:07 AM, Peter Geoghegan
>  wrote:
>> On 15 December 2010 01:35, Robert Haas  wrote:
>>> I am suspicious of the fact that you are invoking initdb as ./initdb.
>>> Is it possible you're invoking this from the build tree, and there's
>>> an installed copy out there that doesn't match, but is getting used?
>>> Like maybe in /usr/local/pgsql/bin?
>>
>> No, I'm not doing that. I'm running initdb from /usr/local/pgsql/bin
>> (nothing pg related can be found in my $PATH), but it's the only copy
>> on my system, which was installed from git master last night. It has
>> debugging symbols, and I've actually re-created this from initdb's
>> point of view within GDB with source level debugging.
>
> Well, something's clearly funky here because your initdb has debugging
> symbols but your postgres executable does not.  I may be missing
> something obvious, but I don't see how that can happen without mixing
> up two different builds.

Just to make sure that I'm not going crazy, I did a git pull, rebuilt
pg passing --enable-debug and --enable-casssert to configure as
before, followed by make && make install. Then I tried this:

[pe...@peter bin]$ pwd
/usr/local/pgsql/bin
[pe...@peter bin]$ ls -l
total 7720
-rwxr-xr-x. 1 root root   53977 Dec 15 16:47 clusterdb
-rwxr-xr-x. 1 root root   55058 Dec 15 16:47 createdb
-rwxr-xr-x. 1 root root   58351 Dec 15 16:47 createlang
-rwxr-xr-x. 1 root root   58036 Dec 15 16:47 createuser
-rwxr-xr-x. 1 root root   53380 Dec 15 16:47 dropdb
-rwxr-xr-x. 1 root root   62052 Dec 15 16:47 droplang
-rwxr-xr-x. 1 root root   53382 Dec 15 16:47 dropuser
-rwxr-xr-x. 1 root root  707190 Dec 15 16:47 ecpg
-rwxr-xr-x. 1 root root  123447 Dec 15 16:47 initdb
-rwxr-xr-x. 1 root root   26435 Dec 15 16:47 pg_config
-rwxr-xr-x. 1 root root   25229 Dec 15 16:47 pg_controldata
-rwxr-xr-x. 1 root root   73784 Dec 15 16:47 pg_ctl
-rwxr-xr-x. 1 root root  301781 Dec 15 16:47 pg_dump
-rwxr-xr-x. 1 root root   75323 Dec 15 16:47 pg_dumpall
-rwxr-xr-x. 1 root root   32015 Dec 15 16:47 pg_resetxlog
-rwxr-xr-x. 1 root root  131867 Dec 15 16:47 pg_restore
-rwxr-xr-x. 1 root root   91006 Dec  6 11:34 pg_upgrade
-rwxr-xr-x. 1 root root 5380671 Dec 15 16:47 postgres
lrwxrwxrwx. 1 root root   8 Dec 15 16:47 postmaster -> postgres
-rwxr-xr-x. 1 root root  398677 Dec 15 16:47 psql
-rwxr-xr-x. 1 root root   55257 Dec 15 16:47 reindexdb
-rwxr-xr-x. 1 root root   32410 Dec 15 16:47 vacuumdb
[pe...@peter bin]$ which postgres
/usr/bin/which: no postgres in
(/usr/local/bin:/usr/bin:/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/peter/bin)
[pe...@peter bin]$ which initdb
/usr/bin/which: no initdb in
(/usr/local/bin:/usr/bin:/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/peter/bin)

Observe that the initdb and postgres timestamps are the same. This
laptop is less than 2 weeks old, and has never had any postgres
packages installed on it. I can once again reproduce the problem,
exactly as before. My postgres executable does have debugging symbols,
just less than initdb (I'm not sure what the exact term is, but it
just lacks line information while having some debugging symbols).

>> I cannot find the coredump. Perhaps it's a permissions issue. What do you 
>> think?
>
> It would presumably get dumped into the data directory.  So if
> --noclean isn't used I expect it'll get nuked.

It isn't there...it just looks like a virginal PGDATA directory.

> Ugh.  Maybe someone smarter can figure out what that means, but I have
> no clue.  _bt_preprocess_keys() is a pretty good-sized function;
> there's no obvious way to know which pointer reference is blowing up
> without line-number information.

That's a pity, because I don't have a clue how to get line number
information. I could always try printf() debugging, but I really
shouldn't have to.

-- 
Regards,
Peter Geoghegan

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

Re: [HACKERS] hstores in pl/python

[Dmitriy Igrishin]

2010/12/15 Florian Pflug 

> On Dec15, 2010, at 16:18 , Dmitriy Igrishin wrote:
> >> 2010/12/15 Florian Pflug 
> >> On Dec15, 2010, at 02:14 , James William Pye wrote:
> >> > On Dec 13, 2010, at 6:16 PM, Tom Lane wrote:
> >> >> how do you identify which type OID is really hstore?
> >> >
> >> > How about an identification field on pg_type?
> >> >
> >> > CREATE TYPE hstore ..., IDENTIFIER 'org.postgresql.hstore';
> >> > -- Where the "identifier" is an arbitrary string.
> >>
> >> I've wanted something like this a few times when dealing
> >> with custom types within a client. A future protocol version
> >> might even transmit these identifiers instead a the type's OID,
> >> thereby removing the dependency on OID from clients entirely.
> >
> > In some another tread I've proposed CREATE TYPE ... WITH OID...
> Yeah, and I believe type identifiers are probably what you were
> really looking for ;-)
>
Indeed, but why OID cannot serve as identifier in this case ? Why to
encode the code ? :-)


>
> > but it was rejected and was proposed to cache OIDs on client side.
> > It is right approach, IMO.
> Yes, but to cache OIDs you first have to find them. As long as their
> name and schema are known, thats easy, but once they aren't you're
> pretty much screwed.Since CREATE EXTENSION is going to let you
> install an extension into any schema you want, not knowing the schema
> is going to be pretty common, I believe.

Agree.


> Type identifiers would solve
> this, by providing an easy and unambiguous way to find specific types.
>
Agree with 1st assertion but disagree with 2nd. If I understand correctly,
"identifier" is a second name for type (object), but Java-styled, right ?
It probably does solve the problem if there are will be convention that
types org.postgresql.* are reserved. But why not reserve name of type
"hstore" and prevent the user to create type with this reserved name ?
All this tells me one thing - to avoid conflicts of naming of specific types
it is necessary to make them built-in.

>
> > But, IMO, comparing strings to determine type for each parameter
> > is not very good idea because it is not so efficient as comparing
> > integers, obviously.
> That's maybe an argument against a possible future protocol version
> that'd transfer type identifiers instead of OIDS. But not against
> associating type identifiers with types in the first place, since
> after your initial lookup you'd still be comparing OIDs.
>

> best regards,
> Florian Pflug
>
>
>
>


-- 
// Dmitriy.

Re: [HACKERS] Complier warnings on mingw gcc 4.5.0

[Andrew Dunstan]

On 12/15/2010 11:49 AM, Tom Lane wrote:

Magnus Hagander  writes:

On Wed, Dec 15, 2010 at 17:43, Tom Lane  wrote:

Do we use configure at all on a mingw build?  If we don't, then
HAVE_INT_OPTRESET is surely not getting defined.

We do use configure on mingw. The output from a regular mingw
configure run formed the base for the config file we use for MSVC
where we can't run it, but an actual mingw build will re-run configure
every time.

Hm.  It still seems pretty likely to me that the root cause is a change
in mingw's getopt library function, but I don't have a theory about the
precise mechanism.  Is there any convenient place where we can look at
the current version of their library sources, as well as the version in
use in the working buildfarm members?




I think you're probably right. narwhal reports having optreset, but my 
Mingw reports not having it, so this looks like a likely culprit.


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] Complier warnings on mingw gcc 4.5.0

[Tom Lane]

Magnus Hagander  writes:
> On Wed, Dec 15, 2010 at 17:43, Tom Lane  wrote:
>> Do we use configure at all on a mingw build?  If we don't, then
>> HAVE_INT_OPTRESET is surely not getting defined.

> We do use configure on mingw. The output from a regular mingw
> configure run formed the base for the config file we use for MSVC
> where we can't run it, but an actual mingw build will re-run configure
> every time.

Hm.  It still seems pretty likely to me that the root cause is a change
in mingw's getopt library function, but I don't have a theory about the
precise mechanism.  Is there any convenient place where we can look at
the current version of their library sources, as well as the version in
use in the working buildfarm members?

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] Default mode for shutdown

[Jim Nasby]

On Dec 15, 2010, at 9:11 AM, Alvaro Herrera wrote:
> Excerpts from Robert Haas's message of mié dic 15 12:03:06 -0300 2010:
> 
>> Certainly, if you have an environment where people are mostly logging
>> into the database directly (not through a connection pooler) and they
>> do a few important queries and then disconnect, smart is a better
>> default.  But if you have an environment where (for whatever reason)
>> long-lasting connections are common, smart is worse than useless.
> 
> It occurs to me that we may need a new mode, which disconnects sessions
> that are not in a transaction (or as soon as they are) but leaves
> in-progress transactions alone; this could be the new default.  Of
> course, this is much more difficult to implement than the current modes.

+1; that would certainly be useful for us.
--
Jim C. Nasby, Database Architect   j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net



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

Re: [HACKERS] Complier warnings on mingw gcc 4.5.0

[Magnus Hagander]

On Wed, Dec 15, 2010 at 17:43, Tom Lane  wrote:
> Andrew Dunstan  writes:
>> On 12/15/2010 11:12 AM, Tom Lane wrote:
>>> 1. Is that build using src/port/getopt.c, or a library-supplied getopt?
>>> What about getopt_long.c?
>>>
>>> 2. Is HAVE_INT_OPTRESET getting defined?  Should it be?
>
>> I had the same thought. I did try forcing use of our getopt and
>> getopt_long, without success, but didn't look at optreset.
>
> Do we use configure at all on a mingw build?  If we don't, then
> HAVE_INT_OPTRESET is surely not getting defined.

We do use configure on mingw. The output from a regular mingw
configure run formed the base for the config file we use for MSVC
where we can't run it, but an actual mingw build will re-run configure
every time.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.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] Complier warnings on mingw gcc 4.5.0

[Tom Lane]

Andrew Dunstan  writes:
> On 12/15/2010 11:12 AM, Tom Lane wrote:
>> 1. Is that build using src/port/getopt.c, or a library-supplied getopt?
>> What about getopt_long.c?
>> 
>> 2. Is HAVE_INT_OPTRESET getting defined?  Should it be?

> I had the same thought. I did try forcing use of our getopt and 
> getopt_long, without success, but didn't look at optreset.

Do we use configure at all on a mingw build?  If we don't, then
HAVE_INT_OPTRESET is surely not getting defined.

It looks to me like it might be a good idea to force HAVE_INT_OPTRESET
on when we are using our own versions of getopt/getopt_long.  If we
don't set that, then correct behavior depends on the assumption that the
internal variable "place" is pointing at a null when the second series
of getopt calls starts.  While I'm prepared to believe that the last
call of getopt left it that way, it's not clear that we can safely
assume that the underlying argv array hasn't been clobbered meanwhile.

You might try adding some debug printouts to src/port/getopt.c to see if
you can trace exactly what's happening there.

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] ALTER TABLE ... REPLACE WITH

[Robert Haas]

On Wed, Dec 15, 2010 at 11:30 AM, Kevin Grittner
 wrote:
> Heikki Linnakangas  wrote:
>> On 14.12.2010 20:27, Simon Riggs wrote:
>
>>> 1. Prepare new data into "new_table" and build indexes
>>> 2. Swap old for new
>>> BEGIN;
>>> DROP TABLE "old_table";
>>> ALTER TABLE "new_table" RENAME to "old_table";
>>> COMMIT;
>>>
>>> Step (2) works, but any people queuing to access the table
>>> will see ERROR: could not open relation with OID x
>>
>> Could we make that work without error?
>
> Well, that worked better for us than building up the new
> contents in a temporary table and doing the sequence Tom
> suggests, but to eliminate the above error we had to do:
>
> BEGIN;
> ALTER TABLE "old_table" RENAME TO "dead_table";
> ALTER TABLE "new_table" RENAME TO "old_table";
> COMMIT;
> -- Wait for all references to old OID to expire.
> DROP TABLE "dead_table";

Been there, done that.  Didn't buy the post-card.

-- 
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] Complier warnings on mingw gcc 4.5.0

[Andrew Dunstan]

On 12/15/2010 11:12 AM, Tom Lane wrote:

Alvaro Herrera  writes:

This bug seems closely related to process_postgres_switches.  I guess
it'd be useful to add some debugging printouts there to figure out
what's being passed the second time around.

It strikes me that the most obvious source for a platform dependency
there would be getopt(), in particular the arrangements to cause getopt
to behave sanely when we invoke it on a different argc array the second
time around.  If that were failing for some reason, you could imagine
getopt seeing 'postgres' as the next switch to parse, which could lead
to the reported failure.

Hence:

1. Is that build using src/port/getopt.c, or a library-supplied getopt?
What about getopt_long.c?

2. Is HAVE_INT_OPTRESET getting defined?  Should it be?




I had the same thought. I did try forcing use of our getopt and 
getopt_long, without success, but didn't look at optreset.


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] Segfault related to pg_authid when running initdb from git master

[Robert Haas]

On Wed, Dec 15, 2010 at 6:07 AM, Peter Geoghegan
 wrote:
> On 15 December 2010 01:35, Robert Haas  wrote:
>> I am suspicious of the fact that you are invoking initdb as ./initdb.
>> Is it possible you're invoking this from the build tree, and there's
>> an installed copy out there that doesn't match, but is getting used?
>> Like maybe in /usr/local/pgsql/bin?
>
> No, I'm not doing that. I'm running initdb from /usr/local/pgsql/bin
> (nothing pg related can be found in my $PATH), but it's the only copy
> on my system, which was installed from git master last night. It has
> debugging symbols, and I've actually re-created this from initdb's
> point of view within GDB with source level debugging.

Well, something's clearly funky here because your initdb has debugging
symbols but your postgres executable does not.  I may be missing
something obvious, but I don't see how that can happen without mixing
up two different builds.

>> Can you fire up gdb on this core dump, using "gdb
>> /usr/local/pgsql/bin/postgres /path/to/coredump"?  Or, another
>> possibility is to run initdb with --noclean and then run the command,
>> without routing the output to /dev/null:
>>
>> /usr/local/pgsql/bin/postgres" --single -F -O -c
>> search_path=pg_catalog -c exit_on_error=true template1
>
> I cannot find the coredump. Perhaps it's a permissions issue. What do you 
> think?

It would presumably get dumped into the data directory.  So if
--noclean isn't used I expect it'll get nuked.

> Anyway, I have produced a useful backtrace by debugging postgres
> directly after running initdb with --noclean as described:
>
> [pe...@peter bin]$ /usr/local/pgsql/bin/postgres --single -F -O -c
> search_path=pg_catalog -c exit_on_error=true template1
> Segmentation fault
> [pe...@peter bin]$ gdb postgres
> GNU gdb (GDB) Fedora (7.2-26.fc14)
> Copyright (C) 2010 Free Software Foundation, Inc.
> License GPLv3+: GNU GPL version 3 or later 
> This is free software: you are free to change and redistribute it.
> There is NO WARRANTY, to the extent permitted by law.  Type "show copying"
> and "show warranty" for details.
> This GDB was configured as "x86_64-redhat-linux-gnu".
> For bug reporting instructions, please see:
> ...
> Reading symbols from /usr/local/pgsql/bin/postgres...done.
> (gdb) set args --single -F -O -c search_path=pg_catalog -c
> exit_on_error=true template1
> (gdb) start
> Temporary breakpoint 1 at 0x577360
> Starting program: /usr/local/pgsql/bin/postgres --single -F -O -c
> search_path=pg_catalog -c exit_on_error=true template1
>
> Temporary breakpoint 1, 0x00577360 in main ()
> (gdb) c
> Continuing.
>
> Program received signal SIGSEGV, Segmentation fault.
> 0x0047615b in _bt_preprocess_keys ()
> (gdb) bt
> #0  0x0047615b in _bt_preprocess_keys ()
> #1  0x00475382 in _bt_first ()
> #2  0x00473d71 in btgettuple ()
> #3  0x006ba67c in FunctionCall2 ()
> #4  0x0046e08a in index_getnext ()
> #5  0x0046d556 in systable_getnext ()
> #6  0x006a92bf in LookupOpclassInfo ()
> #7  0x006a9a58 in RelationInitIndexAccessInfo ()
> #8  0x006aa9cb in RelationBuildDesc ()
> #9  0x006aabfd in load_critical_index ()
> #10 0x006ac12a in RelationCacheInitializePhase3 ()
> #11 0x006c19ca in InitPostgres ()
> #12 0x0060058f in PostgresMain ()
> #13 0x0057774d in main ()

Ugh.  Maybe someone smarter can figure out what that means, but I have
no clue.  _bt_preprocess_keys() is a pretty good-sized function;
there's no obvious way to know which pointer reference is blowing up
without line-number information.

-- 
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] CommitFest wrap-up

[Florian Pflug]

On Dec15, 2010, at 17:17 , Robert Haas wrote:
> Nah, don't bother reposting.   It'd be helpful if you could add a link
> to that message on the CF app though.


Already done. Seems we've hit a race condition there - you must have overlooked
the signalling the semaphore on my rooftop did to warn you...

best regards,
Florian Pflug


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

Re: [HACKERS] Complier warnings on mingw gcc 4.5.0

[Tom Lane]

Alvaro Herrera  writes:
> This bug seems closely related to process_postgres_switches.  I guess
> it'd be useful to add some debugging printouts there to figure out
> what's being passed the second time around.

It strikes me that the most obvious source for a platform dependency
there would be getopt(), in particular the arrangements to cause getopt
to behave sanely when we invoke it on a different argc array the second
time around.  If that were failing for some reason, you could imagine
getopt seeing 'postgres' as the next switch to parse, which could lead
to the reported failure.

Hence:

1. Is that build using src/port/getopt.c, or a library-supplied getopt?
What about getopt_long.c?

2. Is HAVE_INT_OPTRESET getting defined?  Should it be?

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] Complier warnings on mingw gcc 4.5.0

[Andrew Dunstan]

On 12/15/2010 10:17 AM, Tom Lane wrote:

Alvaro Herrera  writes:

Excerpts from Andrew Dunstan's message of mié dic 15 02:08:24 -0300 2010:

That didn't work. But git bisect says it's this commit that's to blame:


Hmm I wonder if this is reproducible in a non-Windows EXEC_BACKEND
scenario.

I'm pretty sure I tried the no-flat-files code in that scenario while
writing it.  But it might be worth trying that again.  You'd think
though that if EXEC_BACKEND were sufficient to provoke it, all Windows
builds would fail.  I'm still mystified by what is the difference
between Andrew's non-working installation and working mingw builds.





This is a new installation of Mingw. The buildfarm animals were set up 
years ago, with substantially older versions of Mingw. SO ISTM that 
either we have tickled a new bug of theirs or their new setup has 
tickled a bug of ours.


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] CommitFest wrap-up

[Robert Haas]

On Wed, Dec 15, 2010 at 10:57 AM, Florian Pflug  wrote:
> On Dec15, 2010, at 16:45 , Robert Haas wrote:
>> On Wed, Dec 15, 2010 at 9:20 AM, Florian Pflug  wrote:
>>> On Dec14, 2010, at 15:01 , Robert Haas wrote:
 On Tue, Dec 14, 2010 at 7:51 AM, Florian Pflug  wrote:
>> - serializable lock consistency - I am fairly certain this needs
>> rebasing.  I don't have time to deal with it right away.  That sucks,
>> because I think this is a really important change.
> I can try to find some time to update the patch if it suffers from 
> bit-rot. Would that help?

 Yes!
>>>
>>> I've rebased the patch to the current HEAD, and re-run my FK concurrency 
>>> test suite,
>>> available from https://github.com/fgp/fk_concurrency, to verify that things 
>>> still work.
>>
>> Thanks, but, EWRONGTHREAD.
>
> Sorry for that. I wasn't sure whether to post this here or into the original 
> thread,
> and it seems I ended up on the losing side of that 50-50 chance ;-)
>
> Want me to repost there, or just remember to use the correct thread next time?

Nah, don't bother reposting.   It'd be helpful if you could add a link
to that message on the CF app though.

-- 
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] CommitFest wrap-up

[Florian Pflug]

On Dec15, 2010, at 16:45 , Robert Haas wrote:
> On Wed, Dec 15, 2010 at 9:20 AM, Florian Pflug  wrote:
>> On Dec14, 2010, at 15:01 , Robert Haas wrote:
>>> On Tue, Dec 14, 2010 at 7:51 AM, Florian Pflug  wrote:
> - serializable lock consistency - I am fairly certain this needs
> rebasing.  I don't have time to deal with it right away.  That sucks,
> because I think this is a really important change.
 I can try to find some time to update the patch if it suffers from 
 bit-rot. Would that help?
>>> 
>>> Yes!
>> 
>> I've rebased the patch to the current HEAD, and re-run my FK concurrency 
>> test suite,
>> available from https://github.com/fgp/fk_concurrency, to verify that things 
>> still work.
> 
> Thanks, but, EWRONGTHREAD.

Sorry for that. I wasn't sure whether to post this here or into the original 
thread,
and it seems I ended up on the losing side of that 50-50 chance ;-)

Want me to repost there, or just remember to use the correct thread next time?

best regards,
Florian Pflug


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

Re: [HACKERS] hstores in pl/python

[Florian Pflug]

On Dec15, 2010, at 16:18 , Dmitriy Igrishin wrote:
>> 2010/12/15 Florian Pflug 
>> On Dec15, 2010, at 02:14 , James William Pye wrote:
>> > On Dec 13, 2010, at 6:16 PM, Tom Lane wrote:
>> >> how do you identify which type OID is really hstore?
>> >
>> > How about an identification field on pg_type?
>> >
>> > CREATE TYPE hstore ..., IDENTIFIER 'org.postgresql.hstore';
>> > -- Where the "identifier" is an arbitrary string.
>> 
>> I've wanted something like this a few times when dealing
>> with custom types within a client. A future protocol version
>> might even transmit these identifiers instead a the type's OID,
>> thereby removing the dependency on OID from clients entirely.
> 
> In some another tread I've proposed CREATE TYPE ... WITH OID...
Yeah, and I believe type identifiers are probably what you were
really looking for ;-)

> but it was rejected and was proposed to cache OIDs on client side.
> It is right approach, IMO.
Yes, but to cache OIDs you first have to find them. As long as their
name and schema are known, thats easy, but once they aren't you're
pretty much screwed.Since CREATE EXTENSION is going to let you
install an extension into any schema you want, not knowing the schema
is going to be pretty common, I believe. Type identifiers would solve
this, by providing an easy and unambiguous way to find specific types.

> But, IMO, comparing strings to determine type for each parameter
> is not very good idea because it is not so efficient as comparing
> integers, obviously.
That's maybe an argument against a possible future protocol version
that'd transfer type identifiers instead of OIDS. But not against
associating type identifiers with types in the first place, since
after your initial lookup you'd still be comparing OIDs.

best regards,
Florian Pflug


 

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

Re: [HACKERS] ALTER TABLE ... REPLACE WITH

[Simon Riggs]

On Wed, 2010-12-15 at 12:17 +0100, Csaba Nagy wrote:

> But one problem would be when the replaced table is the _parent_ for a
> foreign key relationship. I don't think you can have that constraint
> pre-verified on the replacement table and simply replacing the content
> could leave the child relations with orphans. 

Good point.

The only sensible way to handle this is by putting the FK checks into
check pending state (as discussed on a different thread).

We would probably need to disallow FKs with DELETE or UPDATE CASCADE
since it would be difficult to execute those.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/books/
 PostgreSQL Development, 24x7 Support, Training and Services
 


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

Re: [HACKERS] ALTER TABLE ... REPLACE WITH

[Simon Riggs]

On Wed, 2010-12-15 at 07:43 -0600, David Christensen wrote:

> Are there any considerations with toast tables and the inline line pointers 
> for toasted tuples?

Toast tables would be swapped as well. Toast pointers are only
applicable within a relfilenode, so we could not do otherwise.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/books/
 PostgreSQL Development, 24x7 Support, Training and Services
 


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

Re: [HACKERS] CommitFest wrap-up

[Robert Haas]

On Wed, Dec 15, 2010 at 9:20 AM, Florian Pflug  wrote:
> On Dec14, 2010, at 15:01 , Robert Haas wrote:
>> On Tue, Dec 14, 2010 at 7:51 AM, Florian Pflug  wrote:
 - serializable lock consistency - I am fairly certain this needs
 rebasing.  I don't have time to deal with it right away.  That sucks,
 because I think this is a really important change.
>>> I can try to find some time to update the patch if it suffers from bit-rot. 
>>> Would that help?
>>
>> Yes!
>
> I've rebased the patch to the current HEAD, and re-run my FK concurrency test 
> suite,
> available from https://github.com/fgp/fk_concurrency, to verify that things 
> still work.

Thanks, but, EWRONGTHREAD.

-- 
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] Default mode for shutdown

[Robert Haas]

On Wed, Dec 15, 2010 at 10:11 AM, Alvaro Herrera
 wrote:
> Excerpts from Robert Haas's message of mié dic 15 12:03:06 -0300 2010:
>
>> Certainly, if you have an environment where people are mostly logging
>> into the database directly (not through a connection pooler) and they
>> do a few important queries and then disconnect, smart is a better
>> default.  But if you have an environment where (for whatever reason)
>> long-lasting connections are common, smart is worse than useless.
>
> It occurs to me that we may need a new mode, which disconnects sessions
> that are not in a transaction (or as soon as they are) but leaves
> in-progress transactions alone; this could be the new default.  Of
> course, this is much more difficult to implement than the current modes.

That would probably be handy, though I think for my use cases fast
would still be better, or smart with a 30-second timeout.

-- 
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] hstores in pl/python

[Robert Haas]

On Wed, Dec 15, 2010 at 10:15 AM, Dimitri Fontaine
 wrote:
> Robert Haas  writes:
>> Wrong probem.  What we need is a way to identify a type without
>> knowing in advance what its OID is.  In other words, we need to
>> distinguish between the hstore type that is shipped in contrib, and
>> some stupid DBA who types "CREATE DOMAIN hstore as text".
>
> Yeah, yeah. Now, what's wrong with the query I sent?
>
> To ease discussion:
>
> =# select objid
>     from pg_extension_objects('hstore')
>    where class = 'pg_type'::regclass and objdesc ~ '(^|\.)hstore$';
>  objid
> ---
>  16387
> (1 row)

OK, so I guess your point is that I should read the whole email before
replying.  :-)

-- 
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] unlogged tables

[Robert Haas]

On Wed, Dec 15, 2010 at 4:06 AM, Simon Riggs  wrote:
> On Sat, 2010-11-13 at 20:55 -0500, Robert Haas wrote:
>> I think that would be a recipe for bugs.  Look at the three new macros
>> I introduced.  If you keep relistemp around, then any code which
>> relies on it is likely testing for one of those three things, or maybe
>> even something subtly different from any of them, as in the cases
>> where I needed to add a switch statement.  The way I see it, this is
>> ultimately a four-level hierarchy
>
> That argument isn't clear enough to avoid me agreeing so far with Tom
> and Andrew that logged-ness is separate from temp-ness. As you say
> though, it might be a recipe for bugs, so please explain a little more.

Sure.  Most of the existing checks for rd_istemp were actually
checking whether the relation required WAL-logging.  If there's any
third-party code out there that is checking rd_istemp, it likely also
needs to be revised to check whether WAL-logging is needed, not
whether the relation is temp.  The way I've coded it, such code will
fail to compile, and can be very easily fixed by substituting a call
to RelationNeedsWAL() or RelationUsesLocalBuffers() or
RelationUsesTempNamespace(), depending on which property the caller
actually cares about.  That's better than having the code compile, but
then not work as expected.

As of today, RelationNeedsWAL() always gives an answer which is
directly opposite to the answer given by RelationUsesLocalBuffers()
and RelationUsesTempNamespace().  But the main unlogged tables patch
changes that.  RelationNeedsWAL() will return true for permanent
tables and false for unlogged and temp tables, while
RelationUsesLocalBuffers() and RelationUsesTempNamespace() will return
false for permanent and unlogged tables and true for temp tables.
When and if we get global temporary tables, there will be a further
split between RelationUsesLocalBuffers() and
RelationUsesTempNamespace().  The former will return true for both
global and local temporary tables, and the latter only for local
temporary tables.

-- 
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] hstores in pl/python

[Jan Urbański]

On 15/12/10 16:25, Dmitriy Igrishin wrote:
> 2010/12/15 Jan Urbański 
>> So how about just adding a text column to pg_type and a IDENTIFIER
>> keywork to CREATE TYPE. It's not guaranteed to be unique, but isn't it
>> pushing the argument to the extreme? Someone can change around bool and
>> text type oids, too... And then hstore_plpython looks up the well-known
>> identifier, sets up a RVV with the OID and everyone's happy.
>>
> How IDENTIFIER differs from name ? org.postgresql.hstore vs hstore ?

Hm, now that I think of it, the only real difference is that you don't
use search_path to look it up. So public.hstore is just as good an
identifier...

I could live with plpython_hstore_type = "public.hstore", I guess.
hstore_plpython would look at that GUC, look up the type, set up a RVV
containing the OID and plpython would use it.

Cheers,
Jan

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

Re: [HACKERS] hstores in pl/python

[Dmitriy Igrishin]

2010/12/15 Jan Urbański 

> On 15/12/10 16:11, Tom Lane wrote:
> > Robert Haas  writes:
> >> I was asking what would satisfy you as regards a reliable way to
> >> identify a type, not what you think we should do about this particular
> >> proposal.
> >
> > Okay: a preassigned OID is safe.  I haven't seen any other safe
> > proposals.  Relying on a non-reserved name is transparently unsafe.
>
> We could preassign OIDs to contrib types, but that gives the
> not-contrib-nor-core types the cold shoulder.
>
> > Another possibility is that you make the user tell you the
> > fully-qualified name of the type:
> >
> >   plpython.use_hstore = 'public.hstore'
> >
> > Such a GUC would also fix the backwards compatibility issues, since
> > in the absence of a setting you'd continue to use the old behavior.
>
> I just had an illumination. The search path problem is the main issue,
> as (like you noticed), just calling I/O functions of a type should never
> give you anything worse than an ERROR.
>
> > But other than that configurability angle, this seems pretty ugly.
> > Also you'd have to think about protecting yourself against a bad
> > setting, ie the GUC specifies a type that's not hstore.  That might
> > not be a big problem though, as long as you aren't directly messing
> > with the type's representation but just calling its I/O functions.
>
> So how about just adding a text column to pg_type and a IDENTIFIER
> keywork to CREATE TYPE. It's not guaranteed to be unique, but isn't it
> pushing the argument to the extreme? Someone can change around bool and
> text type oids, too... And then hstore_plpython looks up the well-known
> identifier, sets up a RVV with the OID and everyone's happy.
>
How IDENTIFIER differs from name ? org.postgresql.hstore vs hstore ?

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



-- 
// Dmitriy.

Re: [HACKERS] hstores in pl/python

[Jan Urbański]

On 15/12/10 16:11, Tom Lane wrote:
> Robert Haas  writes:
>> I was asking what would satisfy you as regards a reliable way to
>> identify a type, not what you think we should do about this particular
>> proposal.
> 
> Okay: a preassigned OID is safe.  I haven't seen any other safe
> proposals.  Relying on a non-reserved name is transparently unsafe.

We could preassign OIDs to contrib types, but that gives the
not-contrib-nor-core types the cold shoulder.

> Another possibility is that you make the user tell you the
> fully-qualified name of the type:
> 
>   plpython.use_hstore = 'public.hstore'
> 
> Such a GUC would also fix the backwards compatibility issues, since
> in the absence of a setting you'd continue to use the old behavior.

I just had an illumination. The search path problem is the main issue,
as (like you noticed), just calling I/O functions of a type should never
give you anything worse than an ERROR.

> But other than that configurability angle, this seems pretty ugly.
> Also you'd have to think about protecting yourself against a bad
> setting, ie the GUC specifies a type that's not hstore.  That might
> not be a big problem though, as long as you aren't directly messing
> with the type's representation but just calling its I/O functions.

So how about just adding a text column to pg_type and a IDENTIFIER
keywork to CREATE TYPE. It's not guaranteed to be unique, but isn't it
pushing the argument to the extreme? Someone can change around bool and
text type oids, too... And then hstore_plpython looks up the well-known
identifier, sets up a RVV with the OID and everyone's happy.

Cheers,
Jan

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

Re: [HACKERS] hstores in pl/python

[Dmitriy Igrishin]

2010/12/15 Florian Pflug 

> On Dec15, 2010, at 02:14 , James William Pye wrote:
> > On Dec 13, 2010, at 6:16 PM, Tom Lane wrote:
> >> how do you identify which type OID is really hstore?
> >
> > How about an identification field on pg_type?
> >
> > CREATE TYPE hstore ..., IDENTIFIER 'org.postgresql.hstore';
> > -- Where the "identifier" is an arbitrary string.
>
> +1
>
> I've wanted something like this a few times when dealing
> with custom types within a client. A future protocol version
> might even transmit these identifiers instead a the type's OID,
> thereby removing the dependency on OID from clients entirely.
>
In some another tread I've proposed CREATE TYPE ... WITH OID...
but it was rejected and was proposed to cache OIDs on client side.
It is right approach, IMO.

But, IMO, comparing strings to determine type for each parameter
is not very good idea because it is not so efficient as comparing
integers, obviously.

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



-- 
// Dmitriy.

Re: [HACKERS] Complier warnings on mingw gcc 4.5.0

[Tom Lane]

Alvaro Herrera  writes:
> Excerpts from Andrew Dunstan's message of mié dic 15 02:08:24 -0300 2010:
>> That didn't work. But git bisect says it's this commit that's to blame:
>> 

> Hmm I wonder if this is reproducible in a non-Windows EXEC_BACKEND
> scenario.

I'm pretty sure I tried the no-flat-files code in that scenario while
writing it.  But it might be worth trying that again.  You'd think
though that if EXEC_BACKEND were sufficient to provoke it, all Windows
builds would fail.  I'm still mystified by what is the difference
between Andrew's non-working installation and working mingw builds.

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] hstores in pl/python

[Dimitri Fontaine]

Robert Haas  writes:
> Wrong probem.  What we need is a way to identify a type without
> knowing in advance what its OID is.  In other words, we need to
> distinguish between the hstore type that is shipped in contrib, and
> some stupid DBA who types "CREATE DOMAIN hstore as text".

Yeah, yeah. Now, what's wrong with the query I sent?

To ease discussion:

=# select objid
 from pg_extension_objects('hstore')
where class = 'pg_type'::regclass and objdesc ~ '(^|\.)hstore$';
 objid 
---
 16387
(1 row)

Regards,
-- 
Dimitri Fontaine
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] Default mode for shutdown

[Alvaro Herrera]

Excerpts from Robert Haas's message of mié dic 15 12:03:06 -0300 2010:

> Certainly, if you have an environment where people are mostly logging
> into the database directly (not through a connection pooler) and they
> do a few important queries and then disconnect, smart is a better
> default.  But if you have an environment where (for whatever reason)
> long-lasting connections are common, smart is worse than useless.

It occurs to me that we may need a new mode, which disconnects sessions
that are not in a transaction (or as soon as they are) but leaves
in-progress transactions alone; this could be the new default.  Of
course, this is much more difficult to implement than the current modes.

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] hstores in pl/python

[Tom Lane]

Robert Haas  writes:
> I was asking what would satisfy you as regards a reliable way to
> identify a type, not what you think we should do about this particular
> proposal.

Okay: a preassigned OID is safe.  I haven't seen any other safe
proposals.  Relying on a non-reserved name is transparently unsafe.

[ thinks for awhile ... ]  You could imagine having the hstore module
set up a rendezvous variable containing the OIDs of its type, its
I/O functions, and anything else plpython might need to know.  Except
that the hstore C code doesn't know those OIDs either, at least not
when first loaded.  There's also the problem that you don't really want
plpython's behavior suddenly changing when hstore happens to get loaded
or first used.

Another possibility is that you make the user tell you the
fully-qualified name of the type:

plpython.use_hstore = 'public.hstore'

Such a GUC would also fix the backwards compatibility issues, since
in the absence of a setting you'd continue to use the old behavior.
But other than that configurability angle, this seems pretty ugly.
Also you'd have to think about protecting yourself against a bad
setting, ie the GUC specifies a type that's not hstore.  That might
not be a big problem though, as long as you aren't directly messing
with the type's representation but just calling its I/O functions.

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] unlogged tables

[Robert Haas]

On Wed, Dec 15, 2010 at 4:20 AM, Simon Riggs  wrote:
> On Sat, 2010-11-13 at 19:16 -0500, Robert Haas wrote:
>
>> 3. The third patch (relax-sync-commit-v1) allows asynchronous commit
>> even when synchronous_commit=on if the transaction has not written
>> WAL.  Of course, a read-only transaction won't even have an XID and
>> therefore won't need a commit record, so what this is really doing is
>> allowing transactions that have written only to temp - or unlogged -
>> tables to commit asynchronously.
>
> I like this, great idea.
>
> Avoiding the commit record entirely will break Hot Standby though, since
> we rely on the assumption that all xids that are assigned are also
> logged. The xids would be "known assigned", yet since they never
> actually appear they will clog up the machinery (pun unintended).

Uggh, that's a really, really bad pun.

I made the same observation to Tom somewhere-or-other (must have been
a different thread because I don't see it on this one), along with the
further observation that we actually could suppress the commit record
entirely if wal_level < hot_standby, but I'm not sure there's enough
benefit to doing that to worry about the additional complexity.
Changing it from a foreground flush to a background flush already wins
so much that I don't really see the point of doing anything further.

-- 
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] Complier warnings on mingw gcc 4.5.0

[Alvaro Herrera]

Excerpts from Andrew Dunstan's message of mié dic 15 02:08:24 -0300 2010:
> 
> On 12/14/2010 12:42 PM, Tom Lane wrote:
> 
> > Another line of attack is that we know from the response packet that the
> > failure is being reported at guc.c:4794.  It would be really useful to
> > know what the call stack is there.  Could you change that elog to an
> > elog(PANIC) and get a stack trace from the ensuing core dump?
> >
> 
> That didn't work. But git bisect says it's this commit that's to blame:
> 

Hmm I wonder if this is reproducible in a non-Windows EXEC_BACKEND
scenario.

This bug seems closely related to process_postgres_switches.  I guess
it'd be useful to add some debugging printouts there to figure out
what's being passed the second time around.

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] hstores in pl/python

[Robert Haas]

On Wed, Dec 15, 2010 at 10:00 AM, Dimitri Fontaine
 wrote:
> Robert Haas  writes:
>> Well then we need a reliable way to identify a type.  What would satisfy you?
>
> An oid ?

Wrong probem.  What we need is a way to identify a type without
knowing in advance what its OID is.  In other words, we need to
distinguish between the hstore type that is shipped in contrib, and
some stupid DBA who types "CREATE DOMAIN hstore as text".

-- 
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] hstores in pl/python

[Florian Pflug]

On Dec15, 2010, at 02:14 , James William Pye wrote:
> On Dec 13, 2010, at 6:16 PM, Tom Lane wrote:
>> how do you identify which type OID is really hstore?
> 
> How about an identification field on pg_type?
> 
> CREATE TYPE hstore ..., IDENTIFIER 'org.postgresql.hstore';
> -- Where the "identifier" is an arbitrary string.

+1

I've wanted something like this a few times when dealing
with custom types within a client. A future protocol version
might even transmit these identifiers instead a the type's OID,
thereby removing the dependency on OID from clients entirely.

best regards,
Florian Pflug


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

Re: [HACKERS] hstores in pl/python

[Robert Haas]

On Wed, Dec 15, 2010 at 9:53 AM, Tom Lane  wrote:
> Robert Haas  writes:
>> Well then we need a reliable way to identify a type.  What would satisfy you?
>
> Either (1) do nothing (reject this whole proposal) or (2) put hstore
> in core where it will have a well-known OID.  While it would be nice to
> have some more-workable way to interconnect independent extensions,
> I feel no need to either design a solution to that on the spot, or to
> accept half-baked approaches to it.

I was asking what would satisfy you as regards a reliable way to
identify a type, not what you think we should do about this particular
proposal.

-- 
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] Crash safe visibility map vs hint bits

[Bruce Momjian]

Heikki Linnakangas wrote:
> On 04.12.2010 09:14, jes...@krogh.cc wrote:
> > There has been a lot discussion about index-only scans and how to make the 
> > visibillity map crash safe. Then followed by a good discussion about hint 
> > bits.
> >
> > What seems to be the main concern is the added wal volume and it makes me 
> > wonder if there is a way in-between that looks more like hint bits.
> >
> > How about lazily wal-log the complete visibility map say every X minutes or 
> > N amount of tuple updates and make the wal recovery jobs of rechecking 
> > visibility of pages touched by the wal stream on recovery.
> 
> If you WAL-log the visibility map changes after-the-fact, it doesn't 
> solve the race condition we're struggling with: the visibility map 
> change might hit the disk before the PD_ALL_VISIBLE to the heap page. If 
> you crash, you can end up with a situation where the PD_ALL_VISIBLE flag 
> on the heap page is not set, but the bit in the visibility map is. Which 
> causes serious issues later on.

Based on hacker emails and a discussion I had with Heikki while we were
in Germany, I have updated the index-only scans wiki to document a known
solution to making the visibility map crash-safe for use by index-only
scan use:


http://wiki.postgresql.org/wiki/Index-only_scans#Making_the_Visibility_Map_Crash-Safe

Making the Visibility Map Crash-Safe

Currently, a heap page that has all-visible tuples is marked by vacuum
as PD_ALL_VISIBLE and the visibility map (VM) bit is set. This is
currently unlogged, and a crash could require these to be set again.

The complexity is that for index-only scans, the VM bit has meaning, and
cannot be incorrectly set (though it can be incorrectly cleared because
that would just result in additional heap access). If both
PD_ALL_VISIBLE and the VM bit were to be set, and a crash resulted the
VM bit being written to disk, but not the PD_ALL_VISIBLE bit, a later
heap access that wrote a conditionally-visible row would not know to
clear the VM bit, causing incorrect results for index-only scans.

The solution is to WAL log the VM set bit activity. This will cause
full-page writes for the VM page, but this is much less than WAL-logging
each heap page because a VM page represents many heap pages. This
requires that the VM page not be written to disk until its VM-set WAL
record is fsynced to disk. Also, during crash recovering, reading the
VM-set WAL record would cause both the VM-set and heap PD_ALL_VISIBLE to
be set. 

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +

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

Re: [HACKERS] hstores in pl/python

[Jan Urbański]

On 15/12/10 15:38, Tom Lane wrote:
> Jan =?utf-8?B?VXJiYcWEc2tp?=  writes:
>> OK, here's another master plan:
> 
>> 1) hstore_plplython, when loaded, looks for a type called "hstore". If
> you created a "hstore" type that does not come from hstore.so, and you
> still load hstore_plpython, you deserve a segfault.
> 
> No, you don't.  I said upthread that relying on the name of the type was
> a nonstarter, and it still is.  For one thing, this sketch ignores
> search path issues.

Hm. I wa assuming that if you have a type called "hstore" that's not the
contrib hstore type, but you do install hstore_plpython from contrib,
then you can't expect it to work.

So how about this: hstore when loaded sets a rendezvous variable that
points to its I/O routines, called "org.postgresql.types.hstore".

hstore_plpython looks for that rendezvous variable instead of looking up
the type from the catalogs, and then sets a RVV called
"org.postgresql.parsers.hstore.plpython". The problem now is how
plpython is supposed to know if the object it gets is the same hstore,
and not some other type called hstore.

What would fix it, is if the hstore module could somehow know what OID
did the system assign to it, and would publish its I/O routines *and*
its OID as "org.postgresql.types.hstore". hstore_plpython would then
look for "org.postgresql.types.hstore" and set up
"org.postgresql.parsers.OID.plpython" and plpython would look for
"org.postgresql.parsers.plpython.".

It almost looks like we need a unique identifier for the extension type
that's known beforehand by the type writer (which
"org.postgresql.types.hstore" would be)

Cheers,
Jan

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

Re: [HACKERS] hstores in pl/python

[Tom Lane]

Robert Haas  writes:
> Well then we need a reliable way to identify a type.  What would satisfy you?

Either (1) do nothing (reject this whole proposal) or (2) put hstore
in core where it will have a well-known OID.  While it would be nice to
have some more-workable way to interconnect independent extensions,
I feel no need to either design a solution to that on the spot, or to
accept half-baked approaches to it.

regards, tom lane

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