[HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL

[Heikki Linnakangas]

Fujii Masao wrote:
> As I pointed out previously, the standby might restore a partially-filled
> WAL file that is being archived by the primary, and cause a FATAL error.
> And this happened in my box when I was testing the SR.
> 
>   sby [20088] FATAL:  archive file "00010087" has
> wrong size: 14139392 instead of 16777216
>   sby [20076] LOG:  startup process (PID 20088) exited with exit code 1
>   sby [20076] LOG:  terminating any other active server processes
>   act [18164] LOG:  received immediate shutdown request
> 
> If the startup process is in standby mode, I think that it should retry
> starting replication instead of emitting an error when it finds a
> partially-filled file in the archive. Then if the replication has been
> terminated, it has only to restore the archived file again. Thought?

Hmm, so after running restore_command, check the file size and if it's
too short, treat it the same as if restore_command returned non-zero?
And it will be retried on the next iteration. Works for me, though OTOH
it will then fail to complain about a genuinely WAL file that's
truncated for some reason. I guess there's no way around that, even if
you have a script as restore_command that does the file size check, it
will have the same problem.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

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

[HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL

[Fujii Masao]

On Thu, Jan 28, 2010 at 12:27 AM, Heikki Linnakangas
 wrote:
> Log Message:
> ---
> Make standby server continuously retry restoring the next WAL segment with
> restore_command, if the connection to the primary server is lost. This
> ensures that the standby can recover automatically, if the connection is
> lost for a long time and standby falls behind so much that the required
> WAL segments have been archived and deleted in the master.
>
> This also makes standby_mode useful without streaming replication; the
> server will keep retrying restore_command every few seconds until the
> trigger file is found. That's the same basic functionality pg_standby
> offers, but without the bells and whistles.

http://archives.postgresql.org/pgsql-hackers/2010-01/msg01520.php
http://archives.postgresql.org/pgsql-hackers/2010-01/msg02589.php

As I pointed out previously, the standby might restore a partially-filled
WAL file that is being archived by the primary, and cause a FATAL error.
And this happened in my box when I was testing the SR.

  sby [20088] FATAL:  archive file "00010087" has
wrong size: 14139392 instead of 16777216
  sby [20076] LOG:  startup process (PID 20088) exited with exit code 1
  sby [20076] LOG:  terminating any other active server processes
  act [18164] LOG:  received immediate shutdown request

If the startup process is in standby mode, I think that it should retry
starting replication instead of emitting an error when it finds a
partially-filled file in the archive. Then if the replication has been
terminated, it has only to restore the archived file again. Thought?

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

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

[HACKERS] pg_restore --single-transaction and --clean

[Takahiro Itagaki]

As another glitch in pg_restore, a combination of options
--single-transaction and --clean raises errors if we restore data
into an empty database. The reason is pg_restore uses DROP .
The cleanup command fails if the target object doesn't exist.

Is it a TODO item to replace "DROP" into "DROP IF EXISTS"
for cleanup commands in pg_restore?

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center


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

Re: [HACKERS] TRUNCATE+COPY optimization and --jobs=1 in pg_restore

[Takahiro Itagaki]

Andrew Dunstan  wrote:

> Takahiro-san is suggesting there is a case for doing the optimisation in 
> non-parallel mode. But if we do that, is there still a case for 
> --single-transaction?

I think --single-transaction is useful to restore data into non-empty
databases. A normal restore ignores errors, but it might make database
inconsistent state. So, we'd better keep --single-transaction option
to support all-or-nothing restore.

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center



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

Re: [HACKERS] TRUNCATE+COPY optimization and --jobs=1 in pg_restore

[Tom Lane]

Andrew Dunstan  writes:
> Tom Lane wrote:
>> The code is only trying to substitute for something you can't have
>> in parallel restore, ie --single-transaction.

> Exactly. IIRC that's why --single-transaction was introduced in the 
> first place.

To me, --single-transaction is mostly there for people who want to be
sure they have all-or-nothing restore behavior.  Optimizations are
secondary.

> Takahiro-san is suggesting there is a case for doing the optimisation in 
> non-parallel mode. But if we do that, is there still a case for 
> --single-transaction?

Yeah, per above.  The main problem I have with doing it in non-parallel
restore mode is that we couldn't safely do it when outputting to a
script (since we don't know if the user will try to put begin/end
around the script), and I really do not want to allow any differences
between script output and direct-to-database output.  Once that camel's
nose gets under the tent, debuggability will go down the tubes...

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] TRUNCATE+COPY optimization and --jobs=1 in pg_restore

[Andrew Dunstan]

Tom Lane wrote:

Takahiro Itagaki  writes:
  

We have an optimization to bulkload date in pg_restore, but the code
only works in parallel restore (--jobs >= 2). Why don't we do the
same optimization in the serial restore (--jobs = 1) ?



The code is only trying to substitute for something you can't have
in parallel restore, ie --single-transaction.


  


Exactly. IIRC that's why --single-transaction was introduced in the 
first place.


Takahiro-san is suggesting there is a case for doing the optimisation in 
non-parallel mode. But if we do that, is there still a case for 
--single-transaction?


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] TRUNCATE+COPY optimization and --jobs=1 in pg_restore

[Takahiro Itagaki]

Tom Lane  wrote:

> Takahiro Itagaki  writes:
> > We have an optimization to bulkload date in pg_restore, but the code
> > only works in parallel restore (--jobs >= 2). Why don't we do the
> > same optimization in the serial restore (--jobs = 1) ?
> 
> The code is only trying to substitute for something you can't have
> in parallel restore, ie --single-transaction.

Yeah, the comment says so. But it does not necessarily mean that
we cannot optimize the copy also in non-single-transaction restore.

The attached patch improve the judgment condition,
I'll add it to the next commit-fest.

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center



restore-wal-skip_20100210.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] Some belated patch review for "Buffers" explain analyze patch

[Robert Haas]

On Tue, Feb 9, 2010 at 10:51 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> On Tue, Feb 9, 2010 at 8:18 PM, Tom Lane  wrote:
>>> ... I wouldn't object to adding a "total time" field to the
>>> machine-readable formats.
>
>> One possibility we discussed previously is to add some decimal places
>> to the relevant values when nloops > 1.
>
> Hmm, I must've missed that conversation, but it seems like a possibly
> workable compromise.

http://archives.postgresql.org/pgsql-hackers/2009-05/msg01419.php

>> If we're going to add a total time field, I think we should add it to
>> both the machine-readable and human-readable formats.  I know it's a
>> little long-winded, but one of the things that I find really
>> unfortunate about the current format is that it's sometimes hard to
>> look at a plan tree and figure out where "the slow part" is, because
>> some things have been divided through by the number of loops.  Reading
>> through the JSON or YAML format to find the data is, I guess, better
>> than nothing, but only slightly: I frequently deal with plans that are
>> 25-30 lines long: in XML format, those will be 250-300 lines long.  I
>> wouldn't mind having to do EXPLAIN (ANALYZE, VERBOSE) or EXPLAIN
>> (ANALYZE, some-other-option) to get the details, but EXPLAIN (ANALYZE,
>> FORMAT XML) ... is not really a direction I want to go.
>
> I don't really buy this line of reasoning.  You don't want to read the
> XML format because it's too long, so your solution is to make the text
> format longer?

Yes.  We could add every bell and whistle imaginable to the text
format and it still would not begin to approach the verbosity of the
machine-readable formats.  Have you looked at them on a complex plan?
They are really, really long, and in many cases quite unreadable by
human beings.  That's OK, because that's not what they're for.  But do
I want a format this IS intended to be readable by human beings and
also contains all the relevant information?  Definitely.

...Robert

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

Re: [HACKERS] CVS checkout source code for different branches

[Andrew Dunstan]

M Z wrote:

Final, I tried:

$ cvs -z3 -d 
:pserver:anoncvs:passw...@anoncvs.postgresql.org:/projects/cvsroot co 
-r REL8_3_STABLE -P pgsql


Checked file configure.in . Around line 20, there 
is line:
 AC_INIT([PostgreSQL], [8.3.9], [pgsql-b...@postgresql.org 
])


Not sure that indicates version 8.3.9?

But still, is there a testing/developing version for 8.3? I remember 
Alvaro made a patch to contrib/xml2/xpath.c. What is that version? and 
How can get this version? Is there a way I can list all branches / 
versions in CVS server?




I've already told you how to see all the tags/branches - use the "cvs 
log" command on a longstanding file like "configure.in".


REL8_3_STABLE is the branch to develop against for the 8.3 series of 
releases. But unless you're fixing a bug it's pointless - we never add 
features to the stable branches, we only make bug fixes.  And for this 
purpose bugs are defined fairly conservatively. All feature work should 
normally be done against HEAD.


What exactly are you wanting to do with the back branches?

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] Some belated patch review for "Buffers" explain analyze patch

[Tom Lane]

Robert Haas  writes:
> On Tue, Feb 9, 2010 at 8:18 PM, Tom Lane  wrote:
>> ... I wouldn't object to adding a "total time" field to the
>> machine-readable formats.

> One possibility we discussed previously is to add some decimal places
> to the relevant values when nloops > 1.

Hmm, I must've missed that conversation, but it seems like a possibly
workable compromise.

> If we're going to add a total time field, I think we should add it to
> both the machine-readable and human-readable formats.  I know it's a
> little long-winded, but one of the things that I find really
> unfortunate about the current format is that it's sometimes hard to
> look at a plan tree and figure out where "the slow part" is, because
> some things have been divided through by the number of loops.  Reading
> through the JSON or YAML format to find the data is, I guess, better
> than nothing, but only slightly: I frequently deal with plans that are
> 25-30 lines long: in XML format, those will be 250-300 lines long.  I
> wouldn't mind having to do EXPLAIN (ANALYZE, VERBOSE) or EXPLAIN
> (ANALYZE, some-other-option) to get the details, but EXPLAIN (ANALYZE,
> FORMAT XML) ... is not really a direction I want to go.

I don't really buy this line of reasoning.  You don't want to read the
XML format because it's too long, so your solution is to make the text
format longer?

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] [CFReview] Red-Black Tree

[Robert Haas]

2010/2/9 Teodor Sigaev :
>>> Good idea, implemented.
>>
>> Hmm.  I think your implementation is prone to overflow in two places -
>> both when computing step, and also when stepping through the array.
>
> Pls, point me, I don't see that
> !       step |= (step >>  1);
> !       step |= (step >>  2);
> !       step |= (step >>  4);
> !       step |= (step >>  8);
> !       step |= (step >> 16);

So suppose at this point that step is the largest integer that can be
represented...

> !       step ++;

Boom.

> !       step >>= 1;
> !
> !       while(step > 0) {
> !               int i;
>
> !               for (i = step-1; i < nentry; i += 2 * step)

And similarly here... if nentry is greater than maxint/2, then i += 2
* step will overflow, no?

> !                       ginInsertEntry(accum, heapptr, attnum, entries[i]);
>
> !               step >>= 1; /* /2 */
> !       }
>
>
>> Proposed revision attached, with also some rewriting of the comment
>> for that function.
>
> make check fails with your patch:
>
> #3  0x083d2b50 in ExceptionalCondition (conditionName=Could not find the
> frame base for "ExceptionalCondition".
> ) at assert.c:57
> #4  0x081086b6 in ginAppendData (old=0x287f2030, new=0x287f2044,
> arg=0xbfbfd5e4) at ginbulk.c:48
> #5  0x083f5632 in rb_insert (rb=0x2acfe610, data=0x287f2044) at rbtree.c:359
> #6  0x08108968 in ginInsertEntry (accum=0xbfbfd5e4, heapptr=0x28711af4,
> attnum=1, entry=2139062143) at ginbulk.c:135
> #7  0x08108ad9 in ginInsertRecordBA (accum=0xbfbfd5e4, heapptr=0x28711af4,
> attnum=1, entries=0x2ac77068, nentry=6) at ginbulk.c:202

Gaah, sorry.  Presumably I'm running off the end of the array, though
I don't see what I did wrong.  My brain is fried.

...Robert

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

Re: [HACKERS] CVS checkout source code for different branches

[M Z]

Thanks Robert,

Your reply helps a lot. Just right after sending post, I found the way to
list all branches in the CVS server.

Best,
M Z

On Tue, Feb 9, 2010 at 10:43 PM, Robert Haas  wrote:

> On Tue, Feb 9, 2010 at 10:35 PM, M Z  wrote:
> > Final, I tried:
> >
> > $ cvs -z3 -d
> > :pserver:anoncvs:passw...@anoncvs.postgresql.org:/projects/cvsroot co -r
> > REL8_3_STABLE -P pgsql
> >
> > Checked file configure.in. Around line 20, there is line:
> >  AC_INIT([PostgreSQL], [8.3.9], [pgsql-b...@postgresql.org])
> >
> > Not sure that indicates version 8.3.9?
> >
> > But still, is there a testing/developing version for 8.3?
>
> REL8_3_STABLE is the head of the stable branch for 8.3.  It says 8.3.9
> right now because 8.3.9 is the last release that was stamped off that
> branch, but what you checked out actually has any changes backpatched
> since then on it as well.
>
> > I remember Alvaro
> > made a patch to contrib/xml2/xpath.c. What is that version? and How can
> get
> > this version?
>
> That patch hasn't been applied yet.  You have to download it from his
> message and apply it to your local copy using the "patch" program.
>
> > Is there a way I can list all branches / versions in CVS server?
>
> Andrew already answered that question in his previous message: use cvs
> log on a file that's been around for a long time, like configure.in.
>
> It sounds like you need to find some documentation for CVS and read
> it, and/or find a CVS help mailing list and post to it.
>
> ...Robert
>

Re: [HACKERS] TRUNCATE+COPY optimization and --jobs=1 in pg_restore

[Tom Lane]

Takahiro Itagaki  writes:
> We have an optimization to bulkload date in pg_restore, but the code
> only works in parallel restore (--jobs >= 2). Why don't we do the
> same optimization in the serial restore (--jobs = 1) ?

The code is only trying to substitute for something you can't have
in parallel restore, ie --single-transaction.

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] CVS checkout source code for different branches

[Robert Haas]

On Tue, Feb 9, 2010 at 10:35 PM, M Z  wrote:
> Final, I tried:
>
> $ cvs -z3 -d
> :pserver:anoncvs:passw...@anoncvs.postgresql.org:/projects/cvsroot co -r
> REL8_3_STABLE -P pgsql
>
> Checked file configure.in. Around line 20, there is line:
>  AC_INIT([PostgreSQL], [8.3.9], [pgsql-b...@postgresql.org])
>
> Not sure that indicates version 8.3.9?
>
> But still, is there a testing/developing version for 8.3?

REL8_3_STABLE is the head of the stable branch for 8.3.  It says 8.3.9
right now because 8.3.9 is the last release that was stamped off that
branch, but what you checked out actually has any changes backpatched
since then on it as well.

> I remember Alvaro
> made a patch to contrib/xml2/xpath.c. What is that version? and How can get
> this version?

That patch hasn't been applied yet.  You have to download it from his
message and apply it to your local copy using the "patch" program.

> Is there a way I can list all branches / versions in CVS server?

Andrew already answered that question in his previous message: use cvs
log on a file that's been around for a long time, like configure.in.

It sounds like you need to find some documentation for CVS and read
it, and/or find a CVS help mailing list and post to it.

...Robert

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

Re: [HACKERS] Some belated patch review for "Buffers" explain analyze patch

[Robert Haas]

On Tue, Feb 9, 2010 at 8:18 PM, Tom Lane  wrote:
>> Perhaps instead of looking to change the "actual" times we should look
>> at a way to include total time spent in each node.
>
> You can already get that by multiplying the displayed total time by the
> number of loops.  Robert does have a point that this is subject to a lot
> of roundoff error, though, when the per-loop time is much less than 1
> msec.  I wouldn't object to adding a "total time" field to the
> machine-readable formats.

One possibility we discussed previously is to add some decimal places
to the relevant values when nloops > 1.

If we're going to add a total time field, I think we should add it to
both the machine-readable and human-readable formats.  I know it's a
little long-winded, but one of the things that I find really
unfortunate about the current format is that it's sometimes hard to
look at a plan tree and figure out where "the slow part" is, because
some things have been divided through by the number of loops.  Reading
through the JSON or YAML format to find the data is, I guess, better
than nothing, but only slightly: I frequently deal with plans that are
25-30 lines long: in XML format, those will be 250-300 lines long.  I
wouldn't mind having to do EXPLAIN (ANALYZE, VERBOSE) or EXPLAIN
(ANALYZE, some-other-option) to get the details, but EXPLAIN (ANALYZE,
FORMAT XML) ... is not really a direction I want to go.

...Robert

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

Re: [HACKERS] CVS checkout source code for different branches

[M Z]

Final, I tried:

$ cvs -z3 -d :pserver:anoncvs:passw...@anoncvs.postgresql.org:/projects/cvsroot
co -r REL8_3_STABLE -P pgsql

Checked file configure.in. Around line 20, there is line:
 AC_INIT([PostgreSQL], [8.3.9], [pgsql-b...@postgresql.org])

Not sure that indicates version 8.3.9?

But still, is there a testing/developing version for 8.3? I remember Alvaro
made a patch to contrib/xml2/xpath.c. What is that version? and How can get
this version? Is there a way I can list all branches / versions in CVS
server?


***
Index: contrib/xml2/xpath.c
==
=
RCS file: /home/alvherre/Code/cvs/pgsql/contrib/xml2/xpath.c,v
retrieving revision 1.16.2.1
diff -c -p -r1.16.2.1 xpath.c
*** contrib/xml2/xpath.c26 Mar 2008 01:19:11 -  1.16.2.1
--- contrib/xml2/xpath.c27 Jan 2010 15:30:56 -
*** xpath_table(PG_FUNCTION_ARGS)
*** 793,798 
--- 793,801 
  */
   pgxml_parser_init();

+   PG_TRY();
+   {
+
   /* For each row i.e. document returned from SPI */
   for (i = 0; i < proc; i++)
   {
*** xpath_table(PG_FUNCTION_ARGS)
*** 929,934 
--- 932,944 
   if (xmldoc)
   pfree(xmldoc);
   }
+   }
+   PG_CATCH();
+   {
+   xmlCleanupParser();
+   PG_RE_THROW();
+   }
+   PG_END_TRY();

   xmlCleanupParser();
 /* Needed to flag completeness in 7.3.1. 7.4 defines it as a no-op. */
***

[HACKERS] TRUNCATE+COPY optimization and --jobs=1 in pg_restore

[Takahiro Itagaki]

We have an optimization to bulkload date in pg_restore, but the code
only works in parallel restore (--jobs >= 2). Why don't we do the
same optimization in the serial restore (--jobs = 1) ?

We checks is_parallel to decide to use BEGIN-TRUNCATE-COPY:
if (is_parallel && te->created)
but we can always do it unless --single-transaction, right?
if (!ropt->single_txn && te->created)


[ in restore_toc_entry() ]
/*
 * In parallel restore, if we created the table earlier in
 * the run then we wrap the COPY in a transaction and
 * precede it with a TRUNCATE. If archiving is not on
 * this prevents WAL-logging the COPY. This obtains a
 * speedup similar to that from using single_txn mode in
 * non-parallel restores.
 */
if (is_parallel && te->created) < HERE
{
/*
 * Parallel restore is always talking directly to a
 * server, so no need to see if we should issue BEGIN.
 */
StartTransaction(AH);

/*
 * If the server version is >= 8.4, make sure we issue
 * TRUNCATE with ONLY so that child tables are not
 * wiped.
 */
ahprintf(AH, "TRUNCATE TABLE %s%s;\n\n",
 (PQserverVersion(AH->connection) >= 80400 ?
  "ONLY " : ""),
 fmtId(te->tag));
}


Regards,
---
Takahiro Itagaki
NTT Open Source Software Center


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

Re: [HACKERS] CVS checkout source code for different branches

[M Z]

Hi Andrew and all,

I am still struggling with the branch repositories. I tried to checkout from
branch repositories but got error no such repositories:

$ cvs -z3 -d  
:pserver:anoncvs:passw...@anoncvs.postgresql.org:/projects/REL8_4_STABLE
co -P pgsql
/projects/REL8_4_STABLE: no such repository

$ cvs -z3 -d 
:pserver:anoncvs:passw...@anoncvs.postgresql.org:/projects/REL8_3_STABLE
co -P pgsql
/projects/REL8_3_STABLE: no such repository

$ cvs -z3 -d :pserver:anoncvs:passw...@anoncvs.postgresql.org:/REL8_4_STABLE
co -P pgsql
/REL8_4_STABLE: no such repository

$ cvs -z3 -d :pserver:anoncvs:passw...@anoncvs.postgresql.org:/REL8_3_STABLE
co -P pgsql
/REL8_3_STABLE: no such repository

What are the correct repositories for these branch? Is there a way I can
list these branch repositories and how?

Many thanks,
M Z

On Mon, Feb 8, 2010 at 11:56 PM, Andrew Dunstan  wrote:

>
> The only sane things to check out apart from HEAD are normally the STABLE
> branches. For release m.n those are always called RELm_n_STABLE. You can
> also get the tag set for a specific release. Those are called RELm_n_o for
> m.n.o releases.
>
> If you look at the output for "cvs log configure.in" you can see near the
> top a list of tag sets under the heading "symbolic names".
>
> HTH.
>
>
>
> M Z wrote:
>
>> For example, how can I list all the branches for postgresql 8.3 (and 8.4)?
>> Now I can checkout code using:
>> cvs -z3 -d :pserver:anoncvs:passw...@anoncvs.postgresql.org:/projects/cvsroot
>> co -P pgsql
>>
>> But I don't know when version it is, and I want get code from some
>> postgresql 8.3 and 8.4 branches but I don't know the their cvsroot
>>
>> Thanks
>> M Z
>>
>> On Mon, Feb 8, 2010 at 11:04 PM, M Z > jm80...@gmail.com>> wrote:
>>
>>Hi Andrew,
>>
>>Could you please give a little more detail how I can find
>>different CVS branches?
>>
>>Thanks,
>>M Z
>>
>>
>>
>>On Mon, Feb 8, 2010 at 10:38 PM, Andrew Dunstan
>>mailto:and...@dunslane.net>> wrote:
>>
>>
>>
>>M Z wrote:
>>
>>Hi,
>>
>>I am trying to checkout code from different branches (such
>>as 8.3, 8.4).
>>
>>I found a few ways to checkout code from CVS:
>>1. webCVS: http://anoncvs.postgresql.org/cvsweb.cgi/
>>
>>2. cvs -z3 -d
>>:pserver:anoncvs:passw...@anoncvs.postgresql.org:
>> /projects/cvsroot
>>co -P pgsql
>>
>>3. $ rsync --progress -avzCH --delete
>>anoncvs.postgresql.org::pgsql-cvs $CVSROOT
>>
>>However, how can I checkout code from different branches
>>(such as 8.3, 8.4)?
>>
>>
>>
>>CVS is documented here, among other places:
>>
>>
>>To check out a particular branch such as REL8_4_STABLE, use
>>the -r option on the checkout command:
>>
>>  cvs checkout -r branchname
>>
>>cheers
>>
>>andrew
>>
>>
>>
>>
>>
>>
>>

Re: [HACKERS] Largeobject Access Controls (r2460)

[Takahiro Itagaki]

KaiGai Kohei  wrote:

> The attached patch fixed up the cleanup query as follows:
> +   appendPQExpBuffer(dquery,
> + "SELECT pg_catalog.lo_unlink(oid) "
> + "FROM pg_catalog.pg_largeobject_metadata "
> + "WHERE oid = %s;\n", binfo->dobj.name);
>
> And, I also noticed that lo_create() was not prefixed by "pg_catalog.",
> so I also add it.

Thanks. Now the patch is ready to commit.

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center



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

[HACKERS] psql tab-completion for new syntax

[Takahiro Itagaki]

We've added some new syntax in HEAD, but psql tab-completion is out of sync.
Here is a patch to support the following syntax in tab-completion:

- top-level DO
- ALTER TABLE/INDEX/TABLESPACE SET/RESET with options
- ALTER TABLE ALTER COLUMN SET/RESET with options
- CREATE TRIGGER with events

The fix is not a stopper to alpha release, but I'd like to
add it before beta release. Comments welcome.

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center


psql-tab-completion_20100210.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] psql tab completion for DO blocks

[Takahiro Itagaki]

Bruce Momjian  wrote:

> Where are we on this patch?  We should at least implement the completion
> for 'LANGUAGE' in 'DO', and use the existing pg_language query for
> completion.  I am attaching a patch that does exactly this.

I don't think we need the patch except adding DO to the top-level sql_commands.

Syntax of DO command is:
DO code [ LANGUAGE lang_name ]
We need 'code' before LANGUAGE, but the patch completes "DO" to "DO LANGUAGE".
It will be just a syntax error.

Also, we've already had a completion after LANGUAGE (see words_after_create),
so we don't need an additional Query_for_list_of_languages after LANGUAGE.


BTW, I'm working on psql tab-completion adjustment for new syntax in 9.0.
I'll send it soon.

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center



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

Re: [HACKERS] Listen / Notify - what to do when the queue is full

[Tom Lane]

Alvaro Herrera  writes:
> Andrew Dunstan wrote:
>> True. It's a workaround, but I think it's acceptable at this stage.
>> We need to get some experience with this facility before we can
>> refine it.

> Hmm?  If we decide now that it's not going to have encoding conversion,
> we won't able to change it later.

How so?  If the feature currently allows only ASCII data, the behavior
would be upward compatible with a future version that is able to accept
and convert non-ASCII characters.

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] Listen / Notify - what to do when the queue is full

[Alvaro Herrera]

Andrew Dunstan wrote:
> 
> 
> Tom Lane wrote:
> >Andrew Dunstan  writes:
> >>Jeff Davis wrote:
> >>>So, I think ASCII is the natural choice here.
> >
> >>It's not worth hanging up this facility over this issue, ISTM.
> >>If we want something more that ASCII then a base64 or hex
> >>encoded string could possibly meet the need in the first
> >>instance.
> >
> >Yeah, that would serve people who want to push either binary or
> >non-ASCII data through the pipe.  It would leave all risks of encoding
> >problems on the user's head, though.
> 
> True. It's a workaround, but I think it's acceptable at this stage.
> We need to get some experience with this facility before we can
> refine it.

Hmm?  If we decide now that it's not going to have encoding conversion,
we won't able to change it later.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

Re: [HACKERS] Listen / Notify - what to do when the queue is full

[Andrew Dunstan]

Tom Lane wrote:

Andrew Dunstan  writes:
  

Jeff Davis wrote:


So, I think ASCII is the natural choice here.
  


  
It's not worth hanging up this facility over this issue, ISTM. If we 
want something more that ASCII then a base64 or hex encoded string could 
possibly meet the need in the first instance.



Yeah, that would serve people who want to push either binary or
non-ASCII data through the pipe.  It would leave all risks of encoding
problems on the user's head, though.


  


True. It's a workaround, but I think it's acceptable at this stage. We 
need to get some experience with this facility before we can refine it.


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] Listen / Notify - what to do when the queue is full

[Tom Lane]

Andrew Dunstan  writes:
> Jeff Davis wrote:
>> So, I think ASCII is the natural choice here.

> It's not worth hanging up this facility over this issue, ISTM. If we 
> want something more that ASCII then a base64 or hex encoded string could 
> possibly meet the need in the first instance.

Yeah, that would serve people who want to push either binary or
non-ASCII data through the pipe.  It would leave all risks of encoding
problems on the user's head, though.

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] Writeable CTEs and empty relations

[Tom Lane]

Marko Tiikkaja  writes:
> On 2010-02-10 02:19 +0200, Tom Lane wrote:
>> You still haven't explained why it's a good idea to change the snapshot
>> after the executor has started.  Right at the moment I'm prepared to
>> reject the patch on that ground alone.

> The patch only touches the snapshot's curcid.  That's needed to allow
> the queries see the tuples of the DML WITHs ran before that particular
> query.

... and they will also see, for example, tuples inserted by triggers
fired by those queries.  I thought the plan for this feature was to
store and re-read the RETURNING data, not to go back and re-read the
underlying tables.

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] Some belated patch review for "Buffers" explain analyze patch

[Tom Lane]

Greg Stark  writes:
> On Wed, Feb 10, 2010 at 12:32 AM, Tom Lane  wrote:
>> The reason that EXPLAIN prints things the way it does is so that actual
>> costs/times are comparable to estimated costs.

> Oh, that was a thought I had along the way but forgot to mention in my
> email: since the buffer usage isn't related to the cost there isn't
> nearly the impetus to divide by loops except to be consistent with the
> time.

True.  BTW I realized that I was confusing the buffer-usage issue with
the recent changes to add printing of hash join execution stats.
I believe offhand that for both that and the Sort statistics, what
you see is really just stats for the *last* execution of the node,
if it got executed more than once.  While that's incomplete, at least
it is somewhat consistent --- you won't see numbers that are stated
on a completely different basis from those around them.

We'd have to think about whether and how to adjust the sort and hashjoin
stats if we wanted to switch over to whole-query totals.

> Perhaps instead of looking to change the "actual" times we should look
> at a way to include total time spent in each node.

You can already get that by multiplying the displayed total time by the
number of loops.  Robert does have a point that this is subject to a lot
of roundoff error, though, when the per-loop time is much less than 1
msec.  I wouldn't object to adding a "total time" field to the
machine-readable formats.

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] TCP keepalive support for libpq

[Fujii Masao]

On Tue, Feb 9, 2010 at 11:34 PM, Andrew Chernow  wrote:
> If you really want libpq to manage this, I think you need to expose the
> probe interval and timeouts.

Agreed.

Previously I was making the patch that exposes them as conninfo
options so that the standby can detect a network outage ASAP in SR.
I attached that WIP patch as a reference. Hope this helps.

Regards,

-- 
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c
index 31ee680..2687827 100644
--- a/src/interfaces/libpq/fe-connect.c
+++ b/src/interfaces/libpq/fe-connect.c
@@ -180,6 +180,15 @@ static const PQconninfoOption PQconninfoOptions[] = {
 	{"fallback_application_name", NULL, NULL, NULL,
 	"Fallback-Application-Name", "", 64},
 
+	{"keepalives_idle", "PGKEEPALIVESIDLE", NULL, NULL,
+	"TCP-Keepalive-Idle", "", 10}, /* strlen(INT32_MAX) == 10 */
+
+	{"keepalives_interval", "PGKEEPALIVESINTERVAL", NULL, NULL,
+	"TCP-Keepalive-Interval", "", 10}, /* strlen(INT32_MAX) == 10 */
+
+	{"keepalives_count", "PGKEEPALIVESCOUNT", NULL, NULL,
+	"TCP-Keepalive-Count", "", 10}, /* strlen(INT32_MAX) == 10 */
+
 #ifdef USE_SSL
 
 	/*
@@ -452,6 +461,12 @@ connectOptions1(PGconn *conn, const char *conninfo)
 	conn->pgpass = tmp ? strdup(tmp) : NULL;
 	tmp = conninfo_getval(connOptions, "connect_timeout");
 	conn->connect_timeout = tmp ? strdup(tmp) : NULL;
+	tmp = conninfo_getval(connOptions, "keepalives_idle");
+	conn->keepalives_idle = tmp ? strdup(tmp) : NULL;
+	tmp = conninfo_getval(connOptions, "keepalives_interval");
+	conn->keepalives_interval = tmp ? strdup(tmp) : NULL;
+	tmp = conninfo_getval(connOptions, "keepalives_count");
+	conn->keepalives_count = tmp ? strdup(tmp) : NULL;
 	tmp = conninfo_getval(connOptions, "sslmode");
 	conn->sslmode = tmp ? strdup(tmp) : NULL;
 	tmp = conninfo_getval(connOptions, "sslkey");
@@ -809,6 +824,114 @@ connectFailureMessage(PGconn *conn, int errorno)
 }
 
 
+static int
+setKeepalivesIdle(PGconn *conn)
+{
+	int	idle;
+
+	if (conn->keepalives_idle == NULL || IS_AF_UNIX(conn->laddr.addr.ss_family))
+		return 1;
+
+	idle = atoi(conn->keepalives_idle);
+	if (idle < 0)
+		idle = 0;
+
+#ifdef TCP_KEEPIDLE
+	if (setsockopt(conn->sock, IPPROTO_TCP, TCP_KEEPIDLE,
+   (char *) &idle, sizeof(idle)) < 0)
+	{
+		char	sebuf[256];
+
+		appendPQExpBuffer(&conn->errorMessage,
+		  libpq_gettext("setsockopt(TCP_KEEPIDLE) failed: %s\n"),
+		  SOCK_STRERROR(SOCK_ERRNO, sebuf, sizeof(sebuf)));
+		return 0;
+	}
+#else
+	if (idle != 0)
+	{
+		appendPQExpBuffer(&conn->errorMessage,
+		  libpq_gettext("setsockopt(TCP_KEEPIDLE) not supported"));
+		return 0;
+	}
+#endif
+
+	return 1;
+}
+
+
+static int
+setKeepalivesInterval(PGconn *conn)
+{
+	int	interval;
+
+	if (conn->keepalives_interval == NULL || IS_AF_UNIX(conn->laddr.addr.ss_family))
+		return 1;
+
+	interval = atoi(conn->keepalives_interval);
+	if (interval < 0)
+		interval = 0;
+
+#ifdef TCP_KEEPINTVL
+	if (setsockopt(conn->sock, IPPROTO_TCP, TCP_KEEPINTVL,
+   (char *) &interval, sizeof(interval)) < 0)
+	{
+		char	sebuf[256];
+
+		appendPQExpBuffer(&conn->errorMessage,
+		  libpq_gettext("setsockopt(TCP_KEEPINTVL) failed: %s\n"),
+		  SOCK_STRERROR(SOCK_ERRNO, sebuf, sizeof(sebuf)));
+		return 0;
+	}
+#else
+	if (interval != 0)
+	{
+		appendPQExpBuffer(&conn->errorMessage,
+		  libpq_gettext("setsockopt(TCP_KEEPINTVL) not supported"));
+		return 0;
+	}
+#endif
+
+	return 1;
+}
+
+
+static int
+setKeepalivesCount(PGconn *conn)
+{
+	int	count;
+
+	if (conn->keepalives_count == NULL || IS_AF_UNIX(conn->laddr.addr.ss_family))
+		return 1;
+
+	count = atoi(conn->keepalives_count);
+	if (count < 0)
+		count = 0;
+
+#ifdef TCP_KEEPCNT
+	if (setsockopt(conn->sock, IPPROTO_TCP, TCP_KEEPCNT,
+   (char *) &count, sizeof(count)) < 0)
+	{
+		char	sebuf[256];
+
+		appendPQExpBuffer(&conn->errorMessage,
+		  libpq_gettext("setsockopt(TCP_KEEPCNT) failed: %s\n"),
+		  SOCK_STRERROR(SOCK_ERRNO, sebuf, sizeof(sebuf)));
+		return 0;
+	}
+#else
+	if (count != 0)
+	{
+		appendPQExpBuffer(&conn->errorMessage,
+		  libpq_gettext("setsockopt(TCP_KEEPCNT) not supported"));
+		return 0;
+	}
+#endif
+
+	return 1;
+}
+
+
 /* --
  * connectDBStart -
  *		Begin the process of making a connection to the backend.
@@ -1157,8 +1280,8 @@ keep_going:		/* We will come back to here until there is
 
 	/*
 	 * Select socket options: no delay of outgoing data for
-	 * TCP sockets, nonblock mode, close-on-exec. Fail if any
-	 * of this fails.
+	 * TCP sockets, nonblock mode, close-on-exec and keepalives.
+	 * Fail if any of this fails.
 	 */
 	if (!IS_AF_UNIX(addr_cur->ai_family))
 	{
@@ -1194,6 +1317,32 @@ keep_going:		/* We will come back to here until there is
 	}
 #endif   /* F_SETFD */
 
+	if (!IS_AF_UNIX(conn->laddr.addr.ss_family))
+	{
+		int	on;
+
+		on = 1;
+		if (setsockopt(

Re: [HACKERS] Listen / Notify - what to do when the queue is full

[Andrew Dunstan]

Jeff Davis wrote:

Also, the query starts out as text, so we need a way to interpret the
text in an encoding-independent way.

So, I think ASCII is the natural choice here.


  


It's not worth hanging up this facility over this issue, ISTM. If we 
want something more that ASCII then a base64 or hex encoded string could 
possibly meet the need in the first instance.


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] Some belated patch review for "Buffers" explain analyze patch

[Greg Stark]

On Wed, Feb 10, 2010 at 12:32 AM, Tom Lane  wrote:
> The reason that EXPLAIN prints things the way it does is so that actual
> costs/times are comparable to estimated costs.

Oh, that was a thought I had along the way but forgot to mention in my
email: since the buffer usage isn't related to the cost there isn't
nearly the impetus to divide by loops except to be consistent with the
time.

Another point is that changing the actual times to report total times
doesn't actually make much sense either. Total time to produce the
*first* record is pretty meaningless for example.

Perhaps instead of looking to change the "actual" times we should look
at a way to include total time spent in each node.

I had been experimenting with using getrusage to get more profiling
data. It also makes little sense to divide by loops since again it's
all stuff that makes sense to compare with outside data sources and
little sense to compare with the estimated costs. Perhaps we could add
the existing wall clock time to this (after pruning things like nivcsw
and minflt etc once we know what's not useful.)

postgres=# explain (analyze,buffers,resource) select * from i;
   QUERY PLAN

---
-
 Seq Scan on i  (cost=0.00..63344.86 rows=2399986 width=101) (actual
time=0.104..4309.997 rows=240 loops=1)
   Buffers: shared hit=256kB read=307.1MB blocking=392kB
   Resource Usage: user time=656.042 system time=3252.197 read=2.859MB
nvcsw=63 nivcsw=173 minflt=65
 Total runtime: 7881.809 ms



-- 
greg

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

Re: [HACKERS] Avoiding bad prepared-statement plans.

[Kris Jurka]

On Tue, 9 Feb 2010, Mark Mielke wrote:

In a current commercial app we have that uses JDBC and prepared plans for 
just about everything, it regularly ends up with execution times of 30+ 
milliseconds when a complete plan + execute would take less than 1 
millisecond.


PostgreSQL planning is pretty fast. In terms of not over thinking things - I 
think I would even prefer an option that said "always re-plan prepared 
statements" as a starting point. If it happened to become smarter over time, 
such that it would have invalidation criteria that would trigger a re-plan, 
that would be awesome, but in terms of what would help me *today* - being 
able to convert prepared plans into just a means to use place holders would 
help me today on certain real applications in production use right now.




The JDBC driver has two methods of disabling permanently planned prepared 
statements:


1) Use the version two frontend/backend protocol via adding 
protocolVersion=2 to your URL.  This interpolates all parameters into 
the query on the client side.


2) Execute PreparedStatements using the unnamed statement rather than a 
named statement via adding prepareThreshold=0 to your URL.  A named 
statement is expected to be re-used for later execution and ignores the 
passed parameters for planning purposes.  An unnamed statement may be 
re-used, but it doesn't expect to be.  The unnamed statement uses the 
passed parameters for planning purposes, but still cannot make certain 
optimatizations based on the parameter values because it may be 
re-executed again later with different parameters.  For example a LIKE 
query with a parameter value of 'ABC%' cannot be transformed into range 
query because the next execution may use a different parameter value for 
which the transform is not valid.  By default the driver switches to using 
a named statement after the same PreparedStatement object is executed five 
times.


http://jdbc.postgresql.org/documentation/84/connect.html#connection-parameters
http://jdbc.postgresql.org/documentation/84/server-prepare.html

Kris Jurka

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

Re: [HACKERS] Largeobject Access Controls (r2460)

[KaiGai Kohei]

(2010/02/09 21:18), KaiGai Kohei wrote:
> (2010/02/09 20:16), Takahiro Itagaki wrote:
>>
>> KaiGai Kohei wrote:
>>
 I don't think this is necessarily a good idea. We might decide to treat
 both things separately in the future and it having them represented
 separately in the dump would prove useful.
>>>
>>> I agree. From design perspective, the single section approach is more
>>> simple than dual section, but its change set is larger than the dual.
>>
>> OK.
>>
>>
>> When I tested a custom dump with pg_restore, --clean& 
>> --single-transaction
>> will fail with the new dump format because it always call lo_unlink()
>> even if the large object doesn't exist. It comes from dumpBlobItem:
>>
>> ! dumpBlobItem(Archive *AH, BlobInfo *binfo)
>> ! appendPQExpBuffer(dquery, "SELECT lo_unlink(%s);\n", binfo->dobj.name);
>>
>> The query in DropBlobIfExists() could avoid errors -- should we use it 
>> here?
>> | SELECT lo_unlink(oid) FROM pg_largeobject_metadata WHERE oid = %s;
> 
> Yes, we can use this query to handle --clean option.
> I'll fix it soon.

The attached patch fixed up the cleanup query as follows:

+   appendPQExpBuffer(dquery,
+ "SELECT pg_catalog.lo_unlink(oid) "
+ "FROM pg_catalog.pg_largeobject_metadata "
+ "WHERE oid = %s;\n", binfo->dobj.name);

And, I also noticed that lo_create() was not prefixed by "pg_catalog.",
so I also add it.

Rest of parts were not changed.

Thanks,


>> BTW, --clean option is ambiguous if combined with --data-only. Restoring
>> large objects fails for the above reason if previous objects don't exist,
>> but table data are restored *without* truncation of existing data. Will
>> normal users expect TRUNCATE-before-load for --clean& --data-only cases?
>>
>> Present behaviors are;
>> Table data - Appended. (--clean is ignored)
>> Large objects - End with an error if object doesn't exist.
>> IMO, ideal behaviors are:
>> Table data - Truncate existing data and load new ones.
>> Large objects - Work like as MERGE (or REPLACE, UPSERT).
>>
>> Comments?
> 
> In the existing "BLOBS" section, it creates and restores large objects 
> in same time. And, it also unlink existing large object (if exists)
> just before restoring them, when --clean is given.
> 
> In my opinion, when --clean is given, it also should truncate the table
> before restoring, even if --data-only is co-given.
> 
> Thanks,


-- 
OSS Platform Development Division, NEC
KaiGai Kohei 


pgsql-fix-pg_dump-blob-privs.7.patch
Description: application/octect-stream

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

Re: [HACKERS] Some belated patch review for "Buffers" explain analyze patch

[Tom Lane]

Robert Haas  writes:
> On Tue, Feb 9, 2010 at 6:33 PM, Tom Lane  wrote:
>> Not in the least.  Fixing EXPLAIN to consistently print totals would
>> involve changes in (at least) the treatment of estimated costs, and very
>> possibly some changes in the Instrumentation support as well.

> As far as I am aware there is one place (in ExplainNode) where all the
> division happens for the regular estimates, and one place in that same
> function that would need to be changed for EXPLAIN BUFFERS.

The above statement proves that you don't understand the problem ...

The reason that EXPLAIN prints things the way it does is so that actual
costs/times are comparable to estimated costs.  Because estimated costs
are built bottom-up, the cost of a lower plan node is not dependent on
the number of times it's iterated as a result of an upper node deciding
to rescan it.  If we change things so that the displayed actual costs
are totals rather than per-iteration, we'd have to do something to fix
the displayed estimates.

Now that might not be enormously difficult, but it's not trivial,
and it's certainly a larger change than just deciding to change
(or not) the recently-added hash statistics printout.

But the larger picture is that I agree with Greg's feeling that choosing
to completely change the way that EXPLAIN's outputs are defined is not
something to be done at the tail end of a devel cycle.  Maybe it'll be
better but I'd like to have some time with it under our belts before
we're committed.

And, btw, if you think this isn't documented then you haven't read the
relevant documentation.

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] Listen / Notify - what to do when the queue is full

[Jeff Davis]

On Tue, 2010-02-09 at 19:02 -0500, Andrew Chernow wrote:
> Wouldn't binary payloads be an alternative?  NOTE: I may have missed this 
> discussion.  Sorry if it has already been covered.

The Notify struct has a "char *" field, which can't hold embedded NULL
bytes, so it can't really be binary. But it can't be arbitrary text,
because it has to be encoded in a way that works for every possible
client encoding (otherwise there's a possibility of an error, and no way
to handle it).

Also, the query starts out as text, so we need a way to interpret the
text in an encoding-independent way.

So, I think ASCII is the natural choice here.

Regards,
Jeff Davis


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

Re: [HACKERS] Writeable CTEs and empty relations

[Marko Tiikkaja]

On 2010-02-10 02:19 +0200, Tom Lane wrote:
> Marko Tiikkaja  writes:
>> Does this sound completely unacceptable?
> 
> You still haven't explained why it's a good idea to change the snapshot
> after the executor has started.  Right at the moment I'm prepared to
> reject the patch on that ground alone.

The patch only touches the snapshot's curcid.  That's needed to allow
the queries see the tuples of the DML WITHs ran before that particular
query.


Regards,
Marko Tiikkaja

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

Re: [HACKERS] Writeable CTEs and empty relations

[Tom Lane]

Marko Tiikkaja  writes:
> On 2010-02-08 21:30 +0200, I wrote:
>> This doesn't exactly work anymore since we modify the snapshot after
>> calling ExecInitScan().  I'm not really familiar with this part of the
>> code, so I'm asking: is there a simple enough way around this?  Would
>> updating scan->rs_nblocks before scanning the first tuple be OK?

> I've looked at this some more, and the problem is a lot bigger than I
> originally thought.  We'd basically be forced to do another initscan()
> before starting a new scan after the snapshot changed.  One way to
> accomplish this would be that ExecutePlan() would leave a flag in EState
> whenever the scan nodes need to reinit.

> Does this sound completely unacceptable?

You still haven't explained why it's a good idea to change the snapshot
after the executor has started.  Right at the moment I'm prepared to
reject the patch on that ground alone.

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] Some belated patch review for "Buffers" explain analyze patch

[Robert Haas]

On Tue, Feb 9, 2010 at 6:33 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> On Tue, Feb 9, 2010 at 5:41 PM, Tom Lane  wrote:
>>> A more important point is that it would be a nontrivial change, both as
>>> to code and documentation, and it's too late for such in 9.0.  So what
>>> we ought to be confining the discussion to right now is what 9.0 should
>>> print here.
>
>> It's exactly as nontrivial as the proposed change in the other direction.
>
> Not in the least.  Fixing EXPLAIN to consistently print totals would
> involve changes in (at least) the treatment of estimated costs, and very
> possibly some changes in the Instrumentation support as well.

As far as I am aware there is one place (in ExplainNode) where all the
division happens for the regular estimates, and one place in that same
function that would need to be changed for EXPLAIN BUFFERS.  On a
quick look, I see no reason why the Instrumentation support would need
any modification at all.

> I notice
> you blithely disregarded the documentation point, too.

Very blithely.  The current behavior of dividing the estimate by the
row count and rounding off in a way that makes it impossible to
reconstruct the raw numbers is equally undocumented.  It seems to me
that the documentation will require some updating no matter what we
decide to do.

It seems to me that the entire topic of this thread is taking some
numbers that are simple and useful and trying as hard as possible to
masticate them in a way that will make them misleading and difficult
to use.  As I understand it, the proposal on the table is that if we
have a node that over 5,326 iterations hits 31,529 shared buffers and
reads 2135 shared buffers, then instead of printing:

Buffers: shared hit=31529 read=2135

...we're instead going to print:

Buffers: shared hit=47kB read=3kB

Explain to me why we think that's an improvement?

...Robert

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

Re: [HACKERS] Listen / Notify - what to do when the queue is full

[Andrew Chernow]

Jeff Davis wrote:

On Tue, 2010-02-09 at 16:51 -0500, Tom Lane wrote:

Limiting NOTIFY payloads to 7-bit would definitely avoid the issue.
The question is if that's more of a pain than a benefit.


I don't see any alternative. If one backend sends a NOTIFY payload that


Wouldn't binary payloads be an alternative?  NOTE: I may have missed this 
discussion.  Sorry if it has already been covered.


--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

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

Re: [HACKERS] Some belated patch review for "Buffers" explain analyze patch

[Greg Stark]

On Tue, Feb 9, 2010 at 10:42 PM, Robert Haas  wrote:
>> A more important point is that it would be a nontrivial change, both as
>> to code and documentation, and it's too late for such in 9.0.  So what
>> we ought to be confining the discussion to right now is what 9.0 should
>> print here.
>
> It's exactly as nontrivial as the proposed change in the other direction.

I think it's non-triviality lies in the user-visible effects. Ie, we
won't have a release cycle of experience with the change ourselves to
see if we really like the new behaviour and can live with it.

I'm not sure where I stand myself as I find the averaging quite
confusing myself. I'm not sure how confusing it would be to change
though.

The total i/o done is likely to be something you want to compare with
some other metric like output from iostat or knowledge of how much
bandwidth your i/o system can provide so it does seem like totals are
relevant.

I think I'm leaning -- slightly -- towards calling out the discrepancy
by naming it something like "Total Buffer Usage:". But I could be
convinced otherwise.


-- 
greg

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

Re: [HACKERS] Some belated patch review for "Buffers" explain analyze patch

[Tom Lane]

Robert Haas  writes:
> On Tue, Feb 9, 2010 at 5:41 PM, Tom Lane  wrote:
>> A more important point is that it would be a nontrivial change, both as
>> to code and documentation, and it's too late for such in 9.0.  So what
>> we ought to be confining the discussion to right now is what 9.0 should
>> print here.

> It's exactly as nontrivial as the proposed change in the other direction.

Not in the least.  Fixing EXPLAIN to consistently print totals would
involve changes in (at least) the treatment of estimated costs, and very
possibly some changes in the Instrumentation support as well.  I notice
you blithely disregarded the documentation point, too.

regards, tom lane

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

[HACKERS] log_error_verbosity placement

[Bruce Momjian]

I just realized that log_error_verbosity is in the wrong section in
postgresql.conf and in our manual.  It is listed under "When to log"
when in fact it should be in the "What to log" section.

I also added documentation of what "terse" mode actually does.  I added
"verbosity" information a few weeks ago.

Patch attached.

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

  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/config.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/config.sgml,v
retrieving revision 1.250
diff -c -c -r1.250 config.sgml
*** doc/src/sgml/config.sgml	5 Feb 2010 23:37:43 -	1.250
--- doc/src/sgml/config.sgml	9 Feb 2010 23:14:27 -
***
*** 2833,2858 

   
  
-  
-   log_error_verbosity (enum)
-   
-log_error_verbosity configuration parameter
-   
-   
-
- Controls the amount of detail written in the server log for each
- message that is logged.  Valid values are TERSE,
- DEFAULT, and VERBOSE, each adding more
- fields to displayed messages.  VERBOSE logging
- output includes the SQLSTATE error
- code and the source code file name, function name,
- and line number that generated the error.
- Only superusers can change this setting.
-
-   
-  
- 
   
log_min_error_statement (enum)

--- 2833,2838 
***
*** 3172,3177 
--- 3152,3179 

   
  
+  
+   log_error_verbosity (enum)
+   
+log_error_verbosity configuration parameter
+   
+   
+
+ Controls the amount of detail written in the server log for each
+ message that is logged.  Valid values are TERSE,
+ DEFAULT, and VERBOSE, each adding more
+ fields to displayed messages.  TERSE excludes
+ the logging of DETAIL, HINT,
+ QUERY, and CONTEXT error information.
+ VERBOSE output includes the SQLSTATE error
+ code and the source code file name, function name,
+ and line number that generated the error.
+ Only superusers can change this setting.
+
+   
+  
+ 
   
log_hostname (boolean)

Index: src/backend/utils/misc/postgresql.conf.sample
===
RCS file: /cvsroot/pgsql/src/backend/utils/misc/postgresql.conf.sample,v
retrieving revision 1.276
diff -c -c -r1.276 postgresql.conf.sample
*** src/backend/utils/misc/postgresql.conf.sample	1 Feb 2010 13:40:28 -	1.276
--- src/backend/utils/misc/postgresql.conf.sample	9 Feb 2010 23:14:28 -
***
*** 311,318 
  	#   fatal
  	#   panic
  
- #log_error_verbosity = default		# terse, default, or verbose messages
- 
  #log_min_error_statement = error	# values in order of decreasing detail:
   	#   debug5
  	#   debug4
--- 311,316 
***
*** 343,348 
--- 341,347 
  #log_connections = off
  #log_disconnections = off
  #log_duration = off
+ #log_error_verbosity = default		# terse, default, or verbose messages
  #log_hostname = off
  #log_line_prefix = ''			# special values:
  	#   %a = application name

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

Re: [HACKERS] Listen / Notify - what to do when the queue is full

[Jeff Davis]

On Tue, 2010-02-09 at 16:51 -0500, Tom Lane wrote:
> Limiting NOTIFY payloads to 7-bit would definitely avoid the issue.
> The question is if that's more of a pain than a benefit.

I don't see any alternative. If one backend sends a NOTIFY payload that
contains a non-ASCII character, there's a risk that we won't be able to
deliver it to another backend with a client_encoding that can't
represent that character. 

Also, just the fact that client_encoding can be changed at pretty much
any time is a potential problem, because it's difficult to know whether
a particular notification was sent using the old client_encoding or the
new one (because it's asynchronous).

Regards,
Jeff Davis


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

Re: [HACKERS] Writeable CTEs and empty relations

[Marko Tiikkaja]

On 2010-02-08 21:30 +0200, I wrote:
> This doesn't exactly work anymore since we modify the snapshot after
> calling ExecInitScan().  I'm not really familiar with this part of the
> code, so I'm asking: is there a simple enough way around this?  Would
> updating scan->rs_nblocks before scanning the first tuple be OK?

I've looked at this some more, and the problem is a lot bigger than I
originally thought.  We'd basically be forced to do another initscan()
before starting a new scan after the snapshot changed.  One way to
accomplish this would be that ExecutePlan() would leave a flag in EState
whenever the scan nodes need to reinit.

Does this sound completely unacceptable?


Regards,
Marko Tiikkaja

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

Re: [HACKERS] Some belated patch review for "Buffers" explain analyze patch

[Robert Haas]

On Tue, Feb 9, 2010 at 5:41 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> On Tue, Feb 9, 2010 at 5:07 PM, Tom Lane  wrote:
>>> Well, if you want to put forward a proposal to get rid of that approach
>>> entirely, go ahead.  But it doesn't seem like a good idea to me for
>>> EXPLAIN to print some numbers according to one viewpoint and some
>>> according to the other.
>
>> Well, if I propose that we just abandon that approach and print only
>> totals for everything, is that DOA?  I think it would be a major
>> improvement, but it will break backward compatibility.
>
> Well, I'm not for changing it, but I might be in the minority.
>
> A more important point is that it would be a nontrivial change, both as
> to code and documentation, and it's too late for such in 9.0.  So what
> we ought to be confining the discussion to right now is what 9.0 should
> print here.

It's exactly as nontrivial as the proposed change in the other direction.

...Robert

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

Re: [HACKERS] Some belated patch review for "Buffers" explain analyze patch

[Tom Lane]

Robert Haas  writes:
> On Tue, Feb 9, 2010 at 5:07 PM, Tom Lane  wrote:
>> Well, if you want to put forward a proposal to get rid of that approach
>> entirely, go ahead.  But it doesn't seem like a good idea to me for
>> EXPLAIN to print some numbers according to one viewpoint and some
>> according to the other.

> Well, if I propose that we just abandon that approach and print only
> totals for everything, is that DOA?  I think it would be a major
> improvement, but it will break backward compatibility.

Well, I'm not for changing it, but I might be in the minority.

A more important point is that it would be a nontrivial change, both as
to code and documentation, and it's too late for such in 9.0.  So what
we ought to be confining the discussion to right now is what 9.0 should
print here.

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] Writeable CTEs patch

[Robert Haas]

On Tue, Feb 9, 2010 at 3:13 PM, Marko Tiikkaja
 wrote:
> On 2010-02-08 18:42 +0200, Robert Haas wrote:
>> On Thu, Feb 4, 2010 at 11:57 AM, Marko Tiikkaja
>>  wrote:
>>> Here's an updated patch.  Only changes from the previous patch are
>>> fixing the above issue and a regression test for it.
>>
>> - I'm not sure that canSetTag is the right name for the additional
>> argument to ExecInsert/ExecUpdate/ExecDelete.  OTOH, I'm not sure it's
>> the wrong name either.  But should we use something like
>> isTopLevelQuery?
>
> I'm going to have to take back my previous statement; this doesn't make
> a lot of sense in the case of DO ALSO rules (or multiple statements in a
> DO INSTEAD RULE).  Those will have canSetTag=false, but they will be at
> the top level.

Ah.  OK.

>> - It appears that we pull out all of the DML statements first and run
>> them in order, but I'm not sure that's the right thing to do.
>> Consider:
>>
>> WITH x AS (INSERT ...), y AS (SELECT ...), z AS (INSERT ...) SELECT ...
>>
>> I would assume we would do x, CCI, do y, do z, CCI, do main query, but
>> I don't think that's what this implements.  The user might be
>> surprised to find out that y sees the effects of z.
>
> I went ahead and implemented this, but there seems to be one small
> problem: RECURSIVE.  If there is a recursive query between those, it
> might loop forever even if the top-level SELECT only wanted to see a few
> rows from it.  The docs already discourage writing recursive ctes like
> that, but still this is a small caveat.

Doesn't seem like a big problem to me.

>> - It seems like the gram.y changes for common_table_expr might benefit
>> from some factoring; that is, create a production (or find a suitable
>> existing one) for "statements of the sort that can appear within
>> CTEs", and then use that in common_table_expr.  Or maybe this doesn't
>> work; I haven't tried it.
>
> This seems to work.  I used PreparableStmt, but I'm not sure how good
> idea that really is.  Maybe I should create a new one?

If it covers the same territory, I wouldn't duplicate it just for fun.
 Someone might need to split it out in the future, but that's not a
reason to do it now.

...Robert

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

Re: [HACKERS] About psycopg2 (by its author)

[Greg Smith]

Federico Di Gregorio wrote:

First of all let me say that from such incredible hackers as the
PostgreSQL people I'd have expected the ability to find my email address
and maybe keep me or (even better) the psycopg mailing list in CC. That
would have avoided a lot of confusion both on the license and the status
of psycopg2.


Well, the whole thing didn't start as a discussion of psycopg.  It 
started as "why do we have so many Python drivers and so little 
documentation about them all?", and at that point it didn't seem 
appropriate to drag you into a discussion that might not have even 
involved your driver in particular.  There were at least seven other 
driver writers that might have gotten dragged into the discussion too, 
but the topic didn't start by asking everyone of them for an opinion or 
a status update.  It started by considering "what does the PostgreSQL 
community want from a Python driver and how is that different from 
what's available?" and working out from there.


After that went on a while, it became obvious to a number of people that 
while psycopg has excellent technical strengths, it was incompatible 
with what a lot of people wanted to see in terms of its license.  After 
going through all that, it's only at that point that we realized talking 
with you about the license was a really important step--important enough 
that I didn't just find your e-mail address and cc you, I tracked down a 
local contact who already knew you to personally ask about the license 
issue.  I didn't want to put you into the uncomfortable position of 
having to publicly deny our request just because you'd been cc'd with 
the question, if it wasn't compatible with your plans.  It's unfortunate 
that after stating here I was going to handle bringing you into this 
discussion nicely, you got dragged into it badly anyway.



Btw, I was at FOSDEM as probably other PostgreSQL people were and all this could
have been discussed while drinking a couple of beers if only someone
cared to contact me.
  


Most of us who had a strong opinion here weren't at FOSDEM--if we were, 
we'd have been out having beers instead of writing e-mail those days, of 
course.  Please make sure to introduce yourself to any PostgreSQL people 
you find around future conferences--I think most in our community know 
psycopg even if your name wasn't familiar to everyone yet.



So the logical choice is plain LGPL3. I am open to motivated suggestions about 
other
licenses but I'll ignore such crap as "BSD is more open than LGPL".
  


I agree with your general logic and while I can't speak for everyone, I 
would be happy enough with a LGPL3 licensed psycopg (obviously 
addressing the usual OpenSSL mess) to pull the license issue off the top 
of the list as a major problem preventing broader deployment of 
psycopg.  The main two points of contention seemed to be your unique 
customizations to the license, which make a lot of legal people nervous, 
and even worse that they were so clearly limiting many types of 
commercial use.  I hope you'd appreciate that while you have have 
legitimate reasons for your license choices, ones in that form are 
likely to remind this community of the split open/commercial licenses as 
seen in products like MySQL, and we've watch that combination lead 
toward a less open community than this one wants to be.


As for arguments against the LGPL, the main one I care about is that 
you're more likely to have businesses who hire people adopt a product if 
it's BSD or MIT licensed.  I make a decent chunk of my living doing 
support and customization work on open-source projects.  Anything that 
has a GPL license attached is something I'm less likely to incorporate 
into custom project work I do, because it decreases the number of 
businesses who are then interested in it.  This is mainly because they 
have to incorporate all that background into their "credits" list for 
aggregate works, and that concern inevitably opens up more questions 
better avoided about the implications of the software being bundled.


I'm more concerned about increasing the market I can provide such 
solutions to than I am about people stealing my work, crediting me, or 
not sharing their own customizations.  So my preference for BSD-ish 
licenses is a pragmatic one rooted in business goals.  If you wanted to 
improve your odds of companies adopting psycopg for projects that might 
then lead to them hiring you for support or improvements to the 
software, I'd suggest that using the GPL or even the LGPL is actually 
doing the exact opposite of that.  If your goals are more about 
releasing proper free software in the original Stallman inspired sense 
of the word, the LGPL3 might be exactly the right license for you.


Second point, the technical discussion about psycopg2 features and bugs... 


I tried to keep the part of that discussion that went into a more public 
form limited to listing suspected issues for further investigation.  You 
can se

Re: [HACKERS] Listen / Notify - what to do when the queue is full

[Robert Haas]

On Tue, Feb 9, 2010 at 4:51 PM, Tom Lane  wrote:
> Jeff Davis  writes:
>> Looking at the documentation, it appears that all of the server
>> encodings represent 7-bit ascii characters using the same 7-bit ascii
>> representation. Is that true?
>
> Correct.  We only support ASCII-superset encodings, both for frontend
> and backend.
>
> Limiting NOTIFY payloads to 7-bit would definitely avoid the issue.
> The question is if that's more of a pain than a benefit.

I think it's a reasonable restriction for now.  We have limited time
remaining here; and we can always consider relaxing the restriction in
the future when we have more time to think through the issues.  It'll
still be a big improvement over what we have now.

...Robert

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

Re: [HACKERS] Some belated patch review for "Buffers" explain analyze patch

[Robert Haas]

On Tue, Feb 9, 2010 at 5:07 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> On Tue, Feb 9, 2010 at 3:26 PM, Josh Berkus  wrote:
>>> I'd prefer to have the average; it's very confusing to have an explain
>>> row which has the cost per iteration, but the buffer usage per node.
>
>> The cost per iteration thing is IMO one of the most confusing parts of
>> the EXPLAIN output; I'm not really eager to see us replicate that
>> elsewhere.
>
> Well, if you want to put forward a proposal to get rid of that approach
> entirely, go ahead.  But it doesn't seem like a good idea to me for
> EXPLAIN to print some numbers according to one viewpoint and some
> according to the other.

Well, if I propose that we just abandon that approach and print only
totals for everything, is that DOA?  I think it would be a major
improvement, but it will break backward compatibility.

...Robert

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

Re: [HACKERS] Avoiding bad prepared-statement plans.

[Dimitri Fontaine]

Jeroen Vermeulen  writes:
> I think we should be careful not to over-think this.  Planning isn't *that*
> costly, so apply Amdahl's Law liberally.  I'm proposing some easy things we
> could do without adding much overhead or maintenance burden; I've been
> assuming that getting intimate with the planner would risk those advantages.

On a project where performance is a must (5ms per query is about all you
can ask) I have queries for which planning is 40+ ms and execute 2 to 5
ms (dataset fits in RAM, by design).

I'm then abusing pgbouncer so that the PREPARE is shared by a lot of
clients, all the ones landing into the session (transaction pooling).

See preprepare to get a better idea, even if we're yet to run it (it's
being used in production elsewhere, though, I've been told).

  http://preprepare.projects.postgresql.org/

Regards,
-- 
dim

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

Re: [HACKERS] Some belated patch review for "Buffers" explain analyze patch

[Tom Lane]

Robert Haas  writes:
> On Tue, Feb 9, 2010 at 3:26 PM, Josh Berkus  wrote:
>> I'd prefer to have the average; it's very confusing to have an explain
>> row which has the cost per iteration, but the buffer usage per node.

> The cost per iteration thing is IMO one of the most confusing parts of
> the EXPLAIN output; I'm not really eager to see us replicate that
> elsewhere.

Well, if you want to put forward a proposal to get rid of that approach
entirely, go ahead.  But it doesn't seem like a good idea to me for
EXPLAIN to print some numbers according to one viewpoint and some
according to the other.

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] [GENERAL] ERROR: could not load library "...": Exec format error

[Boszormenyi Zoltan]

Boszormenyi Zoltan írta:
> Korry Douglas írta:
>   
>>> I have the $SUBJECT problem loading my own
>>> module in PostgreSQL. The server is HP-UX/ia64,
>>> PostgeSQL 8.4.2 was compiled with HP CC.
>>> pl/PgSQL can be loaded fine.
>>>
>>> ...:/usr/local/pgsql/pgsql-cc-8.4/lib# ldd odbclink.so
>>>libodbc.so.1 => /usr/local/pgsql/runtime/lib/libodbc.so.1
>>>libpthread.so.1 =>  /usr/lib/hpux64/libpthread.so.1
>>>libc.so.1 =>/usr/lib/hpux64/libc.so.1
>>>libdl.so.1 =>   /usr/lib/hpux64/libdl.so.1
>>>
>>> "/usr/local/pgsql/runtime" is a link to "/usr/local/pgsql/pgsql-cc-8.4"
>>>
>>> ...:/usr/local/pgsql/pgsql-cc-8.4/lib# file plpgsql.so odbclink.so
>>> plpgsql.so: ELF-64 shared object file - IA64
>>> odbclink.so:ELF-64 shared object file - IA64
>>>
>>> The module compilation was done using "USE_PGXS=1 gmake".
>>>
>>> How can I solve this issue?
>>>   
>> IIRC, HP/UX doesn't like to dynamic-load shared libraries that use
>> thread-local storage. Your shared library (odbclink.so) is linked
>> against libpthread.so.1 so you may be running into that problem.  I
>> would recommend running the HP/UX equivalent of strace to capture more
>> information about the call to dlopen()  (or perhaps shl_load(),
>> depending on which version of HP/UX you are using).
>> 
>
> Yes, it seems you are right, I have read dlopen(3) in full on HP-UX,
> this small detail is documented there. It seems I need to preload
> the libraries: odbclink.so, libodbc.so.1 and all the needed libs from
> the target ODBC driver... Thanks for the info.
>   

Actually, I experimented with a "threaded" PostgreSQL server,
I added LDFLAGS="-lpthreads" to the PG configure options
and suddenly my module that's linked to libraries also linked
with libpthread.so started working. The dlopen(3) man page
doesn't say this as a working constellation.

Best regards,
Zoltán Böszörményi

-- 
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics

--
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/


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

Re: [HACKERS] Some belated patch review for "Buffers" explain analyze patch

[Tom Lane]

Greg Stark  writes:
> I already have a patch to do this but it's a bit grotty -- do we want
> to have a generic format string in snprintf in case we need it
> somewhere else other than explain.c?

No.  Custom format specifiers that take arguments will confuse the heck
out of gcc's format-checking warnings.  There is no way that saving
a bit of ugliness is worth that.  Just do format_memory_amount(foo)
and print it with %s.

> Secondly, I think it's printing the total buffer usage for that node
> across the whole query -- not the average per iteration. I agree that
> the average is probably more confusing but it's what we do for every
> other stat. Do we want to be consistent?

Probably yes.  But it strikes me that the additional numbers printed for
Sort nodes might be bogus in multiple-loop cases too; what about 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] Some belated patch review for "Buffers" explain analyze patch

[Robert Haas]

On Tue, Feb 9, 2010 at 3:26 PM, Josh Berkus  wrote:
> On 2/9/10 11:50 AM, Greg Stark wrote:
>> Secondly, I think it's printing the total buffer usage for that node
>> across the whole query -- not the average per iteration. I agree that
>> the average is probably more confusing but it's what we do for every
>> other stat. Do we want to be consistent? Call out the inconsistency
>> somehow, perhaps by tagging it "Total Buffer Usage:" or something like
>> that?
>
> I'd prefer to have the average; it's very confusing to have an explain
> row which has the cost per iteration, but the buffer usage per node.

The cost per iteration thing is IMO one of the most confusing parts of
the EXPLAIN output; I'm not really eager to see us replicate that
elsewhere.

...Robert

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

Re: [HACKERS] Listen / Notify - what to do when the queue is full

[Tom Lane]

Jeff Davis  writes:
> Looking at the documentation, it appears that all of the server
> encodings represent 7-bit ascii characters using the same 7-bit ascii
> representation. Is that true?

Correct.  We only support ASCII-superset encodings, both for frontend
and backend.

Limiting NOTIFY payloads to 7-bit would definitely avoid the issue.
The question is if that's more of a pain than a benefit.

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] Listen / Notify - what to do when the queue is full

[Jeff Davis]

On Mon, 2010-02-08 at 22:13 -0800, Jeff Davis wrote:
> I would like to support encoded text, but I think there are other
> problems. For instance, what if one server has a client_encoding that
> doesn't support some of the glyphs being sent by the notifying backend?
> Then we have a mess, because we can't deliver it.

I was thinking more about this. It seems clear that we want the backend
that issues the notify to only put 7-bit ASCII in the payload.

But if the client sends the letters 'string' as a payload, and the
representation in the server encoding is something other than the normal
7-bit ASCII representation of 'string', it will be incorrect, right?

Looking at the documentation, it appears that all of the server
encodings represent 7-bit ascii characters using the same 7-bit ascii
representation. Is that true?

Regards,
Jeff Davis


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

Re: [HACKERS] [PATCH] "could not reattach to shared memory" on Windows

[Etienne Dube]

Magnus Hagander wrote:

IIRC, we've had zero reports on whether the patch worked at all on 8.2
in an environment where the problem actually existed. So yes, some
testing and feedback would be much apprecaited.

//Magnus
  


Thanks for your quick reply.
We upgraded to Service Pack 2 and it solved the problem. Nevertheless, 
I'll try to reproduce the issue under a Win2008 SP1 VM to see whether 
the patch makes a difference. 8.2.x win32 binaries are built using MinGW 
right?


Etienne





2010/2/8 Etienne Dube :
  

Hi,

We've come across this issue on 8.2.15 on a Windows Server 2008 instance. I 
noticed the patch hasn't been applied to the 8.2 branch yet. Any chances that 
this will be part of an eventual 8.2.16 release? Do you need more testing and 
feedback before commiting the patch?

Thanks,

Etienne Dube




   * *From*: Magnus Hagander 
   * *To*: Tom Lane 
   * *Cc*: Tsutomu Yamada , Alvaro Herrera
 , pgsql-hackers@postgresql.org, Dave
 Page 
   * *Subject*: Re: [PATCH] "could not reattach to shared memory" on
 Windows
   * *Date*: Tue, 11 Aug 2009 17:14:08 +0200
   * *Message-id*:
 <9837222c0908110814n414b2fcbxcaf7c0e1fcc05...@mail.gmail.com
 >


On Tue, Aug 11, 2009 at 16:30, Magnus Hagander wrote:
  

On Mon, Aug 10, 2009 at 19:33, Magnus Hagander wrote:


On Mon, Aug 10, 2009 at 16:58, Tom Lane wrote:
  

Magnus Hagander  writes:


On Mon, Aug 10, 2009 at 16:10, Tom Lane wrote:
  

8.2 as well, no?


8.2 has a different shmem implementation - the one that emulates sysv
shmem. The patch will need to be changed around for that, and I
haven't looked at that. It may be worthwhile to do that, but it's a
separate patch, so let's get it out in 8.3 and 8.4 first.
  

If it's at all hard to do, I could see deprecating 8.2 for Windows
instead.


I haven't looked at how much work it would be at all yet. So let's do
that before we decide to deprecate anything. As mentioned downthread,
8.2 is a very widespread release, and we really want to avoid
deprecating it.
  

Here's an attempt at a backport to 8.2. I haven't examined it  in
detail, but it passes "make check" on mingw.

Comments?


I've also built a binary that should be copy:able on top of an 8.2.13
installation made from the standard installer, to test this feature.
Anybody on 8.2 on Windows, please give it a shot and let us know how
it works.

http://www.hagander.net/pgsql/postgres_exe_virtualalloc_8_2.zip


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






  



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

Re: [HACKERS] Some belated patch review for "Buffers" explain analyze patch

[Josh Berkus]

On 2/9/10 11:50 AM, Greg Stark wrote:
> Secondly, I think it's printing the total buffer usage for that node
> across the whole query -- not the average per iteration. I agree that
> the average is probably more confusing but it's what we do for every
> other stat. Do we want to be consistent? Call out the inconsistency
> somehow, perhaps by tagging it "Total Buffer Usage:" or something like
> that?

I'd prefer to have the average; it's very confusing to have an explain
row which has the cost per iteration, but the buffer usage per node.

--Josh Berkus

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

Re: [HACKERS] CreateFakeRelcacheEntry versus Hot Standby

[Tom Lane]

Heikki Linnakangas  writes:
> I started looking at the callers of CreateFakeRelcacheEntry to see if
> any of them actually take a lock on the Relation. It seems not, so maybe
> we should just set those to InvalidOid, and add an
> Assert(OidIsValid(dbId) && OidIsValid(relId)) to LockAcquire() to ensure
> that a fake relcache entry is not used to grab locks in the future either.

+1, I was thinking that would be a reasonable thing to try.  (You can't
assert dbID != 0, but the relid test should be sufficient.)

> However, I spotted another bug. In ginContinueSplit(), we call
> CreateFakeRelcacheEntry(), and pass the fake relcache entry to
> prepareEntryScan() or prepareDataScan(). Those functions store the
> passed-in Relation ptr in the passed in GinBtreeData struct. After the
> call, we free the fake relcache entry, but the pointer is still present
> in the GinBtreeData. The findParents() call later in the function uses
> it, and will thus access free'd memory.

> A trivial fix is to delay freeing the fake relcache entry in
> ginContinueSplit(). But this once again shows that our WAL redo
> functions are not as well tested as they should be, and the WAL redo
> cleanup functions in particularly.

Well, HS will at least help us shake out the redo functions.  Cleanup
is a hard case to test ...

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] Writeable CTEs patch

[Marko Tiikkaja]

On 2010-02-08 18:42 +0200, Robert Haas wrote:
> On Thu, Feb 4, 2010 at 11:57 AM, Marko Tiikkaja
>  wrote:
>> Here's an updated patch.  Only changes from the previous patch are
>> fixing the above issue and a regression test for it.
> 
> - I'm not sure that canSetTag is the right name for the additional
> argument to ExecInsert/ExecUpdate/ExecDelete.  OTOH, I'm not sure it's
> the wrong name either.  But should we use something like
> isTopLevelQuery?

I'm going to have to take back my previous statement; this doesn't make
a lot of sense in the case of DO ALSO rules (or multiple statements in a
DO INSTEAD RULE).  Those will have canSetTag=false, but they will be at
the top level.

> - It appears that we pull out all of the DML statements first and run
> them in order, but I'm not sure that's the right thing to do.
> Consider:
> 
> WITH x AS (INSERT ...), y AS (SELECT ...), z AS (INSERT ...) SELECT ...
> 
> I would assume we would do x, CCI, do y, do z, CCI, do main query, but
> I don't think that's what this implements.  The user might be
> surprised to find out that y sees the effects of z.

I went ahead and implemented this, but there seems to be one small
problem: RECURSIVE.  If there is a recursive query between those, it
might loop forever even if the top-level SELECT only wanted to see a few
rows from it.  The docs already discourage writing recursive ctes like
that, but still this is a small caveat.

> - It seems like the gram.y changes for common_table_expr might benefit
> from some factoring; that is, create a production (or find a suitable
> existing one) for "statements of the sort that can appear within
> CTEs", and then use that in common_table_expr.  Or maybe this doesn't
> work; I haven't tried it.

This seems to work.  I used PreparableStmt, but I'm not sure how good
idea that really is.  Maybe I should create a new one?


Regards,
Marko Tiikkaja

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

Re: [HACKERS] CreateFakeRelcacheEntry versus Hot Standby

[Heikki Linnakangas]

Tom Lane wrote:
> I was rather surprised to find this code still present:
> 
>   /*
>* We set up the lockRelId in case anything tries to lock the dummy
>* relation.  Note that this is fairly bogus since relNode may be
>* different from the relation's OID.  It shouldn't really matter 
> though,
>* since we are presumably running by ourselves and can't have any lock
>* conflicts ...
>*/
>   rel->rd_lockInfo.lockRelId.dbId = rnode.dbNode;
>   rel->rd_lockInfo.lockRelId.relId = rnode.relNode;
> 
> Seems quite unsafe in HS.

Good catch.

I started looking at the callers of CreateFakeRelcacheEntry to see if
any of them actually take a lock on the Relation. It seems not, so maybe
we should just set those to InvalidOid, and add an
Assert(OidIsValid(dbId) && OidIsValid(relId)) to LockAcquire() to ensure
that a fake relcache entry is not used to grab locks in the future either.

However, I spotted another bug. In ginContinueSplit(), we call
CreateFakeRelcacheEntry(), and pass the fake relcache entry to
prepareEntryScan() or prepareDataScan(). Those functions store the
passed-in Relation ptr in the passed in GinBtreeData struct. After the
call, we free the fake relcache entry, but the pointer is still present
in the GinBtreeData. The findParents() call later in the function uses
it, and will thus access free'd memory.

A trivial fix is to delay freeing the fake relcache entry in
ginContinueSplit(). But this once again shows that our WAL redo
functions are not as well tested as they should be, and the WAL redo
cleanup functions in particularly.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

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

[HACKERS] Some belated patch review for "Buffers" explain analyze patch

[Greg Stark]

I was recently experimenting with explain analyze and I realized there
are two things arguably wrong with the "Buffers" output in explain
analyze:

Firstly, it's printing out a number of buffers. We spent a lot of
effort making all GUC variables use units of memory like "kB" and "MB"
so the user should never have to be doing arithmetic in units of
buffers. I think these values should be printed in similar units.
Heikki did express concern that sometimes users want to know iops but
there's no real way for us to report iops since even random reads
might not really be random and they might be from cache. Whereas the
amount of memory is always a meaningful number.

I already have a patch to do this but it's a bit grotty -- do we want
to have a generic format string in snprintf in case we need it
somewhere else other than explain.c?


Secondly, I think it's printing the total buffer usage for that node
across the whole query -- not the average per iteration. I agree that
the average is probably more confusing but it's what we do for every
other stat. Do we want to be consistent? Call out the inconsistency
somehow, perhaps by tagging it "Total Buffer Usage:" or something like
that?


-- 
greg

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

Re: [HACKERS] Why doesn't smgr_redo truncate the VM fork too?

[Heikki Linnakangas]

Tom Lane wrote:
> Heikki Linnakangas  writes:
>> Tom Lane wrote:
>>> Seems inconsistent ...
> 
>> Indeed. Seems like an oversight.
> 
> OK, will fix since I'm fooling with that code anyway.

Thanks. While we're at it, the comment above visibilitymap_truncate()
has a copy-pasto in it (it claims the function is called visibilitymap_test)

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

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

Re: [HACKERS] Why doesn't smgr_redo truncate the VM fork too?

[Tom Lane]

Heikki Linnakangas  writes:
> Tom Lane wrote:
>> Seems inconsistent ...

> Indeed. Seems like an oversight.

OK, will fix since I'm fooling with that code anyway.

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] Why doesn't smgr_redo truncate the VM fork too?

[Heikki Linnakangas]

Tom Lane wrote:
> Seems inconsistent ...

Indeed. Seems like an oversight.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

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

[HACKERS] Why doesn't smgr_redo truncate the VM fork too?

[Tom Lane]

Seems inconsistent ...

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] CreateFakeRelcacheEntry versus Hot Standby

[Tom Lane]

I was rather surprised to find this code still present:

/*
 * We set up the lockRelId in case anything tries to lock the dummy
 * relation.  Note that this is fairly bogus since relNode may be
 * different from the relation's OID.  It shouldn't really matter 
though,
 * since we are presumably running by ourselves and can't have any lock
 * conflicts ...
 */
rel->rd_lockInfo.lockRelId.dbId = rnode.dbNode;
rel->rd_lockInfo.lockRelId.relId = rnode.relNode;

Seems quite unsafe in HS.

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] bugfix - VIP: variadic function ignore strict flag

[Pavel Stehule]

2010/2/9 Tom Lane :
> Pavel Stehule  writes:
>> 2010/2/9 Tom Lane :
>>> So what?  "variadic any" is different in a lot of ways.
>
>> implementation is different, but from users perspective there can not
>> be differences. I am not sure. From my programmer's view is all ok.
>> But I believe so from customer view, there can be a surprise - because
>> NULL value doesn't skip function call.
>
> It's going to be a bit surprising in any case.  If I write
>
>        foo(1, VARIADIC ARRAY[2, NULL])
>
> then what I'm passing is not a null, and so I'd be surprised if the
> function wasn't executed.

>
> I think we should just document this, not make a definitional change
> that seems as likely to break applications as fix them.

really I am not sure, what is good solution. Maybe can speak some other.

Pavel

>
>                        regards, tom lane
>

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

Re: [HACKERS] bugfix - VIP: variadic function ignore strict flag

[Tom Lane]

Pavel Stehule  writes:
> 2010/2/9 Tom Lane :
>> So what?  "variadic any" is different in a lot of ways.

> implementation is different, but from users perspective there can not
> be differences. I am not sure. From my programmer's view is all ok.
> But I believe so from customer view, there can be a surprise - because
> NULL value doesn't skip function call.

It's going to be a bit surprising in any case.  If I write

foo(1, VARIADIC ARRAY[2, NULL])

then what I'm passing is not a null, and so I'd be surprised if the
function wasn't executed.

I think we should just document this, not make a definitional change
that seems as likely to break applications as fix them.

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] bugfix - VIP: variadic function ignore strict flag

[Pavel Stehule]

2010/2/9 Tom Lane :
> Pavel Stehule  writes:
>> 2010/2/9 Tom Lane :
>>> I don't think this is right at all.
>
>> yes, this isn't clear. My arguments for change:
>
>> a) the behave depends on types - "any" is different than others.
>
> So what?  "variadic any" is different in a lot of ways.
>

implementation is different, but from users perspective there can not
be differences. I am not sure. From my programmer's view is all ok.
But I believe so from customer view, there can be a surprise - because
NULL value doesn't skip function call.

>> b) optimization over fmgr doesn't work now.
>> b1. some possible const null and strict are ignored
>
> That's a matter of definition.
>
>> b2. array is non const always - so pre eval doesn't work for variadic
>
> You'd need to explain what you mean by that.  An ARRAY[] construct is
> subject to const-folding AFAICS.

I am sorry. I was confused. This optimization will work. Only NULL is problem.

Regards
Pavel


>
>                        regards, tom lane
>

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

Re: [HACKERS] bugfix - VIP: variadic function ignore strict flag

[Tom Lane]

Pavel Stehule  writes:
> 2010/2/9 Tom Lane :
>> I don't think this is right at all.

> yes, this isn't clear. My arguments for change:

> a) the behave depends on types - "any" is different than others.

So what?  "variadic any" is different in a lot of ways.

> b) optimization over fmgr doesn't work now.
> b1. some possible const null and strict are ignored

That's a matter of definition.

> b2. array is non const always - so pre eval doesn't work for variadic

You'd need to explain what you mean by that.  An ARRAY[] construct is
subject to const-folding AFAICS.

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] Confusion over Python drivers

[Chris Browne]

kevina...@hotmail.com (Kevin Ar18) writes:
> Of course all of this is from the perspective of Python users.  Of
> course, you have your own features that you want from your end (from
> PostgreSQL's perspective).  Perhaps this info would help you to know
> which avenue to pursue.

No, those seem like fine ways of getting a good perspective on the
issue.  

I happen not to use Python much, so there's a certain aspect of "don't
care" on my part... but that doesn't imply that my "PostgreSQL
perspective" would tend to override yours.  Instead, I think that the
"Python users' perspective" *is* a mighty important thing.

The interface needs aspects of "cleanness" on both sides of the
interface...

 - On the Python side, it needs to "play well" in a variety of ways
   that you seem to have described nicely, some technical, some
   licensing oriented.  Some relating to interfacing to further bits
   of Python and to applications and frameworks written in Python.

 - On the PostgreSQL side, there's certainly a preference for 
   "licensing simplicity."

Note that most of the issues there really lie on the Python side, which
underlines the importance of "Python users' perspective."

Further, the "ideal" and "issues/problems" that you point out all seem
reasonable.  The good seems good and the bad seems like things that do
indeed need to be accepted as consequences of the good.

It will doubtless help guide assistance.
-- 
output = reverse("moc.liamg" "@" "enworbbc")
"...as a  robotics designer once told  me, you don't really appreciate
how smart a moron is until you try to design a robot..."
-- Jerry Pournelle

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

Re: [HACKERS] Avoiding bad prepared-statement plans.

[Tom Lane]

Robert Haas  writes:
> On Tue, Feb 9, 2010 at 7:08 AM, Jeroen Vermeulen  wrote:
>> Periodically re-plan prepared statements on EXECUTE.  This is also a chance
>> for queries that were being re-planned every time to go back to a generic
>> plan.

> The most common problem here seems to be that (some?) MCVs need
> different treatment than non-MCVs, so I don't think periodically
> replanning is going to help very much.

It won't help at all.  The only reason for replanning is if something
about the schema or the statistics change, and we already have got
automatic cached-plan invalidation in both those cases.  If you replan
simply because some time has elapsed, you'll just get exactly the
same plan.

The only case that I think still has any merit is where you get a
significantly better plan with known parameter values than without.
The projected-cost threshold might be a reasonable approach for
attacking that, ie, if estimated cost of generic plan exceeds X
then take the time to build a custom plan instead.  I'm not sure that
really will fix the problem, but it would be a very simple change to
make to see how much it helps people.

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] [CFReview] Red-Black Tree

[Oleg Bartunov]

On Mon, 8 Feb 2010, Tom Lane wrote:


Robert Haas  writes:

On Mon, Feb 8, 2010 at 3:05 PM, Alvaro Herrera
 wrote:

It seems a bit strange to have all the rb_free_recursive support and not
use it anywhere ... and a freefunc callback even, whose only caller
seems to set as null currently. ═Hmm, even in the knngist patch the
rb_freefunc stuff is unused.



I don't think it's inappropriate;  it doesn't seem implausible that
someone might want to free an rbtree someday.  I suppose we could
comment it out but I guess I don't see the point.


I think the suggestion was to *remove* it not comment it out.  I'm
skeptical of carrying dead code.  If the functionality is not used
in the proposed gist patches then it's very fair to question whether
it ever will be used.


ok, it's not a big deal to remove code. I think it's time to submit rbtree.




regards, tom lane




Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Streaming replication in docs

[Heikki Linnakangas]

Thom Brown wrote:
> On 9 February 2010 01:57, Fujii Masao  wrote:
>> The attached patch changes the layout according to your suggestion
>> (but with a small modification). Here is the new layout:
>>
>> ---
>> Chapter 24. Backup and Restore
>> 24.1. SQL Dump
>>24.1.1. Restoring the dump
>>24.1.2. Using pg_dumpall
>>24.1.3. Handling large databases
>> 24.2. File System Level Backup
>> 24.3. Continuous Archiving and Point-In-Time Recovery (PITR)
>>24.3.1. Setting up WAL archiving
>>24.3.2. Making a Base Backup
>>24.3.3. Recovering using a Continuous Archive Backup
>>24.3.4. Timelines
>>24.3.5. Tips and Examples
>>24.3.6. Caveats
>> 24.4. Migration Between Releases
>>
>> Chapter 25. High Availability, Load Balancing, and Replication
>> 25.1. Comparison of different solutions
>> 25.2. File-based Log Shipping
>>25.2.1. Planning
>>25.2.2. Implementation
>>25.2.3. Record-based Log Shipping
>> 25.3. Streaming Replication
>>25.3.1. Setup
>>25.3.2. Authentication
>> 25.4. Failover
>> 25.5. Hot Standby
>>25.5.1. User's Overview
>>25.5.2. Handling query conflicts
>>25.5.3. Administrator's Overview
>>25.5.4. Hot Standby Parameter Reference
>>25.5.5. Caveats
>> 25.6. Incrementally Updated Backups
>> ---
>>
>> I didn't change the content except adjusting the "sect" and
>> the "indexterm" tags to new chapter.
>>
>> If this layout change will have been committed, I'll improve
>> the SR section.
> 
> And can I just correct a couple typo's while we're changing it too? (attached)

Thanks, committed.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

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

Re: [HACKERS] bugfix - VIP: variadic function ignore strict flag

[Pavel Stehule]

2010/2/9 Tom Lane :
> Pavel Stehule  writes:
>> +             /*
>> +              * If function has variadic argument, skip calling
>> +              * when variadic array contains NULL values.
>> +              */
>
> I don't think this is right at all.  The strict check ought to apply to
> the array argument as a whole.

yes, this isn't clear. My arguments for change:

a) the behave depends on types - "any" is different than others.

b) optimization over fmgr doesn't work now.

b1. some possible const null and strict are ignored
b2. array is non const always - so pre eval doesn't work for variadic

c) it could be confusing, and it is partially confusing.

point c could be solved by notice in documentation. But a and b are
problem. The variadic funcall cannot be optimized :(

Regards
Pavel Stehule


>
>                        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] Avoiding bad prepared-statement plans.

[Robert Haas]

On Tue, Feb 9, 2010 at 7:08 AM, Jeroen Vermeulen  wrote:
> = Projected-cost threshold =
>
> If a prepared statement takes parameters, and the generic plan has a high
> projected cost, re-plan each EXECUTE individually with all its parameter
> values bound.  It may or may not help, but unless the planner is vastly
> over-pessimistic, re-planning isn't going to dominate execution time for
> these cases anyway.

How high is high?

> = Actual-cost threshold =
>
> Also stop using the generic plan if the statement takes a long time to run
> in practice.  Statistics may have gone bad.  It could also be a one-off due
> to a load peak or something, but that's handled by:
>
> = Plan refresh =
>
> Periodically re-plan prepared statements on EXECUTE.  This is also a chance
> for queries that were being re-planned every time to go back to a generic
> plan.

The most common problem here seems to be that (some?) MCVs need
different treatment than non-MCVs, so I don't think periodically
replanning is going to help very much.  What might help is something
like plan twice, once assuming you have the most common MCV and once
assuming you have a non-MCV.  If the two plans are same, you're
probably safe.  Or if you can somehow determine that one of the plans
will still be pretty fast in the other case, you can just use that
plan across the board.  Otherwise, you have to somehow vary the plan
based on the actual parameter value.

...Robert

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

Re: I: [HACKERS] About "Our CLUSTER implementation is pessimal" patch

[Josh Kupershmidt]

On Tue, Feb 9, 2010 at 5:49 AM, Leonardo F  wrote:

> Not even a comment? As I said, performance results on my system
> were very good
>

Hi Leonardo,
Perhaps you could supply a .sql file containing a testcase illustrating the
performance benefits you tested with your patch -- as I understand, the sole
purpose of this patch is to speed up CLUSTER -- along with your results? The
existing src/test/regress/sql/cluster.sql looks like it only has some basic
sanity checks in it, and not performance tests. An idea of what hardware
you're testing on and any tweaks to postgresql.conf might be useful as well.

I was able to apply your patch cleanly and run CLUSTER with it, and "make
check" passed for me on OS X as well.

Hope this helps, and sorry I'm not able to offer more technical advice on
your patch.
Josh

Re: [HACKERS] buildfarm breakage

[Magnus Hagander]

On Tue, Feb 9, 2010 at 17:11, Tom Lane  wrote:
> Magnus Hagander  writes:
>> Here's a patch that "fixes" this. I put it locally for the radius
>> authentication for now, since we don't use this anywhere else. Should
>> we put this in /port/ somewhere, or is this good for now?
>
> How about dropping it in src/backend/port/win32/mingwcompat.c ?

Oh, meh. I had forgotten we had that file :-)

Thanks for the reminder, will verify tonight that it still works after
I do that.


> The advantage of putting it in src/port/ is that it would possibly
> help client-side code sometime in future.  But what seems more likely
> to happen is that the mingw people will fix their oversight, and
> then we'd be risking link conflicts, which will be harder to fix on
> the client side.  So I'm inclined to not go there as long as we
> don't actually need it on client side.  But putting mingw hacks
> in a mingw-specific place seems a good idea.

yeah, agreed.


-- 
 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] bugfix - VIP: variadic function ignore strict flag

[Tom Lane]

Pavel Stehule  writes:
> + /*
> +  * If function has variadic argument, skip calling
> +  * when variadic array contains NULL values.
> +  */

I don't think this is right at all.  The strict check ought to apply to
the array argument as a whole.

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] buildfarm breakage

[Tom Lane]

Magnus Hagander  writes:
> Here's a patch that "fixes" this. I put it locally for the radius
> authentication for now, since we don't use this anywhere else. Should
> we put this in /port/ somewhere, or is this good for now?

How about dropping it in src/backend/port/win32/mingwcompat.c ?

The advantage of putting it in src/port/ is that it would possibly
help client-side code sometime in future.  But what seems more likely
to happen is that the mingw people will fix their oversight, and
then we'd be risking link conflicts, which will be harder to fix on
the client side.  So I'm inclined to not go there as long as we
don't actually need it on client side.  But putting mingw hacks
in a mingw-specific place seems a good idea.

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] bugfix - VIP: variadic function ignore strict flag

[Pavel Stehule]

Hello,

I am returning back to message
http://archives.postgresql.org/pgsql-general/2010-02/msg00119.php

Our implementation of variadic parameters missing correct handling
test on NULL, and test on non constant value.

This patch add correct tests for variadic parameters. Probably
Gen_fmgrtab.pl have needs some work - there are magic constants for
InvalidOid and ANYOID.

Regards

Pavel Stehule
*** ./src/backend/executor/execQual.c.orig	2010-01-11 16:31:04.0 +0100
--- ./src/backend/executor/execQual.c	2010-02-09 16:08:07.0 +0100
***
*** 1601,1606 
--- 1601,1612 
  		break;
  	}
  }
+ 
+ /* 
+  * If function is variadic, then check a variadic array argument.
+  */
+ if (fcache->func.fn_vararg && ARR_HASNULL(DatumGetArrayTypeP(fcinfo->arg[fcinfo->nargs - 1])))
+ 	callit = false;
  			}
  
  			if (callit)
***
*** 1738,1743 
--- 1744,1759 
  	return (Datum) 0;
  }
  			}
+ 			
+ 			/* 
+ 			 * Check variadic argument. If array with variadic arguments
+ 			 * contains NULLs, then return NULL.
+ 			 */
+ 			if (fcache->func.fn_vararg && ARR_HASNULL(DatumGetArrayTypeP(fcinfo->arg[fcinfo->nargs - 1])))
+ 			{
+ *isNull = true;
+ return (Datum) 0;
+ 			}
  		}
  
  		pgstat_init_function_usage(fcinfo, &fcusage);
***
*** 1805,1810 
--- 1821,1836 
  return (Datum) 0;
  			}
  		}
+ 		
+ 		/*
+ 		 * If function has variadic argument, skip calling
+ 		 * when variadic array contains NULL values.
+ 		 */
+ 		if (fcache->func.fn_vararg && ARR_HASNULL(DatumGetArrayTypeP(fcinfo.arg[fcinfo.nargs - 1])))
+ 		{
+ 			*isNull = true;
+ 			return (Datum) 0;
+ 		}
  	}
  
  	pgstat_init_function_usage(&fcinfo, &fcusage);
*** ./src/backend/optimizer/util/clauses.c.orig	2010-01-19 17:33:33.0 +0100
--- ./src/backend/optimizer/util/clauses.c	2010-02-09 12:04:57.0 +0100
***
*** 3588,3599 
  	/*
  	 * Check for constant inputs and especially constant-NULL inputs.
  	 */
! 	foreach(arg, args)
  	{
! 		if (IsA(lfirst(arg), Const))
! 			has_null_input |= ((Const *) lfirst(arg))->constisnull;
! 		else
! 			has_nonconst_input = true;
  	}
  
  	/*
--- 3588,3643 
  	/*
  	 * Check for constant inputs and especially constant-NULL inputs.
  	 */
! 	if (OidIsValid(funcform->provariadic) && funcform->provariadic != ANYOID)
  	{
! 		foreach(arg, args)
! 		{
! 			if (lnext(arg) != NULL)
! 			{
! /* Check non variriadic arguments. */
! if (IsA(lfirst(arg), Const))
! 	has_null_input |= ((Const *) lfirst(arg))->constisnull;
! else
! 	has_nonconst_input = true;
! 			}
! 			else
! 			{
! Node *vararg = lfirst(arg);
! ListCell	*lc;
! 
! /* Variadic argument is array. Check fields */
! if (IsA(vararg, ArrayExpr))
! {
! 	foreach(lc, ((ArrayExpr *) vararg)->elements)
! 	{
! 		if (IsA(lfirst(lc), Const))
! 			has_null_input |= ((Const *) lfirst(lc))->constisnull;
! 		else
! 			has_nonconst_input = true;
! 	}
! }
! else 
! {
! 	Assert(IsA(vararg, Const));
! 	
! 	if (((Const *) vararg)->constisnull)
! 		has_null_input = true;
! 	else
! 		has_null_input |= ARR_HASNULL(DatumGetArrayTypeP(((Const *) vararg)->constvalue));
! }
! 			}
! 		}
! 	}
! 	else
! 	{
! 		/* simpler and faster form for nonvariadic function or variadic "any" */
! 		foreach(arg, args)
! 		{
! 			if (IsA(lfirst(arg), Const))
! has_null_input |= ((Const *) lfirst(arg))->constisnull;
! 			else
! has_nonconst_input = true;
! 		}
  	}
  
  	/*
*** ./src/backend/utils/fmgr/fmgr.c.orig	2010-01-07 05:53:34.0 +0100
--- ./src/backend/utils/fmgr/fmgr.c	2010-02-09 14:42:42.0 +0100
***
*** 18,23 
--- 18,24 
  #include "access/tuptoaster.h"
  #include "catalog/pg_language.h"
  #include "catalog/pg_proc.h"
+ #include "catalog/pg_type.h"
  #include "executor/functions.h"
  #include "executor/spi.h"
  #include "lib/stringinfo.h"
***
*** 199,204 
--- 200,206 
  		finfo->fn_nargs = fbp->nargs;
  		finfo->fn_strict = fbp->strict;
  		finfo->fn_retset = fbp->retset;
+ 		finfo->fn_vararg = fbp->vararg;
  		finfo->fn_stats = TRACK_FUNC_ALL;		/* ie, never track */
  		finfo->fn_addr = fbp->func;
  		finfo->fn_oid = functionId;
***
*** 216,221 
--- 218,224 
  	finfo->fn_nargs = procedureStruct->pronargs;
  	finfo->fn_strict = procedureStruct->proisstrict;
  	finfo->fn_retset = procedureStruct->proretset;
+ 	finfo->fn_vararg = OidIsValid(procedureStruct->provariadic) && procedureStruct->provariadic != ANYOID;
  
  	/*
  	 * If it has prosecdef set, or non-null proconfig, use
*** ./src/backend/utils/Gen_fmgrtab.pl.orig	2010-01-05 21:23:32.0 +0100
--- ./src/backend/utils/Gen_fmgrtab.pl	2010-02-09 14:23:04.0 +0100
***
*** 77,82 
--- 77,83 
  oid=> $row->{oid},
  strict => $row->{proisstrict},
  retse

Re: [HACKERS] Pathological regexp match

[Joachim Wieland]

On Mon, Feb 8, 2010 at 6:07 PM, David E. Wheeler  wrote:
> On Feb 8, 2010, at 5:15 AM, Magnus Hagander wrote:
>
>> The text is about 180Kb. PostgreSQL takes ~40 seconds without the
>> patch, ~36 seconds with it, to extract the match from it. Perl takes
>> 0.016 seconds.
>
> Obviously we need to support Perl regular expressions in core. Not PCRE, but 
> Perl. ;-P

FWIW, PCRE is BSD licensed, so we could actually include it... Would
be interesting to see how it performs on the pattern at hand.


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] About psycopg2 (by its author)

[Kevin Grittner]

[Resending; I accidentally failed to copy the list.]

Federico Di Gregorio  wrote:
 
> the logical choice is plain LGPL3. I am open to motivated
> suggestions about other licenses but I'll ignore such crap as "BSD
> is more open than LGPL".
 
Well, I don't know about "more open", but I find the PostgreSQL BSD-
derived license easier to understand.
 
http://wiki.postgresql.org/wiki/FAQ#What_is_the_license_of_PostgreSQL.3F
 
http://www.gnu.org/licenses/lgpl-3.0.txt
 
And I suspect that some of these requirements of LGPL might make it
unlikely to be considered as something that can be shipped with
PostgreSQL.  (Yeah, I'm looking at *you*, section 4.)
 
-Kevin
 
> DISCLAIMER. If I receive a message from you, you are agreeing
> that:
>  1. I am by definition, "the intended recipient".
>  2. All information in the email is mine to do with as I see fit
>  and make such financial profit, political mileage, or good joke
>  as it lends itself to. In particular, I may quote it on USENET or
>  the WWW.
>  3. I may take the contents as representing the views of your
>  company.
>  4. This overrides any disclaimer or statement of confidentiality
>  that may be included on your message.

Nice!  :-)



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

Re: [HACKERS] buildfarm breakage

[Magnus Hagander]

On Tue, Feb 9, 2010 at 13:52, Magnus Hagander  wrote:
> On Tue, Feb 9, 2010 at 02:20, Tom Lane  wrote:
>> Andrew Dunstan  writes:
>>> Mingw builds are missing in6addr_any in backend/libpq/auth.c, added by a
>>> recent RADIUS support fix. Looks like we might need to include win32.h
>>> in there.
>>
>> That was discussed already.  I assume Magnus is going to address it
>> as soon as he gets back from FOSDEM.
>
> Yes.
>
> It's not quite that simple, though. It *is* in the win32 header for
> mingw. As an extern. But it's not present in the libraries on mingw
> (it *is* present on msvc). So we need to define the actual contents of
> it. I'll look at getting a mingw box up to fix that as soon as
> possible, hopefully today.

Here's a patch that "fixes" this. I put it locally for the radius
authentication for now, since we don't use this anywhere else. Should
we put this in /port/ somewhere, or is this good for now?


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/


mingw_ipv6.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] Avoiding bad prepared-statement plans.

[Mark Mielke]

On 02/09/2010 08:46 AM, Jeroen Vermeulen wrote:
This sounds like a really nice to have feature. Maybe it'd also be 
possible to skip replanning between executes if the current bound 
values are 'indexwise-equivalent' to the values used at previous 
planning, i.e. nothing in the statistics indicates that execution 
cost would be (much) different. Are there more ways to cut down on 
planning time? Obviously some plannedstatement/plannerinfo structures 
could be kept, but maybe it'd also be possible to plan only that part 
of the join tree where the params are used in a scan/join qual.


I think we should be careful not to over-think this.  Planning isn't 
*that* costly, so apply Amdahl's Law liberally.  I'm proposing some 
easy things we could do without adding much overhead or maintenance 
burden; I've been assuming that getting intimate with the planner 
would risk those advantages.


In a current commercial app we have that uses JDBC and prepared plans 
for just about everything, it regularly ends up with execution times of 
30+ milliseconds when a complete plan + execute would take less than 1 
millisecond.


PostgreSQL planning is pretty fast. In terms of not over thinking things 
- I think I would even prefer an option that said "always re-plan 
prepared statements" as a starting point. If it happened to become 
smarter over time, such that it would have invalidation criteria that 
would trigger a re-plan, that would be awesome, but in terms of what 
would help me *today* - being able to convert prepared plans into just a 
means to use place holders would help me today on certain real 
applications in production use right now.


Cheers,
mark

--
Mark Mielke


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

Re: [HACKERS] Avoiding bad prepared-statement plans.

[Richard Huxton]

On 09/02/10 14:25, Jeroen Vermeulen wrote:

Richard Huxton wrote:


= Actual-cost threshold =

Also stop using the generic plan if the statement takes a long time to
run in practice.


Do you mean:
1. Rollback the current query and start again
2. Mark the plan as a bad one and plan again next execute

If you can figure out how to do #1 then you could probably do it for
all queries, but I'm guessing it's far from simple to implement.


I'm talking about #2. As a matter of fact #1 did come up in one of those
discussions, but how do you know you're not killing the query juuust
before it'd done, and then maybe executing a different plan that's no
better?


Ah, you'd need to be smarter when planning and also remember the 
expected rows from each node. That way if your (index driven) inner node 
was expecting 3 rows you could mark it to force a cancellation if it 
returns (say) 30 or more. You'd allow more slack in later processing and 
less slack earlier on where a bad estimate can explode the final number 
of rows.


Or, there is always the case where we reverse-search an index to find 
the last 10 messages in a group say, but the particular group in 
question hasn't had a comment for months, so you trawl half the table. 
People regularly get bitten by that, and there's not much to be done 
about it. If we could abort when it looks like we're in worst-case 
rather than best-case scenarios then it would be one less thing for 
users to worry about.



= Plan refresh =

Periodically re-plan prepared statements on EXECUTE. This is also a
chance for queries that were being re-planned every time to go back to a
generic plan.


Presumably some score based on update stats and vacuum activity etc.


I was thinking of something very simple: re-do whatever we'd do if the
statement were only being prepared at that point.


Yes, I thought so, the scoring was for *when* to decide to cancel the 
old plan. I suppose total query-time would be another way to decide this 
plan needs reworking.



The good side of all these ideas is good indeed. The bad side is plan
instability. Someone somewhere will have a generic plan that turns out
better than the specific plan (due to bad stats or config settings or
just planner limitations). The question is (I guess): How many more
winners will there be than losers?


That's a good and surprising point, and therefore I'd like to draw
attention away to a different point. :-)

Yes, there will be losers in the sense that people may have optimized
their use of prepared statements to whatever the current planner does.
Maybe somebody out there even deliberately uses them to trick the
planner into a different plan. But that is always going to happen; we're
aiming for better plans, not for giving more detailed control over them.
If you really can't take a change, don't upgrade.

The competing point is: people out there may currently be forgoing
prepared statements entirely because of erratic performance. To those
people, if we can help them, it's like having a new feature.


Oh, I'm persuaded, but that doesn't really get you anywhere :-)

--
  Richard Huxton
  Archonet Ltd

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

Re: [HACKERS] About psycopg2 (by its author)

[Magnus Hagander]

On Tue, Feb 9, 2010 at 15:28, Federico Di Gregorio  wrote:
> On 09/02/2010 15:22, Dave Page wrote:
>> On Tue, Feb 9, 2010 at 1:56 PM, Federico Di Gregorio  wrote:
>>> Btw, I was
>>> at FOSDEM as probably other PostgreSQL people were and all this could
>>> have been discussed while drinking a couple of beers if only someone
>>> cared to contact me.
>>
>> Hmm, I resent that. As one of the people at FOSDEM a) I had no idea
>> you were there and b) I was extremely busy pretty much the whole time
>> I was there, and c) like what I would guess is the majority of PG
>> people there, I don't use python or psycopg.
>>
>> Meeting at conferences is great (and I wish you had popped over to the
>> PG booth to say hi *and* attended the database dinner with us), but
>> it's no substitute for the mailing lists in which everyone can be
>> involved.
>
> That would have been great. I fact I stepped by the PG boot and had a
> little chitchat with some people but I don't usually go around yelling
> «Oh ahi!, I am psycopg developer». Anyway, meeting other people involved
> in free software is always great, so let hope to get all there next
> FOSDEM or to meet at some other conference.

You should! People want to know that :-) I know I would've wanted it...

And BTW, I think none of the people who were involved in that thread
on the list were actually at FOSDEM... (well, Peter was there, but he
wasn't active in the thread beyond an initial post or so)


-- 
 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] TCP keepalive support for libpq

[Andrew Chernow]

Tollef Fog Heen wrote:

(please Cc me on replies, I am not subscribed)

Hi,

libpq currently does not use TCP keepalives.  This is a problem in our
case where we have some clients waiting for notifies and then the
connection is dropped on the server side.  The client never gets the FIN
and thinks the connection is up.  The attached patch unconditionally
adds keepalives.  I chose unconditionally as this is what the server
does.  We didn't need the ability to tune the timeouts, but that could
be added with reasonable ease.


ISTM that the default behavior should be keep alives disabled, as it is 
now, and those wanting it can just set it in their apps:


setsockopt(PQsocket(conn), SOL_SOCKET, SO_KEEPALIVE, ...)

If you really want libpq to manage this, I think you need to expose the 
probe interval and timeouts.  There should be some platform checks as 
well.  Check out...


http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg128603.html

--
Andrew Chernow
eSilo, LLC
every bit counts
http://www.esilo.com/

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

Re: [HACKERS] Avoiding bad prepared-statement plans.

[Jeroen Vermeulen]

Andres Freund wrote:


= Actual-cost threshold =

Also stop using the generic plan if the statement takes a long time to
run in practice.  Statistics may have gone bad.  It could also be a
one-off due to a load peak or something, but that's handled by:


That is not that easy. It means that you have to use savepoints enclosing each 
and every execution of a prepared statement because the query could have 
sideeffects. Which wouldnt be terribly efficient...


This is not within an execution of the statement, but across executions. 
 So the next execution learns from the previous result.  So I'm not 
talking about aborting the ongoing execution.  Sorry for being unclear.



Jeroen

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

Re: [HACKERS] About psycopg2 (by its author)

[Federico Di Gregorio]

On 09/02/2010 15:22, Dave Page wrote:
> On Tue, Feb 9, 2010 at 1:56 PM, Federico Di Gregorio  wrote:
>> Btw, I was
>> at FOSDEM as probably other PostgreSQL people were and all this could
>> have been discussed while drinking a couple of beers if only someone
>> cared to contact me.
> 
> Hmm, I resent that. As one of the people at FOSDEM a) I had no idea
> you were there and b) I was extremely busy pretty much the whole time
> I was there, and c) like what I would guess is the majority of PG
> people there, I don't use python or psycopg.
>
> Meeting at conferences is great (and I wish you had popped over to the
> PG booth to say hi *and* attended the database dinner with us), but
> it's no substitute for the mailing lists in which everyone can be
> involved.

That would have been great. I fact I stepped by the PG boot and had a
little chitchat with some people but I don't usually go around yelling
«Oh ahi!, I am psycopg developer». Anyway, meeting other people involved
in free software is always great, so let hope to get all there next
FOSDEM or to meet at some other conference.

federico

-- 
Federico Di Gregorio 
DISCLAIMER. If I receive a message from you, you are agreeing that:
 1. I am by definition, "the intended recipient".
 2. All information in the email is mine to do with as I see fit and
 make such financial profit, political mileage, or good joke as it lends
 itself to. In particular, I may quote it on USENET or the WWW.
 3. I may take the contents as representing the views of your company.
 4. This overrides any disclaimer or statement of confidentiality that
 may be included on your message.



signature.asc
Description: OpenPGP digital signature

Re: [HACKERS] Avoiding bad prepared-statement plans.

[Jeroen Vermeulen]

Richard Huxton wrote:


= Actual-cost threshold =

Also stop using the generic plan if the statement takes a long time to
run in practice.


Do you mean:
1. Rollback the current query and start again
2. Mark the plan as a bad one and plan again next execute

If you can figure out how to do #1 then you could probably do it for all 
queries, but I'm guessing it's far from simple to implement.


I'm talking about #2.  As a matter of fact #1 did come up in one of 
those discussions, but how do you know you're not killing the query 
juuust before it'd done, and then maybe executing a different plan 
that's no better?




= Plan refresh =

Periodically re-plan prepared statements on EXECUTE. This is also a
chance for queries that were being re-planned every time to go back to a
generic plan.


Presumably some score based on update stats and vacuum activity etc.


I was thinking of something very simple: re-do whatever we'd do if the 
statement were only being prepared at that point.



The good side of all these ideas is good indeed. The bad side is plan 
instability. Someone somewhere will have a generic plan that turns out 
better than the specific plan (due to bad stats or config settings or 
just planner limitations). The question is (I guess): How many more 
winners will there be than losers?


That's a good and surprising point, and therefore I'd like to draw 
attention away to a different point.  :-)


Yes, there will be losers in the sense that people may have optimized 
their use of prepared statements to whatever the current planner does. 
Maybe somebody out there even deliberately uses them to trick the 
planner into a different plan.  But that is always going to happen; 
we're aiming for better plans, not for giving more detailed control over 
them.  If you really can't take a change, don't upgrade.


The competing point is: people out there may currently be forgoing 
prepared statements entirely because of erratic performance.  To those 
people, if we can help them, it's like having a new feature.



Jeroen

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

Re: [HACKERS] About psycopg2 (by its author)

[Dave Page]

On Tue, Feb 9, 2010 at 1:56 PM, Federico Di Gregorio  wrote:
> Btw, I was
> at FOSDEM as probably other PostgreSQL people were and all this could
> have been discussed while drinking a couple of beers if only someone
> cared to contact me.

Hmm, I resent that. As one of the people at FOSDEM a) I had no idea
you were there and b) I was extremely busy pretty much the whole time
I was there, and c) like what I would guess is the majority of PG
people there, I don't use python or psycopg.

Meeting at conferences is great (and I wish you had popped over to the
PG booth to say hi *and* attended the database dinner with us), but
it's no substitute for the mailing lists in which everyone can be
involved.

/D

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

Re: [HACKERS] [CFReview] Red-Black Tree

[Teodor Sigaev]

Good idea, implemented.


Hmm.  I think your implementation is prone to overflow in two places -
both when computing step, and also when stepping through the array.


Pls, point me, I don't see that
!   step |= (step >>  1);
!   step |= (step >>  2);
!   step |= (step >>  4);
!   step |= (step >>  8);
!   step |= (step >> 16);
!   step ++;
!   step >>= 1;
!
!   while(step > 0) {
!   int i;

!   for (i = step-1; i < nentry; i += 2 * step)
!   ginInsertEntry(accum, heapptr, attnum, entries[i]);

!   step >>= 1; /* /2 */
!   }



Proposed revision attached, with also some rewriting of the comment
for that function.


make check fails with your patch:

#3  0x083d2b50 in ExceptionalCondition (conditionName=Could not find the frame 
base for "ExceptionalCondition".

) at assert.c:57
#4  0x081086b6 in ginAppendData (old=0x287f2030, new=0x287f2044, arg=0xbfbfd5e4) 
at ginbulk.c:48

#5  0x083f5632 in rb_insert (rb=0x2acfe610, data=0x287f2044) at rbtree.c:359
#6  0x08108968 in ginInsertEntry (accum=0xbfbfd5e4, heapptr=0x28711af4, 
attnum=1, entry=2139062143) at ginbulk.c:135
#7  0x08108ad9 in ginInsertRecordBA (accum=0xbfbfd5e4, heapptr=0x28711af4, 
attnum=1, entries=0x2ac77068, nentry=6) at ginbulk.c:202

--
Teodor Sigaev   E-mail: teo...@sigaev.ru
   WWW: http://www.sigaev.ru/

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

Re: [HACKERS] TCP keepalive support for libpq

[Tollef Fog Heen]

]] Magnus Hagander 

| Seems reasonable to add this. Are there any scenarios where this can
| cause trouble, that would be fixed by having the ability to select
| non-standard behavior?

Well, it might be unwanted if you're on a pay-per-bit connection such as
3G, but in this case, it just makes the problem a bit worse than the
server keepalive already makes it – it doesn't introduce a new problem.

| I don't recall ever changing away from the standard behavior in any of
| my deployments, but that might be platform dependent?

If you were (ab)using postgres as an IPC mechanism, I could see it being
useful, but not in the normal case.

| If not, I think this is small and trivial enough not to have to push
| back for 9.1 ;)

\o/

-- 
Tollef Fog Heen 
UNIX is user friendly, it's just picky about who its friends are

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

Re: [HACKERS] Avoiding bad prepared-statement plans.

[Andres Freund]

On Tuesday 09 February 2010 13:08:54 Jeroen Vermeulen wrote:
> I've been discussing this with Josh, Heikki, and Peter E. over the past
> few weeks.
> 
> As Peter observed years ago, prepared statements can perform badly
> because their plans are overly generic.  Also, statistics change and
> sometimes plans should change with them.  It would be nice if we could
> avoid users having to worry about these things.
> 
> I have some ideas that I'm willing to work on, if people agree that
> they're useful.  These are simple changes; the goal is to avoid
> pathological performance at minimal cost, not to make prepared
> statements faster than parameterized ones for everyone.  The ideas
> interact in various ways.
> 
> 
> = Projected-cost threshold =
> 
> If a prepared statement takes parameters, and the generic plan has a
> high projected cost, re-plan each EXECUTE individually with all its
> parameter values bound.  It may or may not help, but unless the planner
> is vastly over-pessimistic, re-planning isn't going to dominate
> execution time for these cases anyway.
> 
> = Actual-cost threshold =
> 
> Also stop using the generic plan if the statement takes a long time to
> run in practice.  Statistics may have gone bad.  It could also be a
> one-off due to a load peak or something, but that's handled by:
That is not that easy. It means that you have to use savepoints enclosing each 
and every execution of a prepared statement because the query could have 
sideeffects. Which wouldnt be terribly efficient...

Andres

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

[HACKERS] About psycopg2 (by its author)

[Federico Di Gregorio]

First of all let me say that from such incredible hackers as the
PostgreSQL people I'd have expected the ability to find my email address
and maybe keep me or (even better) the psycopg mailing list in CC. That
would have avoided a lot of confusion both on the license and the status
of psycopg2. If you don't feel too offended by reading this incipit then
we're ready to go on and try ot have a useful discussion (oh, yes, I can
read and write basic english so I won't need any translator.) Btw, I was
at FOSDEM as probably other PostgreSQL people were and all this could
have been discussed while drinking a couple of beers if only someone
cared to contact me.

First point, the license. It started as GPL2+ and sort of grow off of
various comments and user needs. I agree that some of the reasons for
such a complex license don't exist anymore and we're ready for a change.
Note that I strongly don't want a final user to receive a closed,
proprietary version of psycopg2 without the ability to switch (if she
wants) to the "official" version released as free software. But I also
understand that lots of people just want to keep their software
proprietary (the current license already allows that). So the logical
choice is plain LGPL3. I am open to motivated suggestions about other
licenses but I'll ignore such crap as "BSD is more open than LGPL".

Second point, the technical discussion about psycopg2 features and bugs.
We have a mailing list, so please subscribe to it and write there any
comments or requests about the code. The list is alive and nobody ever
complained to not have received an answer.

Finally, I am really happy that after years during which psycopg2 was
used in a lot of projects by a lot of different users eventually the
PostgreSQL people noted it. ;)

Have fun,
federico

-- 
Federico Di Gregorio 
DISCLAIMER. If I receive a message from you, you are agreeing that:
 1. I am by definition, "the intended recipient".
 2. All information in the email is mine to do with as I see fit and
 make such financial profit, political mileage, or good joke as it lends
 itself to. In particular, I may quote it on USENET or the WWW.
 3. I may take the contents as representing the views of your company.
 4. This overrides any disclaimer or statement of confidentiality that
 may be included on your message.



signature.asc
Description: OpenPGP digital signature

Re: [HACKERS] [CFReview] Red-Black Tree

[Alvaro Herrera]

Robert Haas escribió:
> On Mon, Feb 8, 2010 at 3:05 PM, Alvaro Herrera
>  wrote:

> > How do we now that it works?
> 
> Visual inspection?  It's not very complicated.

Well, that works if you assume the trivial/usual malloc/free coding
style, but it fails in the hypothetical scenario I described earlier.
You could as well say that each rbtree must provide a memory context
that is going to be deleted when the tree is freed, instead of freeing
nodes one by one (and in fact it looks more efficient to do it that way
... except that we'd have to get in the business of strcpy'ing the
node's data).  There's no way to know how this stuff is going to be
used, so if it's not going to be used now, I think we shouldn't
implement it.  That's why I looked at the knngist patch too.

But hey, not that i care all that much either -- it's not a lot of code;
a couple dozen lines at most, and not complex.

> > (What, for example, if we were to allocate multiple nodes in a single
> > palloc chunk?  I'm not familiar with this stuff but that seems
> > plausible)
> 
> Well, then you could have the freefunc do something ((MyStruct *)
> a)->is_allocated = false.

Hmm, but isn't "a" gone at that point?

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

Re: [HACKERS] Avoiding bad prepared-statement plans.

[Yeb Havinga]

Jeroen Vermeulen wrote:
I've been discussing this with Josh, Heikki, and Peter E. over the 
past few weeks.

Is this searchable in the archives? I'm interested in ideas discussed.
If a prepared statement takes parameters, and the generic plan has a 
high projected cost, re-plan each EXECUTE individually with all its 
parameter values bound.  It may or may not help, but unless the 
planner is vastly over-pessimistic, re-planning isn't going to 
dominate execution time for these cases anyway.
This sounds like a really nice to have feature. Maybe it'd also be 
possible to skip replanning between executes if the current bound values 
are 'indexwise-equivalent' to the values used at previous planning, i.e. 
nothing in the statistics indicates that execution cost would be (much) 
different. Are there more ways to cut down on planning time? Obviously 
some plannedstatement/plannerinfo structures could be kept, but maybe 
it'd also be possible to plan only that part of the join tree where the 
params are used in a scan/join qual.


regards,
Yeb Havinga


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

Re: [HACKERS] Avoiding bad prepared-statement plans.

[Richard Huxton]

On 09/02/10 12:08, Jeroen Vermeulen wrote:


= Projected-cost threshold =


[snip - this is the simple bit. Sounds very sensible. ]


= Actual-cost threshold =

Also stop using the generic plan if the statement takes a long time to
run in practice.


Do you mean:
1. Rollback the current query and start again
2. Mark the plan as a bad one and plan again next execute

If you can figure out how to do #1 then you could probably do it for all 
queries, but I'm guessing it's far from simple to implement.



= Plan refresh =

Periodically re-plan prepared statements on EXECUTE. This is also a
chance for queries that were being re-planned every time to go back to a
generic plan.


Presumably some score based on update stats and vacuum activity etc.


The good side of all these ideas is good indeed. The bad side is plan 
instability. Someone somewhere will have a generic plan that turns out 
better than the specific plan (due to bad stats or config settings or 
just planner limitations). The question is (I guess): How many more 
winners will there be than losers?


--
  Richard Huxton
  Archonet Ltd

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