[SQL] DBMirror.pl performance change
Steven Hi,
i hope you are ok.
I discovered a problem in DBMirror.pl, performance wise.
pending.c stores data in a way
very similar to the PgSQL input "\" escaped format.
When the field is of type bytea, and the source of data is binary, then
this produces 2 additional backslashes for every unprintable
char.
The performance in function extractData in DBMirror.pl, really suffers
from this condition, since it breaks data in chunks of "\" delimited
strings.
Informally speaking, performance tends to be O(n) where n is the size
of the data.
This can be remedied if we break data in chunks of "'" rather than "\".
"'" happens much more infrequently in common binary files (bz2, tiff, jpg,
pdf etc..), and if we notice that odd number of contained "\", signals an
intermidiate "'", whereas even number of "\" signals the final "'",
then we can make this routine run much faster.
I attach the new extractData function.
Please feel free for any comments.
--
-Achilleus
sub extractData($$) {
my $pendingResult = $_[0];
my $currentTuple = $_[1];
my $fnumber;
my %valuesHash;
$fnumber = 4;
my $dataField = $pendingResult->getvalue($currentTuple,$fnumber);
my $numofbs;
while(length($dataField)>0) {
# Extract the field name that is surronded by double quotes
$dataField =~ m/(\".*?\")/s;
my $fieldName = $1;
$dataField = substr $dataField ,length($fieldName);
$fieldName =~ s/\"//g; #Remove the surronding " signs.
if($dataField =~ m/(^= )/s) {
#Matched null
$dataField = substr $dataField , length($1);
$valuesHash{$fieldName}=undef;
}
elsif ($dataField =~ m/(^=\')/s) {
#Has data.
my $value;
$dataField = substr $dataField ,2; #Skip the ='
LOOP: { #This is to allow us to use last from a do loop.
#Recommended in perlsyn manpage.
do {
my $matchString;
my $matchString2;
#Find the substring ending with the first ' or first \
$dataField =~ m/(.*?[\'])?/s;
$matchString = $1;
$numofbs = ($matchString =~ tr/\\//) % 2;
if ($numofbs == 1) { #// odd number of \, i.e. intermediate '
$matchString2 = substr $matchString,0, length($matchString)-2;
$matchString2 =~ s//\\/g;
$value .= ($matchString2 . "\'");
$dataField = substr $dataField,length($matchString);
}
else { #// even number of \, i.e. found end of data
$matchString2 = substr $matchString,0, length($matchString)-1;
$matchString2 =~ s//\\/g;
$value .= $matchString2;
$dataField = substr $dataField,length($matchString)+1;
last;
}
} until(length($dataField)==0);
}
$valuesHash{$fieldName} = $value;
}#else if
else {
logErrorMessage "Error in PendingData Sequence Id " .
$pendingResult->getvalue($currentTuple,0);
die;
}
} #while
return %valuesHash;
}
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] select and as doubt
On Sun, Jan 15, 2006 at 12:59:48 -0200, Alexandre Gonçalves Jacarandá <[EMAIL PROTECTED]> wrote: > Thanks for reply Tom, but which docs should I read for that ? The documentation for whatever ever programming language you will be using to generate the queries. > > Tom Lane escreveu: > > =?ISO-8859-1?Q?Alexandre_Gon=E7alves_Jacarand=E1?= <[EMAIL PROTECTED]> > > writes: > > > >>It's possible to make an SELECT inside an AS clause ? > > > > > > No. You could try building the query as a string. > > > > regards, tom lane > > > > ---(end of broadcast)--- > > TIP 5: don't forget to increase your free space map settings > > > > > -- > __ > Alguns caminham pelo arco, > eu caminho pela reta. > > Alexandre Gonçalves Jacarandá > Assessor de Tecnologia de Informação > Tel.: 0 ** 21 8131-2313 > > > > > > ___ > Yahoo! doce lar. Faça do Yahoo! sua homepage. > http://br.yahoo.com/homepageset.html > > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 1: 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
[SQL] pgadmin
I'm having trouble with installing pgadmin. If this is not the apporpriate group for seeking help with this, please excuse me and tell the correct one. I have not found anything else more appropriate. 'make' for pgadmin fails, I think it is because I don't have wxWidgets. wxWidgets fails. I think it is because I don't have Motif/Lesstif. Lesstif fails because of another large list of dependencies. For Pete's sake! Do I have to double the size of my Operating System (which is Linux, Redhat 9, BTW) just to install pgadmin? I've installed lots of software packages over the years, mostly through the ./configure;make;make install sequence, but this is the most difficult installation I've ever encountered. I don't have a stripped down version of the O/S either... it's pretty much a full install of RH9. I'm thinking that I must be doing something wrong. PostgreSQL and PostGIS installed without any problems, but this single utility (pgadmin) is really throwing me for a loop. Does anyone have any ideas I could try? Mark ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] pgadmin
Mark Fenbers wrote: > I'm having trouble with installing pgadmin. If this is not the > apporpriate group for seeking help with this, please excuse > me and tell > the correct one. I have not found anything else more appropriate. > > 'make' for pgadmin fails, I think it is because I don't have > wxWidgets. > wxWidgets fails. I think it is because I don't have Motif/Lesstif. > Lesstif fails because of another large list of dependencies. > For Pete's > sake! Do I have to double the size of my Operating System (which is > Linux, Redhat 9, BTW) just to install pgadmin? I've > installed lots of > software packages over the years, mostly through the > ./configure;make;make install sequence, but this is the most > difficult > installation I've ever encountered. I don't have a stripped down > version of the O/S either... it's pretty much a full install of RH9. > > Does anyone have any ideas I could try? Find and install an RPM (or an SRPM) for pgadmin, rather than installing it from the source tarball? The pbone RPM search suggests that there are such things, though they may be a few versions behind*. When I was using redhat 9, I found that apt-rpm was invaluable; the lack of any other automatic dependency resolution tools did rather make RPMs a pain. -Owen * for Redhat 9. There's more-recent pgadmin packages for Fedora, which may have spec files you can make use of to build an RPM for older Redhat. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] pgadmin
Mark Fenbers <[EMAIL PROTECTED]> writes: > ... I don't have a stripped down > version of the O/S either... it's pretty much a full install of RH9. > I'm thinking that I must be doing something wrong. Er ... using RH9 maybe? That's been obsolete for a long time. Fedora Core 3 or 4 would be something reasonable to be using today. Anyway, no this is not the right list for asking for build help for pgadmin. Try pgadmin-support, or maybe pgadmin-hackers. regards, tom lane ---(end of broadcast)--- TIP 1: 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: [SQL] How to implement Microsoft Access boolean (YESNO)
"Jesper K. Pedersen" <[EMAIL PROTECTED]> writes: > Having checked the I/O format it seems that MS Access exports the > values of a YESNO field as 0 and 1 If only Postgres's boolean type were as helpful. -- greg ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] How to implement Microsoft Access boolean (YESNO)
Greg Stark <[EMAIL PROTECTED]> writes: > "Jesper K. Pedersen" <[EMAIL PROTECTED]> writes: >> Having checked the I/O format it seems that MS Access exports the >> values of a YESNO field as 0 and 1 > If only Postgres's boolean type were as helpful. There's a cast to int in 8.1, and you can roll-your-own easily in prior releases ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] How to implement Microsoft Access boolean (YESNO)
Tom Lane <[EMAIL PROTECTED]> writes:
> Greg Stark <[EMAIL PROTECTED]> writes:
> > "Jesper K. Pedersen" <[EMAIL PROTECTED]> writes:
> >> Having checked the I/O format it seems that MS Access exports the
> >> values of a YESNO field as 0 and 1
>
> > If only Postgres's boolean type were as helpful.
>
> There's a cast to int in 8.1, and you can roll-your-own easily in prior
> releases ...
The annoying thing about is that in just about any client language you'll get
't' and 'f' by default and both will evaluate to false. So any user who tries
to do things the obvious way like this will get a surprise:
if ($db->query("select canlogin from users where userid = ?",$userid)) {
...
}
Is there an implicit cast from ints? So if I pass a 0 or 1 argument for a
boolean parameter now it'll work? That definitely didn't work in the past.
--
greg
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
