Re: [GENERAL] Handling of large objects in DBD::Pg? (fwd)
Attached are large object read and write functions I wrote based on
information I gleened from the DBD::Pg install test script.
They read and write to buffers rather than files because my
information wasn't comming from a file and wasn't going to one. However
if you need the data to go to a file you can either open a file handle
yourself in these methods and read into a scalar or write out from one, OR
you can scan the test.pl file in the DBD::Pg install to see how to use the
proper file based read and write Pg calls.
M. Tavasti <[EMAIL PROTECTED]> wrote:
>
> How do I handle large objects in DBD:Pg (perl DBI interface to
> postgresql)?
>
> I've tried to do like this, but not successfull, it looks like there
> is no data inserted. I tried to see in psql is there something, doing
> SELECT lo_export(data,"/tmp/foofaa.txt") from foofaa where id=;
>
> $obj_ins = $dbh->prepare(q{
>INSERT INTO foofaa
>(id,entry,data)
> VALUES
>(?,?,?)});
>
> $obj = $dbh->func("./$dir/obj.txt", 'lo_import');
> $obj_ins->execute($id,$ent,$obj);
>
>
>
>
> Manual page of DBD:Pg if confusing for lo_export:
>
> $ret = $dbh->func($lobjId, 'lo_export');
>
>Exports a large object into a Unix file. Returns
>false upon failure, true otherwise.
>
> To what file
>
> Any help welcome.
>
>
#!/usr/bin/perl -w
use strict;
use DBI;
use DBD::Pg;
my $dsn = "dbname=p1";
my $dbh = DBI->connect('dbi:Pg:dbname=p1', undef, undef, { AutoCommit => 1 });
my $buf = 'abcdefghijklmnopqrstuvwxyz' x 400;
my $id = write_blob($dbh, undef, $buf);
my $dat = read_blob($dbh, $id);
print "Done\n";
sub write_blob {
my ($dbh, $lobj_id, $data) = @_;
# begin transaction
$dbh->{AutoCommit} = 0;
# Create a new lo if we are not passed an lo object ID.
unless ($lobj_id) {
# Create the object.
$lobj_id = $dbh->func($dbh->{'pg_INV_WRITE'}, 'lo_creat');
}
# Open it to get a file descriptor.
my $lobj_fd = $dbh->func($lobj_id, $dbh->{'pg_INV_WRITE'}, 'lo_open');
$dbh->func($lobj_fd, 0, 0, 'lo_lseek');
# Write some data to it.
my $len = $dbh->func($lobj_fd, $data, length($data), 'lo_write');
die "Errors writing lo\n" if $len != length($data);
# Close 'er up.
$dbh->func($lobj_fd, 'lo_close') or die "Problems closing lo object\n";
# end transaction
$dbh->{AutoCommit} = 1;
return $lobj_id;
}
sub read_blob {
my ($dbh, $lobj_id) = @_;
my $data = '';
my $read_len = 256;
my $chunk = '';
# begin transaction
$dbh->{AutoCommit} = 0;
my $lobj_fd = $dbh->func($lobj_id, $dbh->{'pg_INV_READ'}, 'lo_open');
$dbh->func($lobj_fd, 0, 0, 'lo_lseek');
# Pull out all the data.
while ($dbh->func($lobj_fd, $chunk, $read_len, 'lo_read')) {
$data .= $chunk;
}
$dbh->func($lobj_fd, 'lo_close') or die "Problems closing lo object\n";
# end transaction
$dbh->{AutoCommit} = 1;
return $data;
}
[GENERAL] creating tables with different character set?
hi, i have a problem. i'm living in poland, which has its national characters. of course they work great under postgresql, but: when i use non-C locale all ~ '^xxx' and like 'xxx%' searches are not using index scan. this is paintful. for some of the tables i dont need all national characters. in fact i use only a-z0-9 characters which are subset of standard C-locale character set. so my question is. is it possible to make a table that way, that it will allow using indexing when searching for first x characters of string. at the moment the only solution i got is to make another database (with another postmaster process), but this is definetly not easy way. any other options? depesz -- hubert depesz lubaczewski http://www.depesz.pl/ najwspanialszą rzeczą jaką dało nam nowoczesne społeczeństwo, jest niesamowita wręcz łatwość unikania kontaktów z nim ...
[GENERAL]
i have created a table having blob object but now i am not able to insert a picture into the table. Can you send me sql command to insert picture into the table i need syntax. i tried through DB2IMAGE but failed to insert. if there is any other method of putting image in the table, kindly send me the same Akif noor = __ Do You Yahoo!? Yahoo! Auctions - Buy the things you want at great prices! http://auctions.yahoo.com/
[GENERAL] System Catalog
Hello, I working on your DB and I need to get some advanced data from the System Catalog. Pleace can you send me the system catalog or information. Carlos A. García [EMAIL PROTECTED] -- E-Mail Gratis La Brujula www.brujula.com.ar Su punto de partida internet
[GENERAL] Encoding: LATIN2 (hungary)
Hi, I want to set encoding hungarian language. I try to set LATIN2, but char order is "áabc..". The good order is "aábc...". What is the soultion? Thx, Andras ICQ: `find [EMAIL PROTECTED]`
[GENERAL] Unixware 7.1.1 problem with psql
Hi all, I try to use Postgres 7.0.3 on SCO Unixware 7.1.1. It compiles fine, but I have the following error message when a launch psql: psql: No pg_hba.conf entry for localhost, user postgres, database postgres. I have made no change to the pg-hba.conf. I have read in the documentation, it is ok for all operation on local by default. I don't have the UDK on that machine, but I seem to have no impact ? Am I right ? I don't understand what I am doing wrong. Any help appreciated. Joel
Re: [GENERAL] www-sql question
Hello Tom, thanks for taking a look at www-sql as I was going to use as a quick solution to having to write so much code, but now a better solution seems to have presented itself and should work without any problems with PostgreSQL. I was initially looking at an application called MetaHTML, but the developers had not really done anything with it for a long time until recently. They have just released (opensource) and working version MetaHTML 6.10 which compiles fine and is very easy to install. This particular piece is MUCH more advanced than www-sql which really need a major re-work. I found out, with www-sql, that it would not return any error messages or results of the query, but that when I compiled up your examples in the libpq++ directory and made a few simple changes to reflect that same database which www-sql used, I could easily get the data from the database. This led me to think that there was some compatibility issues along with the age of development of www-sql as well. In any case, it is all academic now and I will be using the NEW MetaHTML release for our project with PostgreSQL. Thanks for the effort, Lonnie --- Tom Lane <[EMAIL PROTECTED]> wrote: > Lonnie Cumberland <[EMAIL PROTECTED]> writes: > > The problem is that I am not getting any error messages and it is not > > returning any data from the database. > > > It appears that there has been some changes in the way that things are > being > > done since the last www-sql version had been released. > > I pulled down www-sql-0.5.7 and took a very quick look at its pgsql.c > module, and didn't see any obvious silliness. I don't have time to try > to run a real test though. Try running the postmaster with -d2 (and > without -S) so that queries are logged in the postmaster log, and then > look to see what queries are being received from the www-sql client. > > BTW, you are running www-sql with a recent libpq library compiled into > it, no? > > regards, tom lane __ Do You Yahoo!? Yahoo! Auctions - Buy the things you want at great prices! http://auctions.yahoo.com/
[GENERAL] psql - use of default editor
Is there a way to temporarily turn of the use of a default editor when using psql ? I need to do this when I try to use psql from a script/batch file. Whenever a query is executed psql displays a result, displays it - I need to hit 'q' to end the display and would like to avoid this unnecessary (when running psql from a script) user input. Thanks, Rini __ Do You Yahoo!? Yahoo! Auctions - Buy the things you want at great prices! http://auctions.yahoo.com/
[GENERAL] Re: Error: 'cache lookup failed' w/trigger
Meanwhile, I've found the source of my problem myself. When I drop a function and re-create it (this is what phpPgAdmin does when "modifying" a function), I also have to drop and re-create the corresponding trigger. Again one of these 'gotchas' for beginners... mg schrieb: > Hello, > > I'm new to Postgresql and just tried to write my very first pgsql > trigger procedure. So > please forgive me if this is a stupid question. > > This is what I did: > 1. using bash as user postgres (my db superuser) on my RH6.2 linux box: > bash$ export PGLIB=/usr/lib/pgsql > bash$ createlang plpgsql mydb > > 2. using phpPgAdmin, I then could create the following procedure: > > CREATE FUNCTION "datetimestamp"() RETURNS OPAQUE AS > 'BEGIN NEW.modificationdatetime := \'now\' END;' > LANGUAGE 'plpgsql' > > 3. Then I created a trigger: > CREATE TRIGGER "accounts_datetimestamp" BEFORE INSERT OR UPDATE ON > "accounts" FOR EACH ROW EXECUTE PROCEDURE datetimestamp(); > > Now everytime I try to update something in my accounts table I get the > message: > ERROR: fmgr_info: function 51232: cache lookup failed > > What's wrong here? > BTW: is there a source for trigger procedure examples? These trigger > procedures could help me solve a lot of problems. > > Thanks for any suggestions > Greetings from switzerland > --Marcel
[GENERAL] Help with plpgsql - subst variable value
Hi all. This my code snapshot: CREATE FUNCTION CreateObject(varchar(600)) RETURNS int4 AS ' DECLARE . . . ObjectSeqName text; BEGIN . . .. ObjectSeqName := ''ObjectLog'' || ObjectID || ''_Seq''; CREATE SEQUENCE ObjectSeqName start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 CYCLE; . . . END IF; RETURN ObjectID; END; ' LANGUAGE 'plpgsql'; When I run this function I got ERROR: parser: parse error at or near "$1" So how can get value of ObjectSeqName in sequence/table/etc. name?
[GENERAL] PL/PGSQL Programming Guide/Reference Guide Want
Please tell me where and how I get the PL/PGSQL Programming Guide and Reference Guide? Because the online documentation pages are not enough examples. Thank you very much! -- Why we want to teach our babies to talk and walk, then later we tell them "sit down!", "be quiet!" ? Democracy is not a better way for a solution, it is just another way to spread the blames. --Raymond begin:vcard n:Chui;Raymond tel;fax:(301)713-0963 tel;work:(301)713-0624 Ext. 168 x-mozilla-html:TRUE url:http://members.xoom.com/rchui/ org:NWS, NOAA version:2.1 email;internet:[EMAIL PROTECTED] title:SA, DBA note:ICQ #: 16722494 adr;quoted-printable:;;NOAA, NWS, Office of Hydrology, OH=0D=0A1325 East-West Highway, Room 8112;Silver Spring;MD;20910-3283;U.S.A. x-mozilla-cpt:;-6384 fn:Raymond Chui end:vcard
[GENERAL] timestamp in logfile
a simple question: in the version 7.1 is there a timestamp in this logfile it's really bad if someone lokks at the logfile and one does not know when the error happenend. If anyone knows abaout it please tell me. Ewald
[GENERAL] Help with plpgsql - subst variable value
Hi all. This my code snapshot: CREATE FUNCTION CreateObject(varchar(600)) RETURNS int4 AS ' DECLARE . . . ObjectSeqName text; BEGIN . . .. ObjectSeqName := ''ObjectLog'' || ObjectID || ''_Seq''; CREATE SEQUENCE ObjectSeqName start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 CYCLE; . . . END IF; RETURN ObjectID; END; ' LANGUAGE 'plpgsql'; When I run this function I got ERROR: parser: parse error at or near "$1" So how can get value of ObjectSeqName in sequence/table/etc. name?
Re: [GENERAL] is this expected or am i on crack?
i got help with this from one of the developers on irc, thanks man, sorry i forgot your handle: (hope this is right) select keyword, count(*) from facts where having count(*) > 1; i think that is it, i dont have to table anymore, building a better schemea and more stuff for everything, but that was just sql ninja-ness man, thanks, Chris Humphries ps -> open magazine (openmagazine.net) had an article in it that said good things about postgresql btw, just in case interested...it is a free subscription i think. and no this isnt spam, i dont work for them or anything, just an observation... On Sun, Feb 18, 2001 at 02:54:08AM -0800, Chris Humphries wrote: > hello all, > > first off, some information: > = > = > the table: > Table "facts" > Attribute | Type | Modifier > -+--+-- > keyword | varchar(80) | > description | varchar(255) | > url | varchar(255) | > > > the sql: > select keyword from facts as f1 > where 1 <> (select count(*) from facts as f2 where f1.keyword = f2.keyword) > order by keyword; > > my system: > Linux kernel 2.2.x > p166 > 32M ram > 200M+ swap > = > = > > it took about 65 minutes to complete. i know that it is doing alot of work, > but it there a way that it could be sped up, like something i could configure > or something that i could do to make it faster? > > just looking to see if the time it took to take this is to be expected? > postmaster was using from 78-98% of the cpu for the whole time. > > in hind site i wish i would have made keyword not null primary key... > *sigh*, learn from mistakes i guess. now have to fix the duplicates and > move all the rows into a new table with keyword as primary key... > > thanks for any help, > and developers: thanks for developing a real non-toy database for the masses, > > -chris humphries > > > > > > > > > >
[GENERAL] how critical is WAL ?
Hi, I happenned to come across the following in the documentation on WAL implementation in v7.1 - * Before WAL, any crash during writing could result in: 1.index tuples pointing to non-existent table rows 2.index tuples lost in split operations 3.totally corrupted table or index page content, because of partially written data pages * Does anybody know what kind of a problem this refers to ? Does this mean that incomplete transactions would be stored or does this mean that the entire table might get corrupted and unusable, implying loss of all data ? ( I am using postgresql v7.0.x , and would ideally like to migrate to v7.1 after a few months ... unless it is critical enough to do so earlier. ) Thanks, Rini __ Do You Yahoo!? Yahoo! Auctions - Buy the things you want at great prices! http://auctions.yahoo.com/
[GENERAL] /usr/lib/perl5/5.6/i386-linux/auto/Opcode/Opcode.so doesn't exist.
I use debian sid, kernel 2.2.18, libc6 2.2.2-1, postgresql 7.0.3-4 After upgrading to perl 5.6.0-19 I haven't been able to use my postgresql function created with LANGUAGE 'plperl'. Calling function failed with message: "ERROR: Load of file /usr/lib/postgresql/lib/plperl.so failed: /usr/lib/perl5/5.6/i386-linux/auto/Opcode/Opcode.so: cannot load shared object file: No such file or directory" After creating a link /usr/lib/perl5/5.6/i386-linux/auto/Opcode->/usr/lib/perl/5.6.0/auto/Opcode everything's begun to work as before. Is it a bug or I miss anything in my configuration? Thank you, Mikhail.
Re: [GENERAL] Re: postmaster question
Hello Mitch, I have been trying to do just that and hove found that by going to my init.d directory and starting up the "postgresql" service helps. Now the problem seems to be that even though I have just used "pg_passwd" to add me as a user, I am now getting an error message back saying that: --- Connection to database 'template1' failed. FATAL 1: SetUserId: user 'lonnie' is not in 'pg_shadow' createdb: database creation failed on testpg. Could you please tell me what I am doing wrong with this stuff? I am very new to using it and still do not have feel for the way that things are done. I just want to do a simple test on this system by adding a user, create a database, and start up "psql" with it. Cheers, Lonnie --- Mitch Vincent <[EMAIL PROTECTED]> wrote: > Read the installation documentation that comes with the source/binaries. :-) > > -Mitch > > - Original Message - > From: "root" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Tuesday, February 20, 2001 6:48 PM > Subject: postmaster question > > > > Hello All, > > > > does anyone know how to get the postmaster set up properly in a native > > version of Mandrake Linux 7.1? > > > > I see that that RPM's are there, but the the backend daemon is not up > > and running. I tried to just enter "postmaster" but it says that > > I cannot be runnig it as root and that it needs it's own userid. > > > > I did not see where in the docs that the install discusses this. > > > > Any ideas anyone? > > > > Best Regards, > > Lonnie > > > > > __ Do You Yahoo!? Yahoo! Auctions - Buy the things you want at great prices! http://auctions.yahoo.com/
[GENERAL] problems with copy
Hello I'm starting using PostgreSQL and want to import datas from my old database. I've made ASCII delimited files and want to use the copy instruction. Having the server started, I do the following : > psql mydb >copy table from 'myfile' using delimiters ';' ; I'vegot an error saying that the backend server can't read myfile. I've check rights on this file and every user can read this file. I've created the table and checked that it exists. Can someone help Me ?? Thanks in advance Stephane
[GENERAL] Error: 'cache lookup failed' w/trigger
Hello, I'm new to Postgresql and just tried to write my very first pgsql trigger procedure. So please forgive me if this is a stupid question. This is what I did: 1. using bash as user postgres (my db superuser) on my RH6.2 linux box: bash$ export PGLIB=/usr/lib/pgsql bash$ createlang plpgsql mydb 2. using phpPgAdmin, I then could create the following procedure: CREATE FUNCTION "datetimestamp"() RETURNS OPAQUE AS 'BEGIN NEW.modificationdatetime := \'now\' END;' LANGUAGE 'plpgsql' 3. Then I created a trigger: CREATE TRIGGER "accounts_datetimestamp" BEFORE INSERT OR UPDATE ON "accounts" FOR EACH ROW EXECUTE PROCEDURE datetimestamp(); Now everytime I try to update something in my accounts table I get the message: ERROR: fmgr_info: function 51232: cache lookup failed What's wrong here? BTW: is there a source for trigger procedure examples? These trigger procedures could help me solve a lot of problems. Thanks for any suggestions Greetings from switzerland --Marcel
[GENERAL] handling of database size exceeding physical disk space
Hi, Does anyone know how postgres/ postmaster handles the situation where the physical hard disk space is full ? Does it crash / corrupt the database, or does it cleanly exit with appopriate message so that relevant tables can be pruned (by the user) to free up disk space and get it working again ? Thanks, Rini __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/
[GENERAL] JDBC problem with Postgres 7.0.3
Hello,
I am using the postgres driver for my Java code (JDK 1.2.2) which came
with the Postgres 7.0.3 tar file. I am trying get the name of a column
which is essentially the primary key in this table. Unfortunately, the
result set returned does not have any records(as shown below). This code
works fine on Oracle. I also tried jdbc7.0-1.2.jar but the same problem
occurs.
Code
public String getUniqueRowName() throws SQLException{
DatabaseMetaData dma = m_Connection.getMetaData();
ResultSet rs = dma.getIndexInfo("", "", "", true,
false);
if(rs == null){
return null;
}
rs.next(); No records here
String sIndex = rs.getString("COLUMN_NAME");
return sIndex;
}
Any help is appreciated.
Thanks in advance for your time
Santosh Rau
[GENERAL] copying tables
Hi. I am trying to copy tables. I want to copy a table in one database to a
table in another database. The tables are identical. Here is to code and the
error when using DBI. Can I just use postgresQL somehow?
Thank you.
Table= fs648
+--+--+---+
| Field | Type| Length|
+--+--+---+
| day | text | var |
| type | text | var |
| flight | text | var |
| scene| text | var |
| pattern | int4 | 4 |
| leg | text | var |
| id | int4 not null| 4 |
+--+--+---+
#!/usr/bin/perl -wT
use strict;
use DBI;
my $dbh = DBI->connect("dbi:Pg:dbname=misr_l1;
host=sundog.larc.nasa.gov;port=5432", "", "")
or die "Can't connect\n"; #connect to database
my $sth = $dbh->prepare("SELECT * from fs648");
$sth->execute();
my $array_ref = $sth->fetchall_arrayref();
$sth->finish;
$dbh->disconnect or warn "Disconnection failed\n";
$dbh = DBI->connect("dbi:Pg:dbname=misr_l0;
host=sundog.larc.nasa.gov;port=5432", "", "")
or die "Can't connect\n"; #connect to database
foreach my $row ($array_ref)
{ my ($day, $type, $flight, $scene, $pattern, $leg, $id) = @$row;
my $dayq = $dbh->quote($day);
my $typeq = $dbh->quote($type);
my $flightq = $dbh->quote($flight);
my $sceneq = $dbh->quote($scene);
my $legq = $dbh->quote($leg);
my $rows = $dbh->do("INSERT INTO fs648 VALUES ( $dayq, $typeq,
$flightq, $sceneq, $pattern, $legq, $id)");
}
$dbh->disconnect or warn "Disconnection failed\n";
DBD::Pg::db do failed: ERROR: parser: parse error at or near "xe1154"
$pattern and $id are integers and so don't need quoting. It doesn't work if I
quote them anyway.
Thanks again!
Bob Kernell
Research Scientist
Analytical Services & Materials, Inc.
NASA Atmospheric Sciences Competency
Radiation and Aerolsols Branch
email: [EMAIL PROTECTED]
tel: 757-827-4631
Re: [GENERAL] Multiple triggers/rules
[EMAIL PROTECTED] (Bruce Richardson) writes: > OK, great. Now - if a table has both a before-update trigger and an > DO (as opposed to DO INSTEAD) update rule and the trigger cancels the > update, is the update rule (or rules) cancelled? No. The trigger can only cancel the update of the specific tuple it's being invoked for --- the query as a whole runs normally. Indeed, considering that ON UPDATE rules run before the original query, it'd be difficult for a trigger on the original table to affect them. You may have a conceptual problem here. An ON UPDATE/DELETE rule specifies an additional or substitute query to be run separately from the initial UPDATE/DELETE query, but on the same tuple set (as achieved by adding the initial query's WHERE clause to the rule's WHERE). It's not something you can turn on or off per affected tuple. A trigger, on the other hand, is fired separately for each tuple that a query is about to (or just did) affect. Triggers are a much lower-level mechanism than rules. regards, tom lane
[GENERAL] PHP 3.0.16 and pg_cmdtuples
Hi, I have a - development machine with postgresql 7.0.3 installed and php 3.0.16 with phplib 7.0.2 - production machine idemdito When running my webapplication on the development machine I have no problems, but when running it from the production machine I constantly get the message: -->>> This compilation does not support pg_cmdtuples() in db_pgsql.inc on line 122 which is crapp because I built it with exactly the same config options. What could pissibly be wrong here ? Thanks, Feite Brekeveld
[GENERAL] PostgreSQL packages for Debian potato
We have a number of client machines running Debian Linux, "potato"
release (i.e. 2.2, latest stable). The provided PostgreSQL is release
6.5.3, but I managed to track changes in unstable ("woody") until 7.0.2,
as long as packages were built using an older glibc. Now, unstable
packages (of 7.0.3) are linked against a newer one and using them means
upgrading the whole system, or building from source, or breaking
dependencies, etc., all steps that I am not willing to take.
Sorry for this long dissertation about the Debian world-of-packages,
this is to explain why there aren't anymore, to my knowledge, .deb
packages easily installable on potato and I preciously keep a number of
packages knowing that I have no easy way to reobtain them. But I am
missing ecpg and I cannot install the one provided by Debian since it
carries a huge set of unsatisfied dependencies.
Q1: is there anyone out there with a ecpg_7.0.2-X_i386.deb available?
Q2: are there anywhere Debian potato packages of recent releases?
TIA
--
Alessio F. Bragadini[EMAIL PROTECTED]
APL Financial Services http://village.albourne.com
Nicosia, Cyprus phone: +357-2-755750
"It is more complicated than you think"
-- The Eighth Networking Truth from RFC 1925
[GENERAL] RE: [SQL] handling of database size exceeding physical disk space
Thanks ! I'm using JDBC to insert into the tables. Would it throw an SQLException in such a situation ? Rini --- "Diehl, Jeffrey" <[EMAIL PROTECTED]> wrote: > I happen to know this very well... It handles > things very gracefully as far > as I can tell. I complains that it can't extend the > table and bails out of > the transaction. I just wish it didn't happen so > often... > > Mike Diehl, > Network Monitoring Tool Devl. > 284-3137 > [EMAIL PROTECTED] > > > > -Original Message- > > From: Rini Dutta [mailto:[EMAIL PROTECTED]] > > Sent: February 20, 2001 9:35 AM > > To: [EMAIL PROTECTED]; > [EMAIL PROTECTED] > > Cc: [EMAIL PROTECTED] > > Subject: [SQL] handling of database size exceeding > physical disk space > > > > > > Hi, > > > > Does anyone know how postgres/ postmaster handles > the > > situation where the physical hard disk space is > full ? > > Does it crash / corrupt the database, or does it > > cleanly exit with appopriate message so that > relevant > > tables can be pruned (by the user) to free up disk > > space and get it working again ? > > > > Thanks, > > Rini > > > > __ > > Do You Yahoo!? > > Get personalized email addresses from Yahoo! Mail > - only $35 > > a year! http://personal.mail.yahoo.com/ > > > __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/
Re: [GENERAL] how do I get arbitrary sql results as strings in C
If there's no simple way to do this, I think I found an example of what I
need in function printQuery in psql/print.c
Alex.
On Fri, 23 Feb 2001, adb wrote:
>
> Is there an easy way in libpq to get the results from
> any query as strings.
>
> Imagine a cgi where you input any query and then it prints
> out the rows returned
>
> Something like this example of mysql code:
>
> // fetch each row using mysql_fetch_row
> MYSQL_ROW row;
> unsigned int num_fields;
>
> if( sql_result ) {
> num_fields = mysql_num_fields( sql_result );
>
> while( ( row = mysql_fetch_row( sql_result ) ) ) {
>
> outputBuf_.print( L"" );
> for( unsigned int i = 0; i < num_fields ; i++ ) {
> char * rowVal = row[i];
> if( !rowVal ) {
> rowVal = "";
> }
>
> outputBuf_.print( L"%a", rowVal );
> }
> outputBuf_.print( L"\n" );
> }
> }
>
> Thanks,
>
> Alex.
>
Re: [GENERAL] Query with multiples operators BETWEEN
Tom Lane wrote: > > Do a VACUUM ANALYZE, for starters. These results look like the planner > is working with the initial default estimates for a never-vacuumed table. > > > Indexes might be a good idea too. > http://www.postgresql.org/devel-corner/docs/postgres/indices.html > has a good intro to the basics. > > regards, tom lane The qwery I have problem with is always : SELECT e.name FROM Observation o, Exposure_EPIC e WHERE o.numObs = e.obs AND e.instrPN IS NOT NULL AND o.RA BETWEEN 3 AND 5 AND o.DE BETWEEN 2 AND 7; EXPLAIN result is : NOTICE: QUERY PLAN: Nested Loop (cost=0.00..56.67 rows=3 width=20) -> Seq Scan on observation o (cost=0.00..30.00 rows=1 width=4) -> Seq Scan on exposure_epic e (cost=0.00..22.50 rows=333 width=16) EXPLAIN I have created an index on columns ra and de : CREATE INDEX ra_de_ind ON observation(ra,de); CLUSTER ra_de_ind ON observation; VACUUM observation; EXPLAIN result becomes : Nested Loop (cost=0.00..28.69 rows=3 width=20) -> Index Scan using ra_de_ind on observation o (cost=0.00..2.03 rows=1 width=4) -> Seq Scan on exposure_epic e (cost=0.00..22.50 rows=333 width=16) But the qwery is always not able to find what I'm looking for. Renaud THONNART
[GENERAL] UNIQUE constraint fails
With 7.1b4:
test=# create table auction_type(id serial,login text,birthday timestamp);
test=# create table auction(unique(login)) inherits("auction_type");
ERROR: inherited attribute "login" cannot be a PRIMARY KEY because it is not marked
NOT NULL
But I didn't ask that "login" be a PRIMARY KEY, only a UNIQUE. What's
going on there?
Cheers,
--
ARICIE: Vous laissez dans l'erreur un père qui vous aime ?
Cruel, si de mes pleurs méprisant le pouvoir,
Vous consentez sans peine à ne plus me revoir,
Partez, séparez-vous de la triste Aricie.
(Phèdre, J-B Racine, acte 5, scène 1)
[GENERAL] Re: how critical is WAL
> > > Indexes could get corrupt, requiring rebuilding. > > > That problem is gone > > > with WAL. Transaction commits where always safe. > > > > > > > Thanks. I was specifically asking about Result '3' > > above mentions 'totally corrupted table page content > > because of partially written data pages' . Is this > > also repairable ? > > I never heard of that happening to anyone. Sorry to disappoint you but it seems to be the case with my crash. One of our customers accindentally switched off his machine. This is a shop with 5500+ items. The table contained these items were lost. All the other tables (bills, partners, etc.) were in good shape, select retrieved all data from them. I looked into the postgres manuals and as it suggests, I stopped postmaster and I tried to reindex this table in a standalone postgres. "reindex table" (without force) did not reindexed. "reindex index" dumped core. I dropped the primary index and tried to recreate it, but it also dumped core. I guess it falls under the 3rd category of your listed types of data loss. :-( The question still is : is this kind of failure repairable? The system is: RH 6.2, kernel-2.2.16-3, postgresql-7.0.3 rpms from ftp.postgresql.org. Regards, Zoltan Boszormenyi
