[GENERAL] How do you rebind/reexecute a query without reparsing it?
I come from an Oracle background and am used to being able to parse a query once and then rebind and reexecute that same query multiple times. This can have huge performance benefits for frequently used queries within an application. I am new to PostgreSQL, and am trying to figure out how to do the same here. Specifically from the JDBC client interface. The JDBC interface allows for rebinding and reexecuting, but the implementation seems to rebuild the query every time and sends the new query string to the server to have it reparse. In digging a little deeper, I don't think that the Front End/Back End protocol can support this type of functionality. Is there anyway I can avoid the overhead of reparsing the statements when all I want to do is rebind/reexecute the same statements over and over again? (Again I need this from the JDBC interface) thanks, --Barry
Re: [PORTS] Logging (was Re: [GENERAL] PostgreSQL 7.0-2 RPMset released.)
Tom Lane wrote:
>
> Lamar Owen <[EMAIL PROTECTED]> writes:
> > The real problem with redirecting the postmaster output is the issue
> > of log rolling, which is impossible to do in the 'classic'
> > stderr/stdout redirect UNLESS you throw down postmaster when rolling
> > the log (unless you know a trick I don't).
I think I do ;-) read on...
> Yes. I think ultimately we will have to do some logging support code of
> our own to make this work the way we want. My thought at the moment is
> there's nothing wrong with logging to stderr, as long as there's some
> code somewhere that periodically closes stderr and reopens it to a new
> log file. There needn't be a lot of code involved, we just need a
> well-thought-out spec for how it should work. Comments anyone?
>
> regards, tom lane
I really enjoy using apache's rotatelogs program. stderr is
redirected through a pipe to a very small and robust C program,
rotatelogs, that takes as arguments number of seconds between
log rotates and the log filename. Logs are rotated every
argv[2] seconds. The rotatelogs program takes care of closing
and reopening, and nothing has to done from the application,
just start postmaster with '2>&1 | rotatelogs ...' at the end,
and log to stderr.
Also, BSD license! :)
For reference, I enclose the program as an attachment; it's
less than 100 lines. Also, here's the man page:
Name
rotatelogs - rotate Apache logs without having to kill
the
server
Synopsis
rotatelogs logfile rotationtime
Description
rotatelogs is a simple program for use in conjunction
with
Apache's piped logfile feature which can be used
like
this:
TransferLog"|rotatelogs
/path/to/logs/access_log
86400"
This creates the files /path/to/logs/access_log.
where
is the system time at which the log nominally
starts
(this time will always be a multiple of the rotation
time,
so you can synchronize cron scripts with it). At the
end
of each rotation time (here after 24 hours) a new log
is
started.
Options
logfile
The path plus basename of the logfile. The
suffix
. is automatically added.
rotationtime
The rotation time in seconds.
See Also
httpd(8)
/*
* Simple program to rotate Apache logs without having to kill the server.
*
* Contributed by Ben Laurie <[EMAIL PROTECTED]>
*
* 12 Mar 1996
*/
#define BUFSIZE 65536
#define MAX_PATH1024
#include "ap_config.h"
#include
#include
#include
int main (int argc, char **argv)
{
char buf[BUFSIZE], buf2[MAX_PATH];
time_t tLogEnd = 0;
time_t tRotation;
int nLogFD = -1;
int nRead;
char *szLogRoot;
if (argc != 3) {
fprintf(stderr,
"%s \n\n",
argv[0]);
#ifdef OS2
fprintf(stderr,
"Add this:\n\nTransferLog \"|%s.exe /some/where 86400\"\n\n",
argv[0]);
#else
fprintf(stderr,
"Add this:\n\nTransferLog \"|%s /some/where 86400\"\n\n",
argv[0]);
#endif
fprintf(stderr,
"to httpd.conf. The generated name will be /some/where. "
"where is the\nsystem time at which the log nominally "
"starts (N.B. this time will always be a\nmultiple of the "
"rotation time, so you can synchronize cron scripts with it).\n"
"At the end of each rotation time a new log is started.\n");
exit(1);
}
szLogRoot = argv[1];
tRotation = atoi(argv[2]);
if (tRotation <= 0) {
fprintf(stderr, "Rotation time must be > 0\n");
exit(6);
}
for (;;) {
nRead = read(0, buf, sizeof buf);
if (nRead == 0)
exit(3);
if (nRead < 0)
if (errno != EINTR)
exit(4);
if (nLogFD >= 0 && (time(NULL) >= tLogEnd || nRead < 0)) {
close(nLogFD);
nLogFD = -1;
}
if (nLogFD < 0) {
time_t tLogStart = (time(NULL) / tRotation) * tRotation;
sprintf(buf2, "%s.%010d", szLogRoot, (int) tLogStart);
tLogEnd = tLogStart + tRotation;
nLogFD = open(buf2, O_WRONLY | O_CREAT | O_APPEND, 0666);
if (nLogFD < 0) {
perror(buf2);
exit(2);
}
}
if (write(nLogFD, buf, nRead) != nRead) {
perror(buf2);
exit(5);
}
}
}
[GENERAL] performance and number of selected columns
Hi, say I have three tables a,b,c with lots of columns (say 10 for each table). Using psql command line, why does SELECT a.*,b.*,c.* FROM a,b,c takes much more longer (in my specifc case 3 times) than only selecting one column like SELECT a.oid, b.oid, c.oid FROM a,b,c. This is on 7.0. Can I play with buffer sizes? Indexes exist on most of the columns. Columns in the second line can be arbitrary. Dirk
RE: [GENERAL] pgsql for win
> > My friend Ingo (cc'd here) is hard at work on this right now. > > Maybe a collaboration? I'd really like to see this happen too. > > > > Cheers, > > Tom > > Tom, are you referring to a Cygwin binary, or a native NT port? I don't think it is possible to create a WinNT port without Cygwin. Now I have 7.0beta3 compiled with Cygwin B20 - it runs on the newest Cygwin too. The 7.0 final and the newest Cygwin compiles well (with small updates to the sources) but I cannot get it run. It has problems with allocating shared memory segments. It seems like "user permissions" problem. Dan
Re: [GENERAL] Re: [HACKERS] Postgresql OO Patch
Mike Mascari wrote: > At a minimum, it seems to me, the backend must support the > concept of multiple tuples with different attributes at the > relation level since concurrency and rollback-ability of ALTER > TABLE ADD COLUMN will cause two concurrent transactions to see a > single relation with different attributes. It doesn't seem a > large leap to support this concept for OO purposes from "leaf" to > "base". For "base" to "leaf" type queries, wouldn't it be > acceptable to return the base attributes only, as long as the > equivalent of run-time type information could be had from the > OID? How are you going to be able to go shape.display() and have it work for a triangle, if the triangle's apex's weren't retrieved?
Re: [GENERAL] Re: [HACKERS] Postgresql OO Patch
Chris Bitmead wrote: > > While SQL3 talks about trees and leaf rows, it's not implemented like > that, so all this worrying about digging down trees and leafs is all a > bit mute. Moot. ;-) At a minimum, it seems to me, the backend must support the concept of multiple tuples with different attributes at the relation level since concurrency and rollback-ability of ALTER TABLE ADD COLUMN will cause two concurrent transactions to see a single relation with different attributes. It doesn't seem a large leap to support this concept for OO purposes from "leaf" to "base". For "base" to "leaf" type queries, wouldn't it be acceptable to return the base attributes only, as long as the equivalent of run-time type information could be had from the OID? Just curious, Mike Mascari
[GENERAL] Re: [HACKERS] Postgresql OO Patch
Chris Bitmead wrote: > > > In this > > case, it would only look down into the tree to 3 levels below supertable and > > you'd never get row-types that are down lower than level 3. Anyhow, I still > > don't think returning multple row-types is going to happen, OTOH, I'm pretty sure that original Postgres did allow for it. > > not that I have any authority one way or the other! :-) > > - Hannu
[GENERAL] Re: [HACKERS] Postgresql OO Patch
While SQL3 talks about trees and leaf rows, it's not implemented like that, so all this worrying about digging down trees and leafs is all a bit mute. "Robert B. Easter" wrote: > If it were allowed, you might have to > specify the level to dig to in the tree. The rows are shared among supertable > and subtables. One row in a leaf table has subrows in all its supertables up > the tree. If you do a "SELECT * FROM supertable*" (for example, if you were to > redefine table* to mean select heterogeneous rows), what row will you get for a > row that exists in a leaf? The same row is in all tables between supertable > and the leaf. I suppose it would be necessary to have the query check each row > and see how far down the tree it goes, or the system keeps track of that and > returns the row-type from the table that inserted it. OR, there could be some > extra specifier like "SELECT * FROM supertable DIGGING TO LEVEL 3". In this > case, it would only look down into the tree to 3 levels below supertable and > you'd never get row-types that are down lower than level 3. Anyhow, I still > don't think returning multple row-types is going to happen, not that I have any > authority one way or the other! :-) > > -- > Robert B. Easter > [EMAIL PROTECTED] -- Chris Bitmead mailto:[EMAIL PROTECTED] http://www.techphoto.org - Photography News, Stuff that Matters
[GENERAL] Re: [HACKERS] Postgresql OO Patch
On Sun, 21 May 2000, Chris Bitmead wrote: > Peter Eisentraut wrote: > > > > Chris writes: > > > > > I.e. "SELECT * FROM foobar*" becomes "SELECT * FROM foobar", and > > > "SELECT * from foobar" becomes "SELECT * FROM ONLY foobar". > > > > This aspect of the patch I wholeheartedly agree on. The rest I'm not sure > > about -- yet. :) > > > > > Benefits: > > > *) SQL3 says it. > > I also agree about the usage of ONLY, as long as it follows the official standardized SQL3 spec. About returning multiple types of rows again: I don't see that in SQL3 so far (difficult and time consuming to read). If it were allowed, you might have to specify the level to dig to in the tree. The rows are shared among supertable and subtables. One row in a leaf table has subrows in all its supertables up the tree. If you do a "SELECT * FROM supertable*" (for example, if you were to redefine table* to mean select heterogeneous rows), what row will you get for a row that exists in a leaf? The same row is in all tables between supertable and the leaf. I suppose it would be necessary to have the query check each row and see how far down the tree it goes, or the system keeps track of that and returns the row-type from the table that inserted it. OR, there could be some extra specifier like "SELECT * FROM supertable DIGGING TO LEVEL 3". In this case, it would only look down into the tree to 3 levels below supertable and you'd never get row-types that are down lower than level 3. Anyhow, I still don't think returning multple row-types is going to happen, not that I have any authority one way or the other! :-) -- Robert B. Easter [EMAIL PROTECTED]
