Re: [HACKERS] PL/PgSQL "bare" function calls
On Thu, 16 Sep 2004, Greg Stark wrote: > > Neil Conway <[EMAIL PROTECTED]> writes: > > > whereas adding support for CALL to SQL is part of proper support for stored > > procedures. Gavin and I are hoping to send a proposal for the latter to > > -hackers in a few days. > > What is the point of stored procedures being distinct from functions anyways? > Is there any real difference other than the irregular calling syntax? Is there > anything you can't do with functions that you can do with procedures? Or is it > purely a question of satisfying a spec or providing a more Oracle compatible > syntax? SQL-invoked procedures (ie, stored procedures) differ in two ways from functions. These are: 1) Procedures do not return a value. 2) Arguments have 'parameter modes'. These modes are: IN - an input parameter, which has been initialised to some value and is read-only; OUT - an uninitialised parameter which can be written to; IN OUT - which has the properties of each of the above. What this actually means is that you can declare a procedure as follows: CREATE PROCEDURE foo(IN bar INT, OUT baz INT, OUT bat INT, ...) That is, a procedure can actually 'return' many values from a call. We can do this with composite types but, speaking from experience, this can make migration from PL/SQL just that much harder. The other thing which SQL-invoked procedures necessitate is support for the concept of a 'variable'. The reason being that if you use CALL in top level SQL, you cannot make reference to a field of a relation in any meaningful way and passing a column reference, for example, as an OUT parameter does make any sense. So, SQL2003 defines a few types of variables but the one people may be most familiar with is the host parameter. This is a named variable which is referenced as :foo. I'm putting together a much more detailed email on all this which I hope to send out in the next few days. Thanks, Gavin ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] PL/PgSQL "bare" function calls
Neil Conway <[EMAIL PROTECTED]> writes: > whereas adding support for CALL to SQL is part of proper support for stored > procedures. Gavin and I are hoping to send a proposal for the latter to > -hackers in a few days. What is the point of stored procedures being distinct from functions anyways? Is there any real difference other than the irregular calling syntax? Is there anything you can't do with functions that you can do with procedures? Or is it purely a question of satisfying a spec or providing a more Oracle compatible syntax? -- greg ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] libpq and prepared statements progress for 8.0
Oliver Jowett <[EMAIL PROTECTED]> writes: > > Well benefits that boil down to "Java sucks" aren't very convincing. Perl > > suffers from no such handicap. > > Arguing that Java-specific benefits are not convincing benefits for a JDBC > driver because you don't get them in Perl seems a bit odd to me. You're not > implementing the driver in Perl! Er, we're kind of on two different wavelengths here. What I'm trying to determine are what are the benefits of writing a pure-perl driver versus one that implements the protocol in a C module, versus one that merely interfaces with libpq. The current Perl module interfaces with libpq. The closest analogue to use for comparison is the JDBC driver which is a pure-Java implementation. So the benefits and disadvantages the JDBC driver faces are useful data points. However benefits that arise purely because of quirks of Java and don't relate to Perl are less relevant than benefits and disadvantages that are more general. I wasn't trying to criticize the decisions behind the JDBC implementation. It may well be that the choice that makes sense for Java isn't the same as the choice that makes sense in other languages. Or it may be that there are lessons that can be learned from Java that generalize to other languages and a pure perl implementation may make sense. -- greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] libpq and prepared statements progress for 8.0
Greg Stark wrote: Oliver Jowett <[EMAIL PROTECTED]> writes: There *are* benefits to implementing the protocol directly. First on my personal list is that our Java application would not be able to use postgresql at all if the driver required JNI/libpq. Well benefits that boil down to "Java sucks" aren't very convincing. Perl suffers from no such handicap. Arguing that Java-specific benefits are not convincing benefits for a JDBC driver because you don't get them in Perl seems a bit odd to me. You're not implementing the driver in Perl! Anyway, it's not a language issue so much as a support issue. We're not in a position to build and support libpq and a JNI interface to it on a large range of hardware platforms, but we can get 3rd party support for JVMs on those platforms just fine. Incidentally, does the JDBC spec really allow for multiple-statement queries at all? No, but it's a common extension, and earlier driver versions (talking only V2) supported it. -O ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] pg_dump --exclude-schema=foo
We have discovered an interesting locking scenario with Slony-I that is pointing to a use for the ability to exclude certain schemas from pg_dump. The situation is that when a "full" pg_dump kicks off, a Slony-I "create sync" event, which expects to "LOCK slony_schema.sl_event;", is blocked from getting the exclusive lock that it desires. Everything else then falls behind that, as they are waiting for the lock to get dropped off. The solution is easy enough, in that (at present) the interesting data is in a single database schema. Running... pg_dump --schema=ourdata does the trick. What would be nice would be the ability to consciously _exclude_ schemas, where the nice way would be --exclude-schema=_slony_schema If someone has some "round tuits," I'll have to catch up on sleep... -- (format nil "[EMAIL PROTECTED]" "cbbrowne" "ntlug.org") http://www3.sympatico.ca/cbbrowne/languages.html Save your burned out bulbs for me, I'm building my own dark room. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] A new article about RAID and PostgreSQL
Good morning Let me introduce, I'm Ricardo Rezende and I'm SQL Magazine subeditor, from Brazil (http://www.sqlmagazine.com.br.). My goal in this first contact is to solve a doubt about PostgreSQL RDBMS. I'm writing an article about redundant storage technology, called RAID. The first part of the article can be found in http://www.sqlmagazine.com.br/colunistas.asp?artigo=Colunistas/RicardoRezende/06_Raid_P1.asp My ideia is to put, in the end of the article, a note about the better configuration of RAID to use with PostgreSQL and the reasons, including the reference to the autor/link to this information. Could you send me this information? Our magazine is being a reference between DBAs and Database Developers in Brazil and that is the reason to write "oficial" papers about PostgreSQL Thank you very much and I'm waiting for a return of this e-mail. Cordially Ricardo Rezende Subeditor SQL Magazine [EMAIL PROTECTED]
Re: [HACKERS] beta1 & beta2 & Windows & heavy load
Tom Lane schrieb: Had you been running the server for very long before forcing the error, I don't think this would have happened, because the buffer hashtable would have already expanded to its full working size. Yes, you are right - this was a fresh started pgserver. Once we fix subxacts to not hold their XID locks after subcommit, the probability of a problem should go back down to the same low value that's allowed us to ignore this risk for the past many years. I think so, too. Daniel ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] tweaking MemSet() performance - 7.4.5
HI,
I'm using 7.4.5 on Mac OS X (G5) and was profiling it to see why it is
SO SLOW at committing inserts and deletes into a large database. One
of the many slowdowns was from MemSet. I found an old (2002) thread
about this and retried the tests (see below). The main point is that
the system memset crushes pg's!! Is it possible to add a define to call
the system memset at build time! This probably isn't the case on other
systems.
I wanted to know the size of FunctionCallInfoData (in execQual.c)
because the profiler said that if it was over 128 then use the system
call.
Here are my results:
pgMemSet
* 64
0.410u 0.000s 0:00.42 97.6% 0+0k 0+0io 0pf+0w
* 128
0.600u 0.000s 0:00.61 98.3% 0+0k 0+0io 0pf+0w
* 176 Size of fcinfo is 176, used in execQual.c which was being very
slow here!
0.790u 0.000s 0:00.79 100.0%0+0k 0+0io 0pf+0w
* 256
1.040u 0.000s 0:01.08 96.2% 0+0k 0+0io 0pf+0w
* 512
2.030u 0.000s 0:02.04 99.5% 0+0k 0+0io 0pf+0w
* 1024
3.950u 0.010s 0:03.94 100.5%0+0k 0+0io 0pf+0w
* 2048
7.710u 0.000s 0:07.75 99.4% 0+0k 0+0io 0pf+0w
* 4096
15.390u 0.000s 0:15.37 100.1% 0+0k 0+0io 0pf+0w
system memset
* 64
0.260u 0.000s 0:00.25 104.0%0+0k 0+0io 0pf+0w
* 128
0.310u 0.000s 0:00.31 100.0%0+0k 0+0io 0pf+0w
* 176 Size of fcinfo is 176
0.300u 0.010s 0:00.30 103.3%0+0k 0+0io 0pf+0w
* 256
0.310u 0.000s 0:00.30 103.3%0+0k 0+0io 0pf+0w
* 512
0.350u 0.000s 0:00.33 106.0%0+0k 0+0io 0pf+0w
* 1024
0.590u 0.010s 0:00.63 95.2% 0+0k 0+0io 0pf+0w
* 2048
0.780u 0.000s 0:00.77 101.2%0+0k 0+0io 0pf+0w
* 4096
1.320u 0.000s 0:01.33 99.2% 0+0k 0+0io 0pf+0w
#include
#include "postgres.h"
#include "fmgr.h"
#undef MEMSET_LOOP_LIMIT
#define MEMSET_LOOP_LIMIT 100
int
main(int argc, char **argv)
{
int len = atoi(argv[1]);
char buffer[len];
long long i;
FunctionCallInfoData fcinfo;
printf("Size of fcinfo is %d\n", sizeof(fcinfo));
for (i = 0; i < 990; i++)
MemSet(buffer, 0, len);
//memset(buffer, 0, len);
return 0;
}
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [HACKERS] libpq and prepared statements progress for 8.0
Oliver Jowett <[EMAIL PROTECTED]> writes:
> Greg Stark wrote:
>
> > I was pretty shocked when I heard that JDBC implements the low level protocol
> > itself. It seems like a dead-end strategy to me. Any new protocol innovations
> > need to be implemented in every driver. Every implementation gets the chance
> > to reimplement the same bugs and same inefficiencies over and over again.
>
> There *are* benefits to implementing the protocol directly. First on my
> personal list is that our Java application would not be able to use postgresql
> at all if the driver required JNI/libpq.
Well benefits that boil down to "Java sucks" aren't very convincing. Perl
suffers from no such handicap. There are tons of Perl modules that have C
implementations. Sometimes simply for speed or convenience. Perl makes it
pretty convenient to write modules in C or interface with C libraries without
too much pain.
Hell, it's not the recommended way to make modules but for convenience you
can't really beat:
perl -e 'use Inline C=>q{void greet() {printf("Hello, world\n");}}; greet'
The only benefit for a pure-perl driver would be the ease of use for Windows
users. And that only really matters because Windows users tend to be more
averse to using a compiler and often don't even have one installed.
> There are also some things in the JDBC API that seem hard to map to the current
> libpq API, e.g. streaming parameter data from a Java stream without taking an
> intermediate copy.
Ah, this is another problem. It boils down to "libpq sucks" at least for the
needs of a driver writer. I'm becoming convinced that libpq's problem was that
it's trying to satisfy two users, C programmers using postgres directly and
driver authors who just want a low level interface to the protocol.
What I'm trying to decide is whether the best course of action is to write a
different implementation for a perl driver (either in Perl or in C) or to fix
libpq to be more useful for driver authors.
> The protocol implementation is not really all that complex. The implementation
> for both V2 and V3 weighs in at ~6k lines of Java out of ~38k total, and much
> of that is connection-state juggling that we'd have to do anyway if using libpq
> (working out when to send BEGIN, breaking up multiple-statement queries into
> individual statements and matching the results up, managing portal state, etc).
I'll have to look at these things more closely. I wonder whether it makes
sense for JDBC, ODBC, DBD::Pg to all have independent implementations of these
features.
Incidentally, does the JDBC spec really allow for multiple-statement queries
at all?
--
greg
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] PL/PgSQL "bare" function calls
On Thu, 2004-09-16 at 01:19, Andrew Dunstan wrote: > ISTM that this is being done at the wrong level anyway. I'd like to see > a facility available in our SQL, e.g. > > CALL foo(); > > with the restriction that foo() should be declared to return void. I think these are two distinct issues. The patch I sent along is intended to make it more natural to invoke functions (and eventually procedures) from PL/PgSQL, whereas adding support for CALL to SQL is part of proper support for stored procedures. Gavin and I are hoping to send a proposal for the latter to -hackers in a few days. -Neil ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PostgreSQL Core Committee Welcomes New Member
On Sep 16, 2004, at 7:52 AM, Marc G. Fournier wrote: In recognition of his role as lead developer on the internationalization front, as well as his invaluble work in both the build and release processes, Peter Eisentraut has been invited, and has accepted, to join the Core Committee. Congratulations, Peter! Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostgreSQL Core Committee Welcomes New Member
Mike Mascari <[EMAIL PROTECTED]> writes: >> See http://developer.postgresql.org/bios.php > What ever happened to the idea of specially recognizing Thomas > Lockhart and Vadim Mikheev in a Hackers Emeritus section? I think it's a good idea, but it doesn't look like anyone ever got round to it. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] PostgreSQL Core Committee Welcomes New Member
Tom Lane wrote: Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: Wow - I always thought Peter WAS on the core committee Who is on it? See http://developer.postgresql.org/bios.php What ever happened to the idea of specially recognizing Thomas Lockhart and Vadim Mikheev in a Hackers Emeritus section? Eh? Mike Mascari ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL Core Committee Welcomes New Member
> In recognition of his role as lead developer on the internationalization > front, as well as his invaluble work in both the build and release > processes, Peter Eisentraut has been invited, and has accepted, to join > the Core Committee. Congrats Peter, ... John ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] PostgreSQL Core Committee Welcomes New Member
On Thu, 16 Sep 2004, Christopher Kings-Lynne wrote: In recognition of his role as lead developer on the internationalization front, as well as his invaluble work in both the build and release processes, Peter Eisentraut has been invited, and has accepted, to join the Core Committee. Congratulations, Peter! Wow - I always thought Peter WAS on the core committee Who is on it? See the BIOs on developer.postgresql.org :) Bruce, Tom, Jan, Josh, Myself ... and now, Peter ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] PostgreSQL Core Committee Welcomes New Member
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > Wow - I always thought Peter WAS on the core committee Who is on it? See http://developer.postgresql.org/bios.php regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL Core Committee Welcomes New Member
In recognition of his role as lead developer on the internationalization front, as well as his invaluble work in both the build and release processes, Peter Eisentraut has been invited, and has accepted, to join the Core Committee. Congratulations, Peter! Wow - I always thought Peter WAS on the core committee Who is on it? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 'TID index'
On Wed, Sep 15, 2004 at 10:56:28PM +0100, Simon Riggs wrote: > There are many good ideas out there, yet it is almost impossible to find > somebody else to implement yours! > > The acid test is to try and write it... > > Overall, I agree VACUUM could do with some tuning - and 8.0 has just that. > It needs very careful thought to make sure both concurrency and > recoverability considerations are fully met in any solution you come up > with. Heh, I wasn't even thinking of implentation yet. :) I fully understand the lack of developers. Unfortunately, I have very little idea on the internals of PGSQL, and I'm decidedly not a C coder. I *might* be able to get something hacked up that stores info in a table (since that would mean all the space management stuff would be handled for me). If this is a worthwhile idea can we at least get a TODO? Would it be useful to come up with a high-level design (something I could probably do)? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL Core Committee Welcomes New Member
Marc G. Fournier wrote: In recognition of his role as lead developer on the internationalization front, as well as his invaluble work in both the build and release processes, Peter Eisentraut has been invited, and has accepted, to join the Core Committee. Good work, Peter. Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] PL/PgSQL "bare" function calls
On Thu, 2004-09-16 at 01:05, Tom Lane wrote: > That seems fairly unworkable. For example > > SELECT (2,3,4); > > is valid SQL. Good point. The disambiguation algorithm I suggested isn't sufficient, but I think there ought to be _some_ reasonable algorithm. >From glancing over the SQL commands, I believe SELECT is the only case where a SQL statement starts with a T_WORD token followed by a left parenthesis (correct me if I'm mistaken). If that's the case, one solution would be to just special-case SELECT: if the name of the "function" is 'select', we treat it as a SQL statement and not a function call. Of course, this wouldn't apply if the function name is double-quoted or schema-qualified. Another technique would be to delay distinguishing between these two cases until the function is first invoked; then lookup the function name in pg_proc, and if a candidate function with that name is found, assume it's a function call. I don't really like this technique, though. > Also I'm not sure if you can extend this to cope with > schema-qualified function names. Sorry, I forgot to mention that -- yes, that is intended. -Neil ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Statement parsing problem ?
Replying to my own post, thanks to the assistance of Paul Bort... On Wed, Sep 15, 2004 at 11:43:47PM +1000, Chris Dunlop wrote: > There seems to be a kind of statement parsing problem in 7.4.5 > (from debian postgresql-7.4.5-3, i386). > > Either that, or I'm missing something... > > \echo > \echo Error, from simply swapping the order of t2 and t3 ??? > \echo > > select 1 > from > t1, > t3, > t2 > join t4 on (t4.foo6 = t3.foo5) > where t2.foo3 = t1.foo1 > and t3.foo4 = t1.foo2 ; I'd always thought: FROM t1, t2 join t3 meant: FROM (t1, t2) join t3 but as Paul pointed out, it's actually: FROM t1, (t2 join t3) I.e. in the example above: t2 join t4 on (t4.foo6 = t3.foo5) doesn't work because there's no t3.foo5 on the left of the join. > So is it me, or is this just a bit borken ? It was me! Cheers, Chris. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] PostgreSQL Core Committee Welcomes New Member
On Thu, 2004-09-16 at 08:52, Marc G. Fournier wrote: > In recognition of his role as lead developer on the internationalization > front, as well as his invaluble work in both the build and release > processes, Peter Eisentraut has been invited, and has accepted, to join > the Core Committee. Congratulations, Peter! -Neil ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] x86_64 configure problem
Joe Conway wrote: > One procedural issue did occur to me regarding this kind of change. > It requires someone to run autoconf after applying -- how is that > normally handled? You run autoconf before you commit and then check it in. Please use version 2.53. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] PostgreSQL Core Committee Welcomes New Member
Marc G. Fournier schrieb: > In recognition of his role as lead developer on the internationalization front, as well as his invaluble work in both the build and release processes, Peter Eisentraut has been invited, and has accepted, to join the Core Committee. Glückwunsch! -- Reini Urban http://xarch.tu-graz.ac.at/home/rurban/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] PostgreSQL Core Committee Welcomes New Member
In recognition of his role as lead developer on the internationalization front, as well as his invaluble work in both the build and release processes, Peter Eisentraut has been invited, and has accepted, to join the Core Committee. Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] xact.c state machine redesign
On Wed, Sep 15, 2004 at 01:34:01PM -0400, Tom Lane wrote: > After looking over the state machine in xact.c, I'm thinking of removing > the TBLOCK_SUBENDABORT_ALL and TBLOCK_SUBENDABORT_RELEASE states in > favor of having the ROLLBACK command mark the whole transaction state > stack similarly to what is now done for COMMIT. FYI, this idea works for me. > This isn't any fewer states than we have now, but the states seem much > more clearly organized to me --- in particular, other than the RESTART > states there's full symmetry between outer-level and subtransaction > states. Excellent. This was my initial idea, but I messed up in the transition to savepoints. -- Alvaro Herrera () "Postgres is bloatware by design: it was built to house PhD theses." (Joey Hellerstein, SIGMOD annual conference 2002) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] some PITR performance data with DBT-2
On Wed, Sep 15, 2004 at 09:50:17PM +0100, Simon Riggs wrote: > >Mark Wong wrote > > Hi Simon, > > > > Sorry it has taken so long. Among other things, I doubled the controllers > > and drives on the system I was testing this on. But now I have some data > > against PostgreSQL-8.0beta2. > > > > Thanks very much. > > > Here is the test run with archiving enabled: > > http://www.osdl.org/projects/dbt2dev/results/dev4-010/158/ > > > > Here is the test run with archiving disabled: > > http://www.osdl.org/projects/dbt2dev/results/dev4-010/159/ > > > > > The overall throughput difference between the two runs with archiving > > enabled/disabled was within 1%. > > > > Excellent. I hoped it was that low - my target was < 5%. > > Stats check out with no wierdness in the results. TGFT. > > Also, I notice the tpm figures have gone up some more - have you got new > hardware, or has the PostgreSQL setup been tuned more? Or can it be that > rel8.0 really is that much faster?? It's actually lower than where I was when I started breaking tables out onto separate volumes. I suspect you may be looking at data from a different (and slower) system. Slightly old data from the same system are here: http://www.osdl.org/projects/dbt2dev/results/fs-64bit.html > > Here is sar/iostat/vmstat and oprofile data during the first hour of > > recovery. Total recovery time took about 6.5 hours: > > http://www.developer.osdl.org/markw/pitr/ > > > > That's bad news. My own recovery performance estimates would lead me to hope > that its possible to get the recovery to be quicker than the processes that > wrote the logs, even on a very quick 4 CPU system. I'd be hoping for ~1 > hour, or at least <= 4 hours. > > > I ran the test over a duration of 3 hours (including a 2 hour rampup of > > the driver), as opposed to the 6 hours you originally requested. I > > hope that is ok. > > > > System details, which you may be interested in: > > > > 4 x 1.5 GHz Itanium 2 > > 16GB RAM > > 6 x Compaq Computer Corporation Smart Array 64xx > > 6 x 14 disk 15K RPM drives (split bus) > > > > The database and archive directory were put onto a single LVM volume > > across all 84 drives. > > > > Let me know if I left anything out. > > > > First off, thank you again. > > I've had a look at all the results, but I found a few things: > > - couldnt find postgresql.conf or recovery.conf anywhere, so not sure what > OS command you are using For postgresql.conf parameters, I added "database parameters" link to a "SHOW ALL" command a little late, but it's there now and shows: archive_command | cp %p /opt/misc/archive/%f I've already lost the recovery.done file but I used the command: restore_command = 'cp /opt/misc/archive/%f %p' > - log files were very large indeed due to the SPI error messages, so I > haven't been able to download those properly for analysis - any chance you > could grep out the SPI stuff, so I can see the archive and restore commands? Ok, there should be a log-sans-spi.txt.gz available now. > Stats I'd be interested in for analysing recovery performance would be: > - how many log files in total were archived/restored I did a line count of "archived transaction log file" and got 7604. Unforunitely I don't have the output for the restore anymore. > - where were they archived to Into a separate directory on the same volume with the rest of the database. I'm starting to break things out into separate volumes again. Mark ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 'TID index'
> Jim C. Nasby wrote > I just had a thought that could potentially greatly improve vacuum > performance. What about some kind of TID (or does vacuum use CID?) > index? This would allow vacuum to visit only the pages it needs to > visit. Actually, I guess TID/CID wouldn't even be involved; the only > information needed would be if any tuples on a page have been marked > deleted. Something as simple as a bitmap could work. Storing TID info > might provide added vacuum efficiency, but my guess is it's probably not > worth the extra effort. > > This might not help much for tables that just see a lot of random update > activity, but I think it would be very useful for large tables where > pages with dead tuples are likely to be a small percentage of the total > number of pages. > > Maintaining this information on a per-transaction basis might prove > difficult to do without causing concurrency issues. Luckily, I think > this could probably be done in the background without much difficulty. > One possibility is to check for dead tuples as pages are written to disk > (actually, by definition, there would have to be dead tuples at that > point I would think). If memory serves writing these pages is now a > background process, so this shouldn't cause contention issues. There are many good ideas out there, yet it is almost impossible to find somebody else to implement yours! The acid test is to try and write it... Overall, I agree VACUUM could do with some tuning - and 8.0 has just that. It needs very careful thought to make sure both concurrency and recoverability considerations are fully met in any solution you come up with. Best regards, Simon Riggs ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] libpq and prepared statements progress for 8.0
Greg Stark wrote: I was pretty shocked when I heard that JDBC implements the low level protocol itself. It seems like a dead-end strategy to me. Any new protocol innovations need to be implemented in every driver. Every implementation gets the chance to reimplement the same bugs and same inefficiencies over and over again. There *are* benefits to implementing the protocol directly. First on my personal list is that our Java application would not be able to use postgresql at all if the driver required JNI/libpq. There are also some things in the JDBC API that seem hard to map to the current libpq API, e.g. streaming parameter data from a Java stream without taking an intermediate copy. The protocol implementation is not really all that complex. The implementation for both V2 and V3 weighs in at ~6k lines of Java out of ~38k total, and much of that is connection-state juggling that we'd have to do anyway if using libpq (working out when to send BEGIN, breaking up multiple-statement queries into individual statements and matching the results up, managing portal state, etc). -O ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] x86_64 configure problem
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Joe Conway wrote: | Gaetano Mendola wrote: | |> python -c "from distutils import *" > /dev/null 2>&1 || (echo "You |> need distutils installed"; exit 1) |> | | Sorry for the delay -- things got busy around here all of a sudden. | | Attached is a version of the patch with James Pye's distutils checking | code. Gaetano, please verify it works properly for you. Patch applied and it works. I did the negative test and the positive as well. Thanks. Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBSK5K7UpzwH2SGd4RAtUYAJ0dEjiGUgPhU0Qyb9PqRK+5nJct7wCgvMti A/J0qMMGqIVQH3E4BdV0Tu8= =T+77 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] x86_64 configure problem
Gaetano Mendola wrote:
python -c "from distutils import *" > /dev/null 2>&1 || (echo "You need
distutils installed"; exit 1)
Sorry for the delay -- things got busy around here all of a sudden.
Attached is a version of the patch with James Pye's distutils checking
code. Gaetano, please verify it works properly for you.
Although Peter was clearly not in favor of this, Gaetano and James both
are, and I don't feel strongly either way. So barring any other
objections I'll apply this in a day or so.
One procedural issue did occur to me regarding this kind of change. It
requires someone to run autoconf after applying -- how is that normally
handled?
Thanks,
Joe
Index: config/python.m4
===
RCS file: /cvsroot/pgsql-server/config/python.m4,v
retrieving revision 1.7
diff -c -r1.7 python.m4
*** config/python.m4 29 Nov 2003 19:51:17 - 1.7
--- config/python.m4 15 Sep 2004 20:34:47 -
***
*** 21,31
# Determine the name of various directory of a given Python installation.
AC_DEFUN([_PGAC_CHECK_PYTHON_DIRS],
[AC_REQUIRE([PGAC_PATH_PYTHON])
AC_MSG_CHECKING([Python installation directories])
python_version=`${PYTHON} -c "import sys; print sys.version[[:3]]"`
python_prefix=`${PYTHON} -c "import sys; print sys.prefix"`
python_execprefix=`${PYTHON} -c "import sys; print sys.exec_prefix"`
! python_configdir="${python_execprefix}/lib/python${python_version}/config"
python_includespec="-I${python_prefix}/include/python${python_version}"
if test "$python_prefix" != "$python_execprefix"; then
python_includespec="-I${python_execprefix}/include/python${python_version} $python_includespec"
--- 21,39
# Determine the name of various directory of a given Python installation.
AC_DEFUN([_PGAC_CHECK_PYTHON_DIRS],
[AC_REQUIRE([PGAC_PATH_PYTHON])
+ AC_MSG_CHECKING([for Python distutils module])
+ if "${PYTHON}" 2>&- -c 'import distutils'
+ then
+ AC_MSG_RESULT(yes)
+ else
+ AC_MSG_RESULT(no)
+ AC_MSG_ERROR([distutils module not found])
+ fi
AC_MSG_CHECKING([Python installation directories])
python_version=`${PYTHON} -c "import sys; print sys.version[[:3]]"`
python_prefix=`${PYTHON} -c "import sys; print sys.prefix"`
python_execprefix=`${PYTHON} -c "import sys; print sys.exec_prefix"`
! python_configdir=`${PYTHON} -c "from distutils.sysconfig import get_python_lib as f; import os; print os.path.join(f(plat_specific=1,standard_lib=1),'config')"`
python_includespec="-I${python_prefix}/include/python${python_version}"
if test "$python_prefix" != "$python_execprefix"; then
python_includespec="-I${python_execprefix}/include/python${python_version} $python_includespec"
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] some PITR performance data with DBT-2
>Mark Wong wrote > Hi Simon, > > Sorry it has taken so long. Among other things, I doubled the controllers > and drives on the system I was testing this on. But now I have some data > against PostgreSQL-8.0beta2. > Thanks very much. > Here is the test run with archiving enabled: > http://www.osdl.org/projects/dbt2dev/results/dev4-010/158/ > > Here is the test run with archiving disabled: > http://www.osdl.org/projects/dbt2dev/results/dev4-010/159/ > > The overall throughput difference between the two runs with archiving > enabled/disabled was within 1%. > Excellent. I hoped it was that low - my target was < 5%. Stats check out with no wierdness in the results. TGFT. Also, I notice the tpm figures have gone up some more - have you got new hardware, or has the PostgreSQL setup been tuned more? Or can it be that rel8.0 really is that much faster?? > Here is sar/iostat/vmstat and oprofile data during the first hour of > recovery. Total recovery time took about 6.5 hours: > http://www.developer.osdl.org/markw/pitr/ > That's bad news. My own recovery performance estimates would lead me to hope that its possible to get the recovery to be quicker than the processes that wrote the logs, even on a very quick 4 CPU system. I'd be hoping for ~1 hour, or at least <= 4 hours. > I ran the test over a duration of 3 hours (including a 2 hour rampup of > the driver), as opposed to the 6 hours you originally requested. I > hope that is ok. > > System details, which you may be interested in: > > 4 x 1.5 GHz Itanium 2 > 16GB RAM > 6 x Compaq Computer Corporation Smart Array 64xx > 6 x 14 disk 15K RPM drives (split bus) > > The database and archive directory were put onto a single LVM volume > across all 84 drives. > > Let me know if I left anything out. > First off, thank you again. I've had a look at all the results, but I found a few things: - couldnt find postgresql.conf or recovery.conf anywhere, so not sure what OS command you are using - log files were very large indeed due to the SPI error messages, so I haven't been able to download those properly for analysis - any chance you could grep out the SPI stuff, so I can see the archive and restore commands? Stats I'd be interested in for analysing recovery performance would be: - how many log files in total were archived/restored - where were they archived to - what was the archive/recovery command? Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] 'TID index'
I just had a thought that could potentially greatly improve vacuum performance. What about some kind of TID (or does vacuum use CID?) index? This would allow vacuum to visit only the pages it needs to visit. Actually, I guess TID/CID wouldn't even be involved; the only information needed would be if any tuples on a page have been marked deleted. Something as simple as a bitmap could work. Storing TID info might provide added vacuum efficiency, but my guess is it's probably not worth the extra effort. This might not help much for tables that just see a lot of random update activity, but I think it would be very useful for large tables where pages with dead tuples are likely to be a small percentage of the total number of pages. Maintaining this information on a per-transaction basis might prove difficult to do without causing concurrency issues. Luckily, I think this could probably be done in the background without much difficulty. One possibility is to check for dead tuples as pages are written to disk (actually, by definition, there would have to be dead tuples at that point I would think). If memory serves writing these pages is now a background process, so this shouldn't cause contention issues. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] libpq and prepared statements progress for 8.0
> -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: Wednesday, September 15, 2004 11:34 AM > To: Tom Lane > Cc: Dann Corbit; Greg Stark; Merlin Moncure; > [EMAIL PROTECTED] > Subject: Re: [HACKERS] libpq and prepared statements progress for 8.0 > > > > Tom Lane <[EMAIL PROTECTED]> writes: > > > "Dann Corbit" <[EMAIL PROTECTED]> writes: > > > What about using ECPG as an interface for drivers? > > > > What for? It's not a substitute for libpq --- it sits on top of > > libpq, or did last I checked anyway. And it's designed around a > > preprocessor that seems fairly useless for a driver. > > As it happens DBD::Oracle does use Oracle's precompiler. But > it's more of a hindrance than a help. It basically has to > define and implement its own API which is compiled with > Pro*C. Then the rest of the codebase can ignore the > precompiler and use that interface. > > Precompilers are really old school. There's not much point to > using them except in legacy applications that need them. They > offer no advantage over a programmatic API, and lots of > disadvantages. You have to learn a new language, you're one > step further removed from the resulting code, and heaven help > you if your compiler version doesn't match what the > precompiler was tested with. Nevermind actually trying to use > it from another language entirely. The nice thing about using the SQL precompiler was that it was very simple. A total of 15 interfaces needed to be defined: PROCEDURE PREPARE_STMT PROCEDURE DESCRIBE_SELECT PROCEDURE DESCRIBE_PARM PROCEDURE EXECUTE_STMT PROCEDURE EXECUTE_IMMEDIATE PROCEDURE DECLARE_CURSOR PROCEDURE DECLARE_LIST_CURSOR PROCEDURE DECLARE_CURSOR_READ_ONLY PROCEDURE DECLARE_LIST_CURSOR_READ_ONLY PROCEDURE DECLARE_CURSOR_INSERT_ONLY PROCEDURE DECLARE_LIST_CURSOR_INSERT_ONLY PROCEDURE OPEN_CURSOR PROCEDURE FETCH_ROW PROCEDURE CLOSE_CURSOR PROCEDURE RELEASE_STMT And each one was only a few lines (the entire SQLMOD file, including comments is 5695 bytes long). A C++ wrapper was written around these statements, and the C++ wrapper is what is used from the applications that exercise the underlying SQLMOD interface. It was also considerably FASTER than using the C API directly. This is probably due to Rdb being ANCIENT technology, and quite possibly the C API is written over top of SQLMOD rather than the other way around. At any rate, it is very simple to write a generic interface using a precompiler. However, if the PostgreSQL precompiler is not reentrant, it is a waste of time. My thought was that ECPG might deliver the missing functionality needed by the Perl interface. Since ECPG sits on top of libpq, and libpq seems to lack some needed feature, it seems highly unlikely that it can provide what is missing. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] libpq and prepared statements progress for 8.0
Tom Lane <[EMAIL PROTECTED]> writes: > Greg Stark <[EMAIL PROTECTED]> writes: > > Is there anything technically hard in adding this functionality to libpq? It > > looks like it's just mechanically adding more entry points to existing code. > > Well, (a) I ran out of time, and (b) I wasn't sure what the most > convenient API would be. Should we create something that packages > together a Parse and a Describe Statement, or expose those as > separate things? I don't know either. For my purposes it seems like a waste to be handling this if I don't need it. On the other hand another round trip when it is needed would be poor. Does the protocol provide it immediately or does it require another message to the server? > > The describe statement part could be much trickier but DBD::Pg doesn't really > > need that for basic functionality. > > Doesn't it? I thought the problem was that they couldn't find out what > datatypes the parameters got resolved as. That may not be important > if they are happy with always shipping text strings, but if they want to > move over to using binary transmission of parameter values then they > gotta know the parameter types. Well personally I'm happy always shipping text strings. I'm sure someone else will have different opinions but I don't really see why it would be any faster to marshal data into Postgres's preferred binary representation than it would to marshal it into a string. Neither are going to match Perl's internal representation anyways. I'm assuming the data type is always known to the programmer anyways and he can ensure the data is provided in the appropriate Perl representation the driver expects. It could be useful for fancier situations like marshalling a timestamp from an ambiguous perl datastructure that could represent an integer or integer array into a Postgres string or binary representation of a timestamp. Or perhaps for things like GUI tools that will display a user dialog box for prompting for parameters of the appropriate type. But these seem like refinements. The basic functionality is to offer the equivalent functionality to what exists already, where the provided parameters are simply interpolated into the query string. > > I do wonder whether DBD::Pg is really best off using libpq. > > I was wondering that myself. Would they be able to implement a > pure-Perl driver if they ginned up their own protocol code? > It'd be a lot of work, of course, so I can understand that they > might not feel it's worth the trouble. Well a pure-Perl driver or a driver written in C with perl bindings wouldn't really be an unreasonable amount of work I don't think. What I'm worried about is whether it's the right strategy. I was pretty shocked when I heard that JDBC implements the low level protocol itself. It seems like a dead-end strategy to me. Any new protocol innovations need to be implemented in every driver. Every implementation gets the chance to reimplement the same bugs and same inefficiencies over and over again. I had thought it was a better idea to have a library that handled the low level protocol details. It should provide a 1-1 mapping for everything you can do with the protocol. But at least that way you're guaranteed to never be sending garbage down the wire getting the state machine out of sync with the server. > > From what I'm reading now it seems the "read every record before > > returning" behaviour is rooted in the libpq interface. > > Right. Again that's probably something that could be handled by > exposing more/different API, but no one has stepped up to design it. It sort of seems to me that libpq's problem is trying to do too much. It tries to be an abstract easy-to-use API for C programmers. But there's a need for a low level API that just handles the protocol syntax and state machine and not much more. -- greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] libpq and prepared statements progress for 8.0
Tom Lane <[EMAIL PROTECTED]> writes: > "Dann Corbit" <[EMAIL PROTECTED]> writes: > > What about using ECPG as an interface for drivers? > > What for? It's not a substitute for libpq --- it sits on top of libpq, > or did last I checked anyway. And it's designed around a preprocessor > that seems fairly useless for a driver. As it happens DBD::Oracle does use Oracle's precompiler. But it's more of a hindrance than a help. It basically has to define and implement its own API which is compiled with Pro*C. Then the rest of the codebase can ignore the precompiler and use that interface. Precompilers are really old school. There's not much point to using them except in legacy applications that need them. They offer no advantage over a programmatic API, and lots of disadvantages. You have to learn a new language, you're one step further removed from the resulting code, and heaven help you if your compiler version doesn't match what the precompiler was tested with. Nevermind actually trying to use it from another language entirely. -- greg ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] libpq and prepared statements progress for 8.0
"Dann Corbit" <[EMAIL PROTECTED]> writes: > What about using ECPG as an interface for drivers? What for? It's not a substitute for libpq --- it sits on top of libpq, or did last I checked anyway. And it's designed around a preprocessor that seems fairly useless for a driver. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] libpq and prepared statements progress for 8.0
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane > Sent: Wednesday, September 15, 2004 10:51 AM > To: Greg Stark > Cc: Merlin Moncure; [EMAIL PROTECTED] > Subject: Re: [HACKERS] libpq and prepared statements progress for 8.0 > > > Greg Stark <[EMAIL PROTECTED]> writes: > > Is there anything technically hard in adding this functionality to > > libpq? It looks like it's just mechanically adding more > entry points > > to existing code. > > Well, (a) I ran out of time, and (b) I wasn't sure what the > most convenient API would be. Should we create something > that packages together a Parse and a Describe Statement, or > expose those as separate things? > > There's surely no technical difficulty once you've got some > consensus on what the API should look like. > > > The describe statement part could be much trickier but > DBD::Pg doesn't > > really need that for basic functionality. > > Doesn't it? I thought the problem was that they couldn't > find out what datatypes the parameters got resolved as. That > may not be important if they are happy with always shipping > text strings, but if they want to move over to using binary > transmission of parameter values then they gotta know the > parameter types. > > > I do wonder whether DBD::Pg is really best off using libpq. > > I was wondering that myself. Would they be able to implement > a pure-Perl driver if they ginned up their own protocol code? > It'd be a lot of work, of course, so I can understand that > they might not feel it's worth the trouble. > > > From what I'm reading now it seems the "read every record before > > returning" behaviour is rooted in the libpq interface. > > Right. Again that's probably something that could be handled > by exposing more/different API, but no one has stepped up to > design it. What about using ECPG as an interface for drivers? I wrote an ODBC driver for Rdb on OpenVMS using SQLMOD (which is [essentially] ECPG for Rdb) so that is a technique that should be able to provide the needed functionality. According to my understanding ECPG is now reentrant. Is that correct? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] libpq and prepared statements progress for 8.0
Greg Stark <[EMAIL PROTECTED]> writes: > Is there anything technically hard in adding this functionality to libpq? It > looks like it's just mechanically adding more entry points to existing code. Well, (a) I ran out of time, and (b) I wasn't sure what the most convenient API would be. Should we create something that packages together a Parse and a Describe Statement, or expose those as separate things? There's surely no technical difficulty once you've got some consensus on what the API should look like. > The describe statement part could be much trickier but DBD::Pg doesn't really > need that for basic functionality. Doesn't it? I thought the problem was that they couldn't find out what datatypes the parameters got resolved as. That may not be important if they are happy with always shipping text strings, but if they want to move over to using binary transmission of parameter values then they gotta know the parameter types. > I do wonder whether DBD::Pg is really best off using libpq. I was wondering that myself. Would they be able to implement a pure-Perl driver if they ginned up their own protocol code? It'd be a lot of work, of course, so I can understand that they might not feel it's worth the trouble. > From what I'm reading now it seems the "read every record before > returning" behaviour is rooted in the libpq interface. Right. Again that's probably something that could be handled by exposing more/different API, but no one has stepped up to design it. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] libpq and prepared statements progress for 8.0
Tom Lane <[EMAIL PROTECTED]> writes: > "Merlin Moncure" <[EMAIL PROTECTED]> writes: > > Question: what is the relevance of the binary protocol, are you trying > > to send/fetch binary data via the command interface? > > My understanding of the original post is that DBD::Pg is sitting atop > libpq and wants to keep doing so. So they're going to need some > improvements to libpq to get at Parse-into-a-named-statement and > Describe Statement. This is one of the things that didn't get done in > the 7.4 cycle, and no one seems to have got round to it later either. > But it's clearly a deficiency of libpq. Well even without parse-into-a-named-statement they could be using PQexecParam for now. I'm talking with them trying to straighten this out. Is there anything technically hard in adding this functionality to libpq? It looks like it's just mechanically adding more entry points to existing code. Were you leaving this as a honey pot hoping it would attract new programmers? I'm looking at doing it now. The describe statement part could be much trickier but DBD::Pg doesn't really need that for basic functionality. It would be a useful feature for later though. I do wonder whether DBD::Pg is really best off using libpq. From what I'm reading now it seems the "read every record before returning" behaviour is rooted in the libpq interface. Ideally a program should be able to stream results and process them as they arrive. It looks like PQgetResult might be relevant but the documentation isn't clear whether each result returned is for an entire query in the original statement or if they can be partial result sets. -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] xact.c state machine redesign
After looking over the state machine in xact.c, I'm thinking of removing the TBLOCK_SUBENDABORT_ALL and TBLOCK_SUBENDABORT_RELEASE states in favor of having the ROLLBACK command mark the whole transaction state stack similarly to what is now done for COMMIT. In detail this would require adding a TBLOCK_ABORT_PENDING state to use at the top level, and ROLLBACK would act thus: * For each subtransaction level: if it's in SUBABORT state (ie, already aborted) then shift it to SUBENDABORT state (giving permission to pop it from the stack); otherwise mark it SUBABORT_PENDING. * At the outer level: if it's in ABORT state then shift to ENDABORT, otherwise mark it ABORT_PENDING. In CommitTransactionCommand we would have the behaviors: ABORT: SUBABORT: do nothing (same as now) SUBENDABORT: cleanup & pop recursively examine parent SUBABORT_PENDING: abort subtransaction cleanup & pop recursively examine parent ENDABORT: cleanup go to DEFAULT state ABORT_PENDING: abort transaction cleanup go to DEFAULT state I'm also toying with handling ROLLBACK TO by marking all the levels above the target as SUBENDABORT or SUBABORT_PENDING, and then marking the target level with one of two new states, TBLOCK_SUBRESTART or TBLOCK_SUBABORT_RESTART (the latter if it was already SUBABORT). These would have the behaviors TBLOCK_SUBRESTART: abort subtransaction cleanup & pop start new subtransaction with same name TBLOCK_SUBABORT_RESTART: cleanup & pop start new subtransaction with same name This isn't any fewer states than we have now, but the states seem much more clearly organized to me --- in particular, other than the RESTART states there's full symmetry between outer-level and subtransaction states. Also, this ensures that the planned state transitions are fully marked out on the state stack before we start to do anything, which I think is going to be more robust. AbortOutOfAnyTransaction is a bit of a kluge and I don't really want to depend on it to implement ROLLBACK. Comments? regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Problems with SPI memory management
"Katsaros Kwn/nos" <[EMAIL PROTECTED]> writes: > What I'm trying to do is to get the Query related to a select statement, > alter it and produce a new SPI_plan that will execute. To do so, I > retrieve the query from the _SPI_plan->qtlist, alter it (seems OK in > nodeToString) and then use some SPI functions copied in my code to > produce the new _SPI_plan. In more details, I begin a new my_SPI > session, call my_SPI_prepare and pass the query. The code is almost the > same with that in spi.c apart from that I do not call pg_parse and > pg_analyze_and_rewrite.Then I begin a new SPI session and pass the new > plan. What do you mean by "begin a new SPI session" --- do you SPI_finish() and SPI_connect()? (If so, why bother?) I think that SPI_finish() would release the memory in which the previous plan existed. If you're not copying the entire parse tree this would lead to trouble. You'd be well advised to be doing this sort of hackery in a build with --enable-cassert. That turns on CLOBBER_FREED_MEMORY which makes misuse of freed memory a whole lot more obvious. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] banner vs version
On Wed, 2004-09-15 at 09:15, G u i d o B a r o s i o wrote: > [EMAIL PROTECTED] local]$ psql template1 > Welcome to psql 8.0.0beta2, the PostgreSQL interactive terminal. > template1=# select version(); >version > - > PostgreSQL 7.4.2 on i686-pc-linux-gnu, compiled by GCC 2.96 > (1 row) > > Is this ok? Banner version, 8 beta2, version() returns 7.4.2. I think this explains the problem that you e-mailed in earlier. You probably tried to install a 8.0beta2 plpgsql into a 7.4.2 backend. AFAIK, this is fine, 8.0 psql clients should be able to connect to 7.4 backends (; -- Regards, James William Pye signature.asc Description: This is a digitally signed message part
Re: [HACKERS] PL/PgSQL "bare" function calls
Andrew Dunstan wrote: ISTM that this is being done at the wrong level anyway. I'd like to see a facility available in our SQL, e.g. CALL foo(); with the restriction that foo() should be declared to return void. Of course, that doesn't remove the keyword requirement as Neil wanted, but doing that would probably require a lot more work - we'd have to make procedures a whole lot closer to first-class objects. I agree with this, except that foo() should be a PROCEDURE, not a FUNCTION. Joe ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] PG_exception_stack
G u i d o B a r o s i o <[EMAIL PROTECTED]> writes: > [EMAIL PROTECTED] postgres]$ createlang plpgsql tech_mis > createlang: language installation failed: ERROR: could not load library > "/usr/local/pgsql/lib/plpgsql.so": /usr/local/pgsql/lib/plpgsql.so: undefined > symbol: PG_exception_stack I think you've got a version mismatch problem --- specifically, it looks like you are trying to load an 8.0 plpgsql.so into a pre-8.0 backend. PG_exception_stack is (just by chance no doubt) the first symbol the newer code tries to reference that's not in the older backend. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Problems with SPI memory management
Hi! I posted the following message to the general list but no answer.Could you please help? I have some problems with the SPI memory management (at least I think this is the problem). What I'm trying to do is to get the Query related to a select statement, alter it and produce a new SPI_plan that will execute. To do so, I retrieve the query from the _SPI_plan->qtlist, alter it (seems OK in nodeToString) and then use some SPI functions copied in my code to produce the new _SPI_plan. In more details, I begin a new my_SPI session, call my_SPI_prepare and pass the query. The code is almost the same with that in spi.c apart from that I do not call pg_parse and pg_analyze_and_rewrite.Then I begin a new SPI session and pass the new plan. Now, the problem: Everything works fine only when I execute my function just after I have initiated a new psql session. When I try to call it again, the Query structure inside the new plan seems empty (all fields shown by nodeToString seem to have no values). Even if I reload my shared library (load command),the same problem appears. I allocate memory for the Query object with palloc() outside a SPI or my_SPI session. Any hints? Thanks in advance! Ntinos Katsaros PS: I'm not very good at C! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PG_exception_stack
On Wed, 2004-09-15 at 09:04, G u i d o B a r o s i o wrote: > [EMAIL PROTECTED] postgres]$ createlang plpgsql tech_mis > createlang: language installation failed: ERROR: could not load > library "/usr/local/pgsql/lib/plpgsql.so": > /usr/local/pgsql/lib/plpgsql.so: undefined symbol: PG_exception_stack It looks like your build is a bit fubar'd. I don't know why exactly, but it can't find that symbol, so I'd suggest a gmake distclean, configure, and gmake all install. (hrm, might also want to make sure that your installations arent crossing paths somewhere here, if you have multiple versions installed) (Note that I just did it with my dev build without trouble) -- Regards, James William Pye signature.asc Description: This is a digitally signed message part
[HACKERS] some PITR performance data with DBT-2
Hi Simon, Sorry it has taken so long. Among other things, I doubled the controllers and drives on the system I was testing this on. But now I have some data against PostgreSQL-8.0beta2. Here is the test run with archiving enabled: http://www.osdl.org/projects/dbt2dev/results/dev4-010/158/ Here is the test run with archiving disabled: http://www.osdl.org/projects/dbt2dev/results/dev4-010/159/ Here is sar/iostat/vmstat and oprofile data during the first hour of recovery. Total recovery time took about 6.5 hours: http://www.developer.osdl.org/markw/pitr/ The overall throughput difference between the two runs with archiving enabled/disabled was within 1%. I ran the test over a duration of 3 hours (including a 2 hour rampup of the driver), as opposed to the 6 hours you originally requested. I hope that is ok. System details, which you may be interested in: 4 x 1.5 GHz Itanium 2 16GB RAM 6 x Compaq Computer Corporation Smart Array 64xx 6 x 14 disk 15K RPM drives (split bus) The database and archive directory were put onto a single LVM volume across all 84 drives. Let me know if I left anything out. -- Mark Wong - - [EMAIL PROTECTED] Open Source Development Lab Inc - A non-profit corporation 12725 SW Millikan Way - Suite 400 - Beaverton, OR 97005 (503) 626-2455 x 32 (office) (503) 626-2436 (fax) http://developer.osdl.org/markw/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] WIN1250 as server encoding
Tom Lane wrote: > AFAIR, the only place where these numbers are stored is in > pg_database.datencoding, so only the server-encoding values are > frozen in any meaningful sense. You could rearrange the numbers > currently assigned to client encodings to preserve the range > property. Interesting. I guess I was too traumatized by the last numbering change to ignore that possibility. Does anyone else see a problem with that? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] banner vs version
G u i d o B a r o s i o wrote: > Is this ok? Banner version, 8 beta2, version() returns 7.4.2. The banner shows the psql (client) version, version() shows the server version. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] banner vs version
[EMAIL PROTECTED] local]$ psql template1 Welcome to psql 8.0.0beta2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit template1=# select version(); version - PostgreSQL 7.4.2 on i686-pc-linux-gnu, compiled by GCC 2.96 (1 row) template1=# Is this ok? Banner version, 8 beta2, version() returns 7.4.2. Regards ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] PG_exception_stack
Didn't find a solution for this on the lists, and I am not a yet-brand-new-guru, so... I ask :) What about this? I've found a box (dual Intel(R) Xeon(TM) CPU 2.80GHz) in which to test the beta2. Trying to cretae proc languages I found this error. I've seen error creating languages on other boxes, but none of them like this one. What makes me a litle bit lazy is the PG_except... msg. I am trying to do something nasty or just watching something not usual? I've found recent posts related to the BKI and talking about the PG_exception_stack, but none of them seem to handle the createlang situation. Any hints? [EMAIL PROTECTED] postgres]$ createlang plpgsql tech_mis createlang: language installation failed: ERROR: could not load library "/usr/local/pgsql/lib/plpgsql.so": /usr/local/pgsql/lib/plpgsql.so: undefined symbol: PG_exception_stack [EMAIL PROTECTED] postgres]$ pg_config --configure '--enable-thread-safety' '--with-perl' '--without-docdir' [EMAIL PROTECTED] postgres]$ pg_config --version PostgreSQL 8.0.0beta2 [EMAIL PROTECTED] postgres]$ cd /usr/local/pgsql/lib/ [EMAIL PROTECTED] lib]$ ls -ltrh plpgsql.so -rwxr-xr-x1 root root 123k Sep 15 15:41 plpgsql.so [EMAIL PROTECTED] lib]$uname -a Linux prdpwgb0208 2.4.22ow1np-ht #8 SMP Mon Oct 13 19:54:55 GMT 2003 i686 unknown Regards, Guido ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] WIN1250 as server encoding
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Some people have requested to add WIN1250 as an allowed server encoding. > So far, the order of the encoding numbers determined which ones were > client-only, so in order not to renumber the encodings, I could only > come up with the attached ugly solution. If no one thinks of a better > one, we'll go with that. Can't we just renumber them? AFAIR, the only place where these numbers are stored is in pg_database.datencoding, so only the server-encoding values are frozen in any meaningful sense. You could rearrange the numbers currently assigned to client encodings to preserve the range property. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] PL/PgSQL "bare" function calls
Tom Lane wrote: Neil Conway <[EMAIL PROTECTED]> writes: (3) The parser must distinguish between two cases when it sees an unknown word (T_WORD) beginning a statement. The word could be the beginning of a SQL statement (stmt_execsql in the grammar), such as: UPDATE ...; or the name of a function in a function call: invoke_func(...); The patch currently distinguishes between these cases by looking at the next token -- if it is a left parenthesis, the patch assumes it is a function call, otherwise it assumes it is a SQL statement. Is this the best approach? That seems fairly unworkable. For example SELECT (2,3,4); is valid SQL. Also I'm not sure if you can extend this to cope with schema-qualified function names. ISTM that this is being done at the wrong level anyway. I'd like to see a facility available in our SQL, e.g. CALL foo(); with the restriction that foo() should be declared to return void. Of course, that doesn't remove the keyword requirement as Neil wanted, but doing that would probably require a lot more work - we'd have to make procedures a whole lot closer to first-class objects. cheers andrew ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] WIN1250 as server encoding
On Wed, Sep 15, 2004 at 05:02:44PM +0200, Peter Eisentraut wrote: > Some people have requested to add WIN1250 as an allowed server encoding. > So far, the order of the encoding numbers determined which ones were > client-only, so in order not to renumber the encodings, I could only > come up with the attached ugly solution. If no one thinks of a better > one, we'll go with that. Probably a silly suggestion, but... A second encoding number which happens to assign the same character codes as WIN1250, with a 1-to-1 translation table, and renaming the old number to make room for the new one in newly compiled code? Jeroen ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] PL/PgSQL "bare" function calls
Neil Conway <[EMAIL PROTECTED]> writes: > (3) The parser must distinguish between two cases when it sees an > unknown word (T_WORD) beginning a statement. The word could be the > beginning of a SQL statement (stmt_execsql in the grammar), such as: > UPDATE ...; > or the name of a function in a function call: > invoke_func(...); > The patch currently distinguishes between these cases by looking at the > next token -- if it is a left parenthesis, the patch assumes it is a > function call, otherwise it assumes it is a SQL statement. Is this the > best approach? That seems fairly unworkable. For example SELECT (2,3,4); is valid SQL. Also I'm not sure if you can extend this to cope with schema-qualified function names. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] WIN1250 as server encoding
Some people have requested to add WIN1250 as an allowed server encoding. So far, the order of the encoding numbers determined which ones were client-only, so in order not to renumber the encodings, I could only come up with the attached ugly solution. If no one thinks of a better one, we'll go with that. It would also be good if someone who has an environment that calls for WIN1250 (that is, Windows and an appropriate language environment) could test whether this actually does anything besides compiling without errors. :) -- Peter Eisentraut http://developer.postgresql.org/~petere/ diff -cr ../cvs-pgsql/src/include/mb/pg_wchar.h ./src/include/mb/pg_wchar.h *** ../cvs-pgsql/src/include/mb/pg_wchar.h 2004-09-11 00:10:58.0 +0200 --- ./src/include/mb/pg_wchar.h 2004-09-15 16:51:40.0 +0200 *** *** 143,149 * in the pg_enc2name[] (mb/encnames.c) array! * * If you add some encoding don'y forget check ! * PG_ENCODING_[BE|FE]_LAST macros. * * The PG_SQL_ASCII is default encoding and must be = 0. */ --- 143,149 * in the pg_enc2name[] (mb/encnames.c) array! * * If you add some encoding don'y forget check ! * PG_VALID_[BE|FE]_ENCODING macros. * * The PG_SQL_ASCII is default encoding and must be = 0. */ *** *** 177,210 PG_ISO_8859_6,/* ISO-8859-6 */ PG_ISO_8859_7,/* ISO-8859-7 */ PG_ISO_8859_8,/* ISO-8859-8 */ ! ! /* followings are for client encoding only */ ! PG_SJIS, /* Shift JIS (Winindows-932) */ ! PG_BIG5, /* Big5 (Windows-950) */ ! PG_GBK, /* GBK (Windows-936) */ ! PG_UHC, /* UHC (Windows-949) */ PG_WIN1250, /* windows-1250 */ ! PG_GB18030, /* GB18030 */ _PG_LAST_ENCODING_ /* mark only */ } pg_enc; - #define PG_ENCODING_BE_LAST PG_ISO_8859_8 - #define PG_ENCODING_FE_LAST PG_GB18030 /* * Please use these tests before access to pg_encconv_tbl[] * or to other places... */ - #define PG_VALID_BE_ENCODING(_enc) \ - ((_enc) >= 0 && (_enc) <= PG_ENCODING_BE_LAST) - - #define PG_ENCODING_IS_CLIEN_ONLY(_enc) \ - (((_enc) > PG_ENCODING_BE_LAST && (_enc) <= PG_ENCODING_FE_LAST) - #define PG_VALID_ENCODING(_enc) \ ((_enc) >= 0 && (_enc) < _PG_LAST_ENCODING_) /* On FE are possible all encodings */ #define PG_VALID_FE_ENCODING(_enc) PG_VALID_ENCODING(_enc) --- 177,211 PG_ISO_8859_6,/* ISO-8859-6 */ PG_ISO_8859_7,/* ISO-8859-7 */ PG_ISO_8859_8,/* ISO-8859-8 */ ! PG_SJIS, /* Shift JIS (Winindows-932), client only */ ! PG_BIG5, /* Big5 (Windows-950), client only */ ! PG_GBK, /* GBK (Windows-936), client only */ ! PG_UHC, /* UHC (Windows-949), client only */ PG_WIN1250, /* windows-1250 */ ! PG_GB18030, /* GB18030, client only */ _PG_LAST_ENCODING_ /* mark only */ } pg_enc; /* * Please use these tests before access to pg_encconv_tbl[] * or to other places... */ #define PG_VALID_ENCODING(_enc) \ ((_enc) >= 0 && (_enc) < _PG_LAST_ENCODING_) + #define PG_ENCODING_IS_CLIENT_ONLY(_enc) \ + (PG_VALID_ENCODING(_enc) \ + && (((_enc) == PG_SJIS) \ + || ((_enc) == PG_BIG5) \ + || ((_enc) == PG_GBK) \ + || ((_enc) == PG_UHC) \ + || ((_enc) == PG_GB18030))) + + #define PG_VALID_BE_ENCODING(_enc) \ + (PG_VALID_ENCODING(_enc) && !PG_ENCODING_IS_CLIENT_ONLY(_enc)) + /* On FE are possible all encodings */ #define PG_VALID_FE_ENCODING(_enc) PG_VALID_ENCODING(_enc) Nur in ./src: Makefile.custom. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] libpq and prepared statements progress for 8.0
"Merlin Moncure" <[EMAIL PROTECTED]> writes: > Question: what is the relevance of the binary protocol, are you trying > to send/fetch binary data via the command interface? My understanding of the original post is that DBD::Pg is sitting atop libpq and wants to keep doing so. So they're going to need some improvements to libpq to get at Parse-into-a-named-statement and Describe Statement. This is one of the things that didn't get done in the 7.4 cycle, and no one seems to have got round to it later either. But it's clearly a deficiency of libpq. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Statement parsing problem ?
On Sep 15, 2004, at 9:43 AM, Chris Dunlop wrote: Either that, or I'm missing something... From the SELECT docs ... A JOIN clause combines two FROM items. Use parentheses if necessary to determine the order of nesting. In the absence of parentheses, JOINs nest left-to-right. In any case JOIN binds more tightly than the commas separating FROM items. CROSS JOIN and INNER JOIN produce a simple Cartesian product, the same result as you get from listing the two items at the top level of FROM, but restricted by the join condition (if any). CROSS JOIN is equivalent to INNER JOIN ON (TRUE), that is, no rows are removed by qualification. These join types are just a notational convenience, since they do nothing you couldn't do with plain FROM and WHERE. --- Since you're doing a simple join, you'd be better off using form select 1 as "OK" from t1, t2, t3, t4 on where t4.foo6 = t3.foo5 and t2.foo3 = t1.foo1 and t3.foo4 = t1.foo2 ; and then you can vary the order of the and clauses any way you like. But using the "FROM t1, t2, t3 JOIN t4" form binds left-to-right tigher than the comma separated list, so it is operating on exactly two tables (t3 and t4), not the t1, t2, t3 cartesian product joined with t4. James Robinson Socialserve.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Statement parsing problem ?
G'day, There seems to be a kind of statement parsing problem in 7.4.5 (from debian postgresql-7.4.5-3, i386). Either that, or I'm missing something... The following script: -- create table t1 ( foo1 integer, foo2 integer ); create table t2 ( foo3 integer ); create table t3 ( foo4 integer, foo5 integer ); create table t4 ( foo6 integer ); \echo \echo --- \echo this works \echo --- select 1 as "OK" from t1, t2, t3 join t4 on (t4.foo6 = t3.foo5) where t2.foo3 = t1.foo1 and t3.foo4 = t1.foo2 ; \echo \echo \echo Error, from simply swapping the order of t2 and t3 ??? \echo select 1 from t1, t3, t2 join t4 on (t4.foo6 = t3.foo5) where t2.foo3 = t1.foo1 and t3.foo4 = t1.foo2 ; \echo \echo \echo slightly different error, using a table alias \echo select 1 from t1, t3 a, t2 join t4 on (t4.foo6 = a.foo5) where t2.foo3 = t1.foo1 and a.foo4 = t1.foo2 ; -- produces the output: -- --- this works --- OK (0 rows) Error, from simply swapping the order of t2 and t3 ??? psql:/tmp/test.sql:32: NOTICE: adding missing FROM-clause entry for table "t3" psql:/tmp/test.sql:32: ERROR: JOIN/ON clause refers to "t3", which is not part of JOIN slightly different error, using a table alias psql:/tmp/test.sql:46: ERROR: relation "a" does not exist -- So is it me, or is this just a bit borken ? Cheers, Chris. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] SELECT FOR UPDATE NOWAIT and PostgreSQL 8.0
Devrim GUNDUZ wrote: > > Now we have LOCK TABLE ... NOWAIT; but I wonder whether we'll have the > > SELECT ... NOWAIT one. Today I got a request for this; and it was > > reported that this feature will be used in a huge project. > > > > Well, it shouldn't be too much of a patch - just cloning the code? > > > > Perhaps they can start in development without it and we'll patch it in > > later. > > I learned that the code is ready. They'll change the code now. > > >> Hmm... this seems the exact opposite of how I would tend to think > >> the feature > >> would be used... ie. you don't really care how long the query takes, just > >> that you can't get the lock. > > > > Agreed - and this is important! I thought we'd done NOWAIT on the SELECT... > > > > Oh well, 8.1 will be better still. > > Bruce: Any TODO here? ;) OK, but the NOWAIT has to be done for SELECT FOR UPDATE, UPDATE, and DELETE. Anyone want to suggest an API for that? Anddo you realize there are lots of locks for those commands, like locks on pg_class and stuff. Would it be only for exclusive locks? As you can see there are some unanswered questions. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] libpq and prepared statements progress for 8.0
> A bit of context here. The perl DBD::Pg developers are trying to
figure
> out
> how to implement prepared queries sanely. As it stands now they're
> basically
> writing off both binary prepared queries and SQL based prepared
queries as
> basically useless. It would be a shame to have a brand new binary
protocol
> but
> find it ignored by driver writers.
>
> The problem is that you want to be able to do
>
> $sth = $dbh->prepare("SELECT col_a FROM tab WHERE col_b = ?");
> $sth->execute(1);
> ...
>
> And not have to jump through hoops binding parameters to types and so
on.
>
suggestion: default to text type ('character varying') and overload your
prepare method to allow a vector of types for special cases. It follows
that if you don't know what type you are dealing with than it gets dealt
with as a string.
Question: what is the relevance of the binary protocol, are you trying
to send/fetch binary data via the command interface?
Merlin
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
