FW: [GENERAL] code to cancel a running query, worker thread
Help needed,
i have the following peice of
code, which is meant for cancelling queries in between
import java.sql.*;
public class QueryExecutor implements Runnable {
/** * @param args */ private Thread
worker; private Params params; private Results
results; private volatile boolean cancelRequest; private
volatile boolean closeRequest;
private class Params { public Statement
statement; public String
query; public boolean
pending; } private class Results {
public ResultSet rs; public SQLException
exception; public boolean serviced; }
public QueryExecutor() { params = new
Params(); results = new Results(); worker = new
Thread(this); worker.start(); } /** *
Executes an SQL query. * The method can be interrupted by another
thread at any moment. * @return ResultSet if
execution successful * @exception SQLException if a database error
occurs * @exception InterruptedException if interrupted by another
thread **/
public synchronized ResultSet executeQuery(Statement statement,
String query) throws
SQLException, InterruptedException { //Set query
parameters synchronized(params)
{ params.statement =
statement; params.query =
query; params.pending =
true;
params.notify(); }
synchronized(results)
{ try
{
//Wait for the query to
complete
while(!results.serviced)
{
results.wait();
System.out.println("waiting for
results");
}
System.out.println("obtained
results");
if (results.exception != null)
{
throw
results.exception;
} } catch
(InterruptedException e) {
System.out.println("Cancelling");
cancel();
//throw e; } finally
{
results.serviced = false;
} return
results.rs; } }
private void cancel() { cancelRequest =
true; try
{
params.statement.cancel();
synchronized(results)
{
while(!results.serviced)
{
results.wait();
}
} } catch (SQLException e)
{
return; } catch (InterruptedException e)
{
return; } finally
{ cancelRequest =
false; } }
public void close() { closeRequest =
true; if (params.statement != null)
{ cancel(); } worker.interrupt(); try
{ worker.join(); } catch
(InterruptedException e) {} }
// The implementation of the Runnable interface (for the worker
thread) public void run() { ResultSet rs =
null; SQLException ex =
null; while(!closeRequest)
{ synchronized(params)
{
try
{
//Wait for query
parameters
while(!params.pending)
{
params.wait();
}
params.pending =
false;
} catch (InterruptedException e)
{
if (closeRequest)
{
return;
}
}
//Execute
query
try
{
rs =
params.statement.executeQuery(
params.query);
System.out.println(params.query);
} catch (SQLException e)
{
if (!cancelRequest)
{
ex =
e;
}
} }
//Set query
results
synchronized(results)
{
results.rs =
rs;
results.exception =
ex;
results.serviced =
true;
results.notify();
} } }}
in the front end i select a particular item , whcih will perform
executeQuery,
when i select another item, the prev query gets cancelled and new one is
executed.
however if i change my selection very fast, it seems that the worker thread
stops responding.
and then even if i click on other items, no query gets submitted.
Is the above peice of code fine, does the problem lie in the code which
calls the executeQuery of QueryExecutor?
Thanks,
regards
Surabhi
Re: [GENERAL] primary keys
On Wed, 19 Apr 2006 19:39:45 -0700, Orion Henry <[EMAIL PROTECTED]> wrote: > I'm trying to craft a query that will determine what column(s) are the > primary key for a given table. I have succeeded but the query is so > ugly that it borders on silly and cannot work for an arbitrary number of > tables since indkey is an int2vect and the ANY keyword does not work on > it. > > Please tell me there's an easier way to do this. Here is the query for > tablename $table. > > SELECT attname > FROM pg_index > JOIN pg_class ON (indrelid = pg_class.oid) > JOIN pg_attribute ON (attrelid = pg_class.oid) > WHERE indisprimary IS TRUE > AND (attnum = indkey[0] OR attnum = indkey[1] OR attnum = indkey[2]) > AND relname = '$table'; > > Orion works for me on version 8.1.3 SELECT attname FROM pg_index JOIN pg_class ON (indrelid = pg_class.oid) JOIN pg_attribute ON (attrelid = pg_class.oid) WHERE indisprimary IS TRUE AND attnum = any(indkey) AND relname = $tablename; or on v7 you could try select pcl.relname, (select array_accum(attname) from pg_attribute where attrelid = pco.conrelid and attnum = any(pco.conkey)) as cols >from pg_constraint pco join pg_class pcl on pcl.oid = pco.conrelid where pcl.relname = $tablename and pco.contype = 'p' klint. +---+-+ : Klint Gore: "Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] deleting table content through command prompt
Try psql.exe -h localhost -U your_user -d your_database -c "drop table your_tablename;" Assumption: windows switches are the same as the *nix ones > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of > [EMAIL PROTECTED] > Sent: Thursday, 20 April 2006 9:04 AM > To: pgsql-general@postgresql.org > Subject: [GENERAL] deleting table content through command prompt > > > hi all, > i am trying to create a database backup and restore windows > batch file, > which when run will do a database table backup and a corresponding > table restore. > > now for table_usernames backup i run something like from the command > prompt, > pg_dump.exe -i -h localhost -p 5432 -U postgres -F t -v -f > "C:\usernames.backup" -t table_usernames "loginDetails" > now for db restore i run something like from the command prompt, > pg_restore.exe -i -h localhost -p 5432 -U postgres -d > "loginDetails" -a > -t table_usernames -v "C:\usernames.backup" > > the problem is that after i do a backup i need to delete the table > content and then do a restore. through pgadmin i would do DELETE FROM > table_usernames. however i need to do this from the command prompt, > like how i am doing above. > > it seems very obvious and simple, but i am unable to think of how i > would do deletion of a table from the command prompt > > thanks for all feedback and help. > > > ---(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 > The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] locating a primary key
Orion wrote: I'm trying to craft a query that will determine what column(s) belong to the primary key for a given table. This seems like the kind of thing that would very common for people to try to do but my query was rather ugly and not flexible since it does not work for an arbitrary number of columns in the primary key. This is all due to the use of the int2vect type on indkey which is undocumented and does not seem to work with normal array tools such as ANY and cant be cast to int2[]. Please tell me there's an easier/better way to do this. SELECT attname FROM pg_index JOIN pg_class ON (indrelid = pg_class.oid) JOIN pg_attribute ON (attrelid = pg_class.oid) WHERE indisprimary IS TRUE AND (attnum = indkey[0] OR attnum = indkey[1] OR attnum = indkey[2]) AND relname = '$table'; Orion ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster select * from key_column_usage; -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] problem while adding a column
When ever trying to add a new column to the table get the following error ERROR: duplicate key violates unique constraint "pg_attribute_relid_attnum_index"While the new column that is added is not there in the table before.Doing a select * from table from the same tables also giving an error saying: ERROR: cache lookup failed for type 0Any idea what could be going wrong here??/Shoaib
Re: [GENERAL] locating a primary key
Orion <[EMAIL PROTECTED]> writes: > I'm trying to craft a query that will determine what column(s) belong > to the primary key for a given table. The information_schema.key_column_usage view might help. > This is all due to the use of > the int2vect type on indkey which is undocumented and does not seem to > work with normal array tools such as ANY and cant be cast to int2[]. As of 8.1 it can ... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Categories and Sub Categories (Nested)
You should look up the contrib module ltree which is made for this sort of thing. If you reinvent the wheel like this, you will be stuck with 2 levels. With ltree you can have as many as you need and add more at any time. It lets you query for ancestors and descendants of any item at any level. I highly recommend it. On 4/19/06, Martin Kuria <[EMAIL PROTECTED]> wrote: > Hi, > > I have a postgresql database Table Categories which has the structure like > this > > Cat_ID | Parent_ID | Name > > 1 | 0 | Automobiles > 2 | 0 | Beauty & Health > 3 | 1 | Bikes > 4 | 1 | Cars > 5 | 3 | Suzuki > 6 | 3 | Yamaha > 7 | 0 | Clothes > > According to the above Database data, Suzuki and Yamaha are the > Subcategories of Category Bikes and Bikes in turn is the Subcategory of > Automobiles. > > My question is what is the advantage of Creating NESTED Table over have a > table structure below which achieve the same goal: > > >>Category Table > > Cat_ID | Cat_Name > > 1 | Automobiles > 2 | Beauty & Health > 3 | Bikes > 4 | Cars > 7 | Clothes > > >>Subcategory Table > > Subcat_ID | Sub_Cat_ID | Sub_Name > -- > 10 | 3 | Suzuki > 11 | 3 | Yamaha > 12 | 1 | Bikes > 13 | 1 | Cars > > Since all display the data in them in a tree structure like below > > Automobiles > ---> Bikes > --> Suzuki > --> Yamaha > ---> Cars > Beauty & Health > Clothes > > Please advice should I Create a Nested Table Categories or > Should I create TWO Tables, Category Table and Subcategory Table? > > Thanks once again. > +-+ > | Martin W. Kuria (Mr.) [EMAIL PROTECTED] > ++ > > _ > Express yourself instantly with MSN Messenger! Download today it's FREE! > http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ > > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > ---(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
[GENERAL] recovery problem
I want to prepare a backup machine (for disaster management) by passing only the ARCHIVEDIR directory from another online machine - both the machines have Postgresql installed. We have sent PGLOG and ARCHIVEDIR. Previously it was working,but now,when I am issuing pg_ctl start,it is giving error like this... postmaster starting [EMAIL PROTECTED]:/usr/local/pgsql/data> LOG: database system was shut down at 2006-04-19 11:56:35 IST LOG: starting archive recovery LOG: restore_command = "cp /mnt/server/archivedir/%f %p" cp: cannot stat `/mnt/server/archivedir/0001.history': No such file or directory LOG: restored log file "0001" from archive LOG: record with zero length at 0/A70B28 LOG: invalid primary checkpoint record LOG: restored log file "0001" from archive LOG: record with zero length at 0/A70AEC LOG: invalid secondary checkpoint record PANIC: could not locate a valid checkpoint record LOG: startup process (PID 7849) was terminated by signal 6 LOG: aborting startup due to startup process failure Actually I can't take full data backup,everyday.For this reason,I was using pg_xlog directory. Please help me,in this stage. Thanking you Jayati Biswas ---(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: [GENERAL] Query to check existence of stored procedure?
Hi Jim, >> select count(*) from pg_proc where proname = 'your_function'; >> > don't forget about schema's, you will need to join with > pg_namespace.oid and pg_proc.pronamespace your answer looks a little bit cryptic for me being somebody who hasn't had to dive into the pg_... tables yet. :-) What do you exactly mean? Could you provide me a complete query for that job? Is there anything to consider, if the user performing this query is NOT the owner of the stored prodcedure? (but he needs this info as well!) Thank you in advance, Alexander. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] recovery problem
hello, I am trying to setup a "hot standby" on a second machine. I have created a "recovery.conf" file and started a restore with logs from the primary machine. everything was OK. now a have new transaction logs generated by the primary machine and I want to "play" them on the secondary one. I have stopped postgres, recreated "recovery.conf", started postgres and I get the following error: LOG: database system was shut down at 2005-05-23 05:19:34 PDT LOG: starting archive recovery LOG: restore_command = "/usr/cbmp/core/bin/restore_pg_tlog %f %p" LOG: restored log file "0001000800C4" from archive LOG: invalid resource manager ID 53 at 8/C4FFFEF8 LOG: invalid primary checkpoint record LOG: restored log file "0001000800C4" from archive LOG: invalid resource manager ID 52 at 8/C4FFFEBC LOG: invalid secondary checkpoint record PANIC: could not locate a valid checkpoint record LOG: startup process (PID 18297) was terminated by signal 6 LOG: aborting startup due to startup process failure LOG: logger shutting down what is the procedure for creating a "hot standby" (continuously feeding a series of WAL files created by the primary machine into the secondary one) ? ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] tomcat postgresql connectivity error
Hi, Im trying to run a web application from tomcat which connects to a database on postgresql 8.1.3 But when it tries to connect to a database.an E 101 Exception occured while retrieving results. is shown When I checked up the logs of tomcat.I could see.. SQLException [ERROR: No parser with id 17555]; nested exception is org.postgresql.util.PSQLException: ERROR: No parser with id 17555 Please advise Danish ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] consulta SELECT FOR UPDATE
Saludo.Tengo un problema con una consulta, como puedo insertar o actualizar un conjunto de datos en una tabla. Hice una consulta para comprarar dos tablas y el resultado los inserto en una nueva tabla, esa nueva tabla tengo los datos que se deben de actualizar en otra tabla, ya tengo la consulta que comprar e inserta en la nueva tabla pero me falta insertar esos datos en otra tabla o poder actualizarlos. lo que hice fue esto: /* con esta consulta compraro por medio de la version y sin tomar en cuenta el modulo sys, las dos tablas flmodules y flmodulescliente para obtener los modulos que debo de actualizar en el flfiles./*Select Into flfilescliente from (select * from flfiles where sha notnull and idmodulo <> '' and idmodulo In (select flmodules.idmodulo from flmodulescliente left join flmodules on flmodulescliente.idmodulo=flmodules.idmodulo where flmodules.idmodulo !='sys' and flmodules.version !=flmodulescliente.version)) as temp ;/* y con esta linea borro los modulos viejos en el flfiles*/ Delete from flfiles where idmodulo In (select distinct (idmodulo ) from flfilescliente);/*Solo falta insertar en la tabla flfiles los modulos nuevo, que suplen a los que borro en la linea anterior*/ ojala me puedan ayudar, gracias.
[GENERAL] deleting table content through command prompt
hi all, i am trying to create a database backup and restore windows batch file, which when run will do a database table backup and a corresponding table restore. now for table_usernames backup i run something like from the command prompt, pg_dump.exe -i -h localhost -p 5432 -U postgres -F t -v -f "C:\usernames.backup" -t table_usernames "loginDetails" now for db restore i run something like from the command prompt, pg_restore.exe -i -h localhost -p 5432 -U postgres -d "loginDetails" -a -t table_usernames -v "C:\usernames.backup" the problem is that after i do a backup i need to delete the table content and then do a restore. through pgadmin i would do DELETE FROM table_usernames. however i need to do this from the command prompt, like how i am doing above. it seems very obvious and simple, but i am unable to think of how i would do deletion of a table from the command prompt thanks for all feedback and help. ---(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
[GENERAL] postmaster services problem
Hi, I’m having problem starting the postmaster service at my office’s server now. Everything works fine for a year. But today morning, the I was unable to log into the database server. When I tried to start the postmaster service, it says, “The service started and stopped. Some services will stop automatically when there is no work to do”. Can you help me out here? Thanks! Regards, Ling Lii Shyan Test Engineer CentiFORCE Instruments Blk 55 Ayer Rajah Crescent #01-14 Ayer Rajah Industrial Estate Singapore 139949 Office Main: (65) 6866 1500 Testlab : (65) 6866 1510 DID : (65) 6866 1566 Fax : (65) 6778 3011 Email: [EMAIL PROTECTED] Website: www.centiforce.com
[GENERAL] Permission problem opening database
Hi, I have done some RTFMing and come up with little on this one. I have a database that I had setup in the past that I haven't opened for a while created under 8.1 running the native Win32 port. When I started the postgres service and tried connecting using PgAdmin III I got the following error: An Error has occurred. FATAL: could not open relation 16627/16628/2701: Permission Denied. Any ideas on how this can be fixed. Thanks. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] tomcat postgresql database connectivity error
Hi, Im trying to connect to a postgresql database from a web application running on tomcat... But the moment my web application connects an error is thrown up at the postgresql logs - ERROR: No parser with id 19369 The tomcat logs show the following error: -- com.rapidigm.bacs.db.BacsDBException: (executing StatementCallback): encountered SQLException [ERROR: No parser with id 19369]; nested exception is org.postgres ql.util.PSQLException: ERROR: No parser with id 19369 Thanks dsids -- View this message in context: http://www.nabble.com/tomcat-postgresql-database-connectivity-error-t1474001.html#a3986439 Sent from the PostgreSQL - general forum at Nabble.com. ---(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
[GENERAL] primary keys
I'm trying to craft a query that will determine what column(s) are the primary key for a given table. I have succeeded but the query is so ugly that it borders on silly and cannot work for an arbitrary number of tables since indkey is an int2vect and the ANY keyword does not work on it. Please tell me there's an easier way to do this. Here is the query for tablename $table. SELECT attname FROM pg_index JOIN pg_class ON (indrelid = pg_class.oid) JOIN pg_attribute ON (attrelid = pg_class.oid) WHERE indisprimary IS TRUE AND (attnum = indkey[0] OR attnum = indkey[1] OR attnum = indkey[2]) AND relname = '$table'; Orion ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] locating a primary key
I'm trying to craft a query that will determine what column(s) belong to the primary key for a given table. This seems like the kind of thing that would very common for people to try to do but my query was rather ugly and not flexible since it does not work for an arbitrary number of columns in the primary key. This is all due to the use of the int2vect type on indkey which is undocumented and does not seem to work with normal array tools such as ANY and cant be cast to int2[]. Please tell me there's an easier/better way to do this. SELECT attname FROM pg_index JOIN pg_class ON (indrelid = pg_class.oid) JOIN pg_attribute ON (attrelid = pg_class.oid) WHERE indisprimary IS TRUE AND (attnum = indkey[0] OR attnum = indkey[1] OR attnum = indkey[2]) AND relname = '$table'; Orion ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Calling the same function more than once with the same arguments
"Clodoaldo Pinto" <[EMAIL PROTECTED]> writes: > I'm building a function caller() in which a certain function called() > will be called many times in from clauses with the same arguments and > I'm wondering if is there a performance penalty for that or if the sql > engine is smart enough to call called() only once. No, it isn't. 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: [GENERAL] Calling the same function more than once with the same
Look into the immutable flag on function creation: http://www.postgresql.org/docs/8.1/interactive/xfunc-volatility.html On Wed, 19 Apr 2006, Clodoaldo Pinto wrote: I'm building a function caller() in which a certain function called() will be called many times in from clauses with the same arguments and I'm wondering if is there a performance penalty for that or if the sql engine is smart enough to call called() only once. I tried to substitute called() in the from clauses for a temporary table created first in caller() but it refuses to build caller() with the message "ERROR: relation "temp_table_name" does not exist". It does not exist in build time but it will exist in run time. Regards, Clodoaldo Pinto ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Calling the same function more than once with the same arguments
I'm building a function caller() in which a certain function called() will be called many times in from clauses with the same arguments and I'm wondering if is there a performance penalty for that or if the sql engine is smart enough to call called() only once. I tried to substitute called() in the from clauses for a temporary table created first in caller() but it refuses to build caller() with the message "ERROR: relation "temp_table_name" does not exist". It does not exist in build time but it will exist in run time. Regards, Clodoaldo Pinto ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] help plpgsql, cursors, fetch into
Yes, it sure looks like a bug, this function used to work before, besides I have another function that's pretty similar and it works. Anyway, I did an script creating new tables and functions, and the function worked. I also found that in the original function i had another sentence which is the one causing the problem...but only in the original function, in the new function I created using the script, it doesn't cuase problems. This is the original function: CREATE OR REPLACE FUNCTION Asignar_test(id_solicitud integer) RETURNS integer[] AS $$ DECLARE curs1 refcursor; taxi_id numeric; punto geometry; radio float; asignados integer[]; tipotax varchar(1); i integer; BEGIN SELECT posicion,tipo INTO punto,tipotax FROM solicitud_test WHERE id_solicitud=id; radio :=0.002; WHILE (SELECT id FROM taxi_test WHERE posicion && Expand(punto,0.1) AND Distance(punto,posicion) < radio AND tipo_taxi like tipotax AND estado = 'l' LIMIT 1) is null AND radio < 1 LOOP radio := radio + 0.002; END LOOP; curs1 := cercanos_test(punto, radio, tipotax); i:=0; LOOP FETCH curs1 INTO taxi_id; EXIT WHEN NOT FOUND; i:=i+1; asignados[i] := taxi_id; UPDATE taxi SET estado = 'p' where id = taxi_id; END LOOP; CLOSE curs1; RETURN asignados; END; $$ LANGUAGE 'plpgsql'; The function works again when I delete the UPDATE sentence. - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Mauricio Mantilla" <[EMAIL PROTECTED]> Cc: Sent: Wednesday, April 19, 2006 11:26 AM Subject: Re: [GENERAL] help plpgsql, cursors, fetch into Mauricio Mantilla <[EMAIL PROTECTED]> writes: Hi, I'm having a problem with FETCH INTO, but I can't figure out what it is. That kinda looks like a bug. Which PG version are you using exactly? Could you provide a self-contained test case (ie, a script to create the needed tables and sample data)? regards, tom lane -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.4/318 - Release Date: 18/04/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.4.4/318 - Release Date: 18/04/2006 ---(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
[GENERAL] plperl on AIX
I'm having trouble getting plperl to work on AIX 5.3.2. Postgresql Version: 8.1.1 Perl Version: 5.8.7 I've rebuilt perl as a shared library version and built that into postgre using --with-perl. The postgre build/install works fine, without errors. The plperl regression tests all fail, though. Here are the test results of the first regression test: -- -- checkpoint so that if we have a crash in the tests, replay of the -- just-completed CREATE DATABASE won't discard the core dump file -- checkpoint; -- -- Test result value processing -- CREATE OR REPLACE FUNCTION perl_int(int) RETURNS INTEGER AS $$ return undef; $$ LANGUAGE plperl; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. connection to server was lost I've tried this same thing manually with the same result. Does anyone have any suggestions on how to get this to work? Thanks
[GENERAL] Error while adding a column
Hi All,While trying to add a column to the table below. On DESC of the table
I do not see the column name, but on adding it, it complains of it
already being present, and on trying to drop it I get the "ERROR: cache
lookup failed for attribute 56 of relation 16681:" error.desc test_users;
Table "test_users"
Column | Type |
Modifiers
---+-+--
--
user_id | integer | not null default
nextval('test_users_seq'::regclass)
user_active | boolean | not null default
false
pgsql=> ALTER TABLE test_users ADD user_skin VARCHAR(255)
DEFAULT NULL;
ERROR: column "user_skin" of relation "test_users" already exists
pgsql=> ALTER TABLE test_users DROP user_skin;
ERROR: cache lookup failed for attribute 56 of relation 16681Any help will be appreciated /Shoaib
Re: [GENERAL] grant privileges across schemas
On Wed, Apr 19, 2006 at 09:29:50 -0300, Fernan Aguero <[EMAIL PROTECTED]> wrote: > > The following works, but I'm not sure about the consequences > of granting USAGE to a schema, as the documentation is > not clear, IMO : "For schemas, allows access to objects > contained in the specified schema (assuming that the > objects' own privilege requirements are also met). > Essentially this allows the grantee to "look up" objects > within the schema." > mydb=> GRANT USAGE ON SCHEMA myschema TO readonly; > GRANT > > After doing this, how do I give SELECT privilege to this > user for all tables of this schema? Granting access to a schema allows use of that schema. It does not grant access to objects contained in the schema. You will need to do a grant for each object in addition to what you are already doing. You will probably want to write a script or function to do it, as there isn't a built in command to do grants to multiple objects. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Translating sql error codes
"Andrus" <[EMAIL PROTECTED]> writes: > I want that end users see error messages descibed in > Appendix A. PostgreSQL Error Codes > in Estonian. > I can provide translations to those error codes. > How to make Postgres server to return my translated error messages ? Step right up and become a translator :-). There's some starting information here: http://developer.postgresql.org/docs/postgres/nls-translator.html and most of the work gets done here: http://pgfoundry.org/projects/pgtranslation/ regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Translating sql error codes
I want that end users see error messages descibed in Appendix A. PostgreSQL Error Codes in Estonian. I can provide translations to those error codes. How to make Postgres server to return my translated error messages ? Or is it more reasonable to implement this in my application by displaying text corresponding to sql error code ? In this case I can translate SQL error codes returned by ODBC driver also. Andrus. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] help plpgsql, cursors, fetch into
Mauricio Mantilla <[EMAIL PROTECTED]> writes: > Hi, I'm having a problem with FETCH INTO, but I can't figure out what it is. That kinda looks like a bug. Which PG version are you using exactly? Could you provide a self-contained test case (ie, a script to create the needed tables and sample data)? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How to implement a "subordinate database"?
On Wed, Apr 19, 2006 at 06:19:50 -0400, Kynn Jones <[EMAIL PROTECTED]> wrote: > I keep bumping against this situation: I have a main database A, and I want > to implement a database B, that is distinct from A, but subordinate to it, > meaning that it refers to data in A, but not vice versa. > > I don't simply want to add new tables to A to implement B, because this > unnecessarily clutters A's schema with tables that entirely extraneous to > it. > > Is there some other way? Have you looked at schemas? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] help plpgsql, cursors, fetch into
Hi, I'm having a problem with FETCH INTO, but I can't figure out what it
is.
I have this function which works out well:
CREATE OR REPLACE FUNCTION Cercanos(punto
geometry,radio float, tipo varchar(1)) RETURNS refcursor AS
$$DECLAREmycurs refcursor;BEGIN OPEN mycurs
FOR SELECT id FROM taxi WHERE posicion && Expand(punto,100) AND
Distance(punto,posicion) < radio AND tipo_taxi like
tipo; RETURN mycurs;END;$$ LANGUAGE
plpgsql;
Then I call it inside this other function, this is the one I'm having
trouble with.
CREATE OR REPLACE FUNCTION Asignar(id_solicitud integer) RETURNS integer[] AS
$$DECLAREcurs1 refcursor;taxi_id numeric;punto
geometry;radio float;asignados integer[];tipotax varchar(1);i
integer;BEGIN SELECT posicion,tipo INTO punto,tipotax
FROM solicitud WHERE id_solicitud=id; radio
:=0.002;WHILE (SELECT id FROM taxi WHERE estado
= 'x' AND Distance(punto,posicion) < radio LIMIT 1) is null AND radio < 1
LOOP radio := radio +
0.002;
END
LOOP;RAISE NOTICE 'radio %',
radio;curs1 := cercanos(punto, radio,
tipotax);i:=0;LOOP
FETCH curs1 INTO taxi_id;EXIT WHEN NOT
FOUND;
i:=i+1; asignados[i] :=
taxi_id;END LOOP;CLOSE
curs1; RETURN
asignados;END;$$ LANGUAGE 'plpgsql';
The function should return this array {1,3}
Instead it returns something like
{1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,3}
But when I run function Cercanos, I get 1 and 3 only one time, which is
what it should return cause column id from table taxi is unique.
I also tried to solve this by not letting the function return 1 more than
once in a row:
CREATE OR REPLACE FUNCTION Asignar(id_solicitud integer) RETURNS integer[] AS
$$DECLAREcurs1 refcursor;taxi_id numeric;punto
geometry;radio float;asignados integer[];tipotax varchar(1);i
integer;BEGIN SELECT posicion,tipo INTO punto,tipotax
FROM solicitud WHERE id_solicitud=id; radio
:=0.002;WHILE (SELECT id FROM taxi WHERE estado
= 'x' AND Distance(punto,posicion) < radio LIMIT 1) is null AND radio < 1
LOOP radio := radio +
0.002;
END
LOOP;RAISE NOTICE 'radio %',
radio;curs1 := cercanos(punto, radio,
tipotax);i:=0;LOOP
FETCH curs1 INTO taxi_id;EXIT WHEN NOT
FOUND;
CONTINUE WHEN asignados[i] =
taxi_id;
i:=i+1; asignados[i] :=
taxi_id;END LOOP;CLOSE
curs1; RETURN
asignados;END;$$ LANGUAGE 'plpgsql';
Now the function returns something like this:
{1,3,1,3,1,3,1,3,1,3,1,3,1,3,1,3,1,3}
Does anyone know what micht be
wrong?
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.4.4/318 - Release Date: 18/04/2006
---(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
Re: [GENERAL] page is uninitialized?
Hi Tom, I had a problem recently with an index on my category_product table. A few times when I was vacuuming that table, I had forgotten to reset our statement_timeout setting from 2 minutes to 0, so a few times the statement was cancelled pre-maturely due to the timeout setting. Perhaps that caused the problem. I deleted the index and was able to vacuum the table correctly. Prior to dropping the index, the database did crash on me before I figured out what the problem was. As a side note, we do have autovacuum turned on, but it doesn't seem to kick in for tables that have a very large number of frequent updates. The category_product table gets updated once every half an hour. Vacuuming often finds over 300,000 row versions to delete. So that's why I've been going in and running vacuum analyze manually. I know I should probably add it to the end of the task that updates the table. Thanks, Brendan Duddridge | CTO | 403-277-5591 x24 | [EMAIL PROTECTED] ClickSpace Interactive Inc. Suite L100, 239 - 10th Ave. SE Calgary, AB T2G 0V9 http://www.clickspace.com On Apr 19, 2006, at 9:02 AM, Tom Lane wrote: Brendan Duddridge <[EMAIL PROTECTED]> writes: I was doing a vacuum analyze verbose on my database today and I noticed the following message printed out: WARNING: relation "category_product" page 128979 is uninitialized --- fixing WARNING: relation "category_product" page 128980 is uninitialized --- fixing ... There are some situations in which this is expected, which is why VACUUM deals with it, but none of them are very good: they involve backends adding a page to a table and then failing before they can write a WAL record about initializing the page. Have you had any crashes recently? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Categories and Sub Categories (Nested)
Martin Kuria wrote: Hi, I have a postgresql database Table Categories which has the structure like this Cat_ID | Parent_ID | Name 1 | 0 | Automobiles 2 | 0 | Beauty & Health 3 | 1 | Bikes 4 | 1 | Cars 5 | 3 | Suzuki 6 | 3 | Yamaha 7 | 0 | Clothes This has one big drawback, you'll need a query for (almost) every record you want to select. Some databases have "solutions" for this, varying in usability (though I really have only used one such database so far). My question is what is the advantage of Creating NESTED Table over have a table structure below which achieve the same goal: In relational databases? None, AFAIK. Drawbacks seems more like it. Fabian Pascal describes a method in one of his books that works by exploding the tree. Ideally this should be done "automagically" by the database; he suggests an EXPLODE function that takes a table as argument, but I'm quite confident the same can be achieved with a few triggers. It works almost as what you describe in your second solution. Category Table Cat_ID | Cat_Name 1 | Automobiles 2 | Beauty & Health 3 | Bikes 4 | Cars 7 | Clothes Subcategory Table You could use a relation-table here, and put the subcategories in the category table. That table would look like: Category_Category table Cat_Id | Parent_Id | Depth - 3 | 1 | 1 4 | 1 | 1 5 | 3 | 1 5 | 1 | 2 6 | 3 | 1 6 | 1 | 2 Note that all descendents have relations to all their ancestors. That's what makes this thing work. Automatically keeping track of those can be done with triggers on insert, update and delete. Now you can query all children of automobiles at once: select category.*, categore_category.parent_id, category_category.depth from category inner join category_category on (cat_id = parent_id) where parent_id = 1; Cat_Id | Cat_Name | Depth --- 3 | Bikes | 1 4 | Cars | 1 5 | Suzuki | 2 6 | Yamaha | 2 You can add more columns with specific data that can be used to sort the tree, for example by keeping a reference to the direct parent. Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] page is uninitialized?
Brendan Duddridge <[EMAIL PROTECTED]> writes: > I was doing a vacuum analyze verbose on my database today and I > noticed the following message printed out: > WARNING: relation "category_product" page 128979 is uninitialized > --- fixing > WARNING: relation "category_product" page 128980 is uninitialized > --- fixing > ... There are some situations in which this is expected, which is why VACUUM deals with it, but none of them are very good: they involve backends adding a page to a table and then failing before they can write a WAL record about initializing the page. Have you had any crashes recently? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] page is uninitialized?
Brendan Duddridge <[EMAIL PROTECTED]> writes: > Shortly after posting this message, I received the following error also: > PANIC: right sibling is not next child in > "category_product__is_active_idx" Last week's report of that same error was traced to running with full_page_writes turned off; are you doing that? If so, don't. http://archives.postgresql.org/pgsql-bugs/2006-04/msg00135.php http://archives.postgresql.org/pgsql-announce/2006-04/msg2.php regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Bulk inserts within a Perl script?
Thank you all for cluing me in on pg_putline and pg_endcopy. Much cleaner than my kluge. kj
[GENERAL] some error messages in postgeslog
I use PostgreSQL 8.0.0 it seems that the disk was close to full, i executed a program (in C++) which opens a connection to Postgres using PQConnectdb. and then it reads from a particluar table, and simply displays the values on the console. after doing that it will close the connection using PQfinish and thats when these error messages come <2006-04-05 17:10:47 CDT%idle>LOG: disconnection: session time: 0:00:00.00 user=sdc database=dbexpress host=[local] port=<2006-04-05 17:10:47 CDT%idle>LOG: disconnection: session time: 7:41:26.70 user=sdc database=dbexpress host=[local] port=<2006-04-05 17:10:47 CDT%>ERROR: could not access status of transaction 0<2006-04-05 17:10:47 CDT%>DETAIL: could not write to file "/export/home1/sdc_image_pool/dbx/pg_clog/0001" at offset 40960:No space left on device<2006-04-05 17:10:48 CDT%>LOG: could not close temporary statistics file "/export/home1/sdc_image_pool/dbx/global/pgstat.tmp.24783": No space left on device<2006-04-05 17:10:49 CDT%>ERROR: could not access status of transaction 0<2006-04-05 17:10:49 CDT%>DETAIL: could not write to file "/export/home1/sdc_image_pool/dbx/pg_clog/0001" at offset 40960:No space left on device<2006-04-05 17:10:50 CDT%>ERROR: could not access status of transaction 0<2006-04-05 17:10:50 CDT%>DETAIL: could not write to file "/export/home1/sdc_image_pool/dbx/pg_clog/0001" at offset 40960:No space left on device is it true that for starting a connection to postgres read a table and then closing connection disk space will be needed. Is it because of this reason these error messages are coming? i have another question i have also noticed this error message in the postgres log, what are its implications and what happened that this error message came : 2006-04-05 09:22:08 CDT%>LOG: received fast shutdown request<2006-04-05 09:22:08 CDT%>LOG: aborting any active transactions<2006-04-05 09:22:08 CDT%>LOG: checkpoints are occurring too frequently (16 seconds apart)<2006-04-05 09:22:08 CDT%>HINT: Consider increasing the configuration parameter "checkpoint_segments". Thanks, regards Surabhi
[GENERAL] grant privileges across schemas
Hi, I'm using a database that is splitted into a number of schemas. In my local installation I'd like to have 3 users: a dba (ALL privileges), a user with read-write (INSERT/UPDATE/DELETE on all tables and views and SELECT on all sequences) privileges and a user with read-only (SELECT on all tables and views) privileges. Then individual db users would be given the read-only or read-write roles as appropriate. Right now I'd like to have these set across all schemas (later, for finer control we would turn to specific control for individual schemas). The problem is that I only seem to be able to set the privs for the dba (ALL) since this is done at the database level: GRANT ALL ON DATABASE mydb TO dba; However the following doesn't work: mydb=> GRANT SELECT ON DATABASE mydb TO readonly; ERROR: invalid privilege type SELECT for database mydb=> GRANT SELECT ON SCHEMA myschema TO readonly; ERROR: invalid privilege type SELECT for schema mydb=> GRANT SELECT ON TABLE myschema.* TO readonly; ERROR: syntax error at or near "TO" at character 34 LINE 1: GRANT SELECT ON TABLE myschema.* TO readonly; ^ The following works, but I'm not sure about the consequences of granting USAGE to a schema, as the documentation is not clear, IMO : "For schemas, allows access to objects contained in the specified schema (assuming that the objects' own privilege requirements are also met). Essentially this allows the grantee to "look up" objects within the schema." mydb=> GRANT USAGE ON SCHEMA myschema TO readonly; GRANT After doing this, how do I give SELECT privilege to this user for all tables of this schema? Thanks in advance, Fernan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] strange query filter problems
On Wed, Apr 19, 2006 at 01:53:46PM +0200, Jonas Henriksen wrote: > Yes, explain analyze looks like this: Well, incorrect statistics are definitly the culprit, look: > " -> Index Scan using sskjema_pkey on sskjema s (cost=0.00..3868.95 > rows=9738 width=157) (actual time=104.465..208.185 rows=14417 loops=1)" 50% off, not bad. > "-> Index Scan using speciesix on tskjema t (cost=0.00..6.01 rows=1 > width=75) (actual time=0.140..24.594 rows=1703 loops=1)" > " Index Cond: ((species ~>=~ 'TAGGMAKRELL'::bpchar) AND (species > ~<~ 'TAGGMAKRELM'::bpchar))" > " Filter: (species ~~ 'TAGGMAKRELL%'::text)" Youch, 170200% percent off, which explains why it gets wildly bad timings. Can you increase the statistics on the species column? Check the docs for how. -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] strange query filter problems
Yes, explain analyze looks like this:
EXPLAIN
ANALYZE
SELECT
*
FROM sskjema s inner join tskjema t using(sskjema_pkey) where
t.species::char(12) like 'TAGGMAKRELL%'::char(12)
and s.date >=20050101
"Merge Join (cost=6.02..3899.33 rows=1 width=228) (actual
time=150.274..331.782 rows=190 loops=1)"
" Merge Cond: ("outer".sskjema_pkey = "inner".sskjema_pkey)"
" -> Index Scan using sskjema_pkey on sskjema s (cost=0.00..3868.95
rows=9738 width=157) (actual time=104.465..208.185 rows=14417
loops=1)"
"Filter: (date >= 20050101)"
" -> Sort (cost=6.02..6.03 rows=1 width=75) (actual
time=34.693..40.956 rows=1703 loops=1)"
"Sort Key: t.sskjema_pkey"
"-> Index Scan using speciesix on tskjema t (cost=0.00..6.01
rows=1 width=75) (actual time=0.140..24.594 rows=1703 loops=1)"
" Index Cond: ((species ~>=~ 'TAGGMAKRELL'::bpchar) AND
(species ~<~ 'TAGGMAKRELM'::bpchar))"
" Filter: (species ~~ 'TAGGMAKRELL%'::text)"
"Total runtime: 333.158 ms"
EXPLAIN
ANALYZE
SELECT
*
FROM sskjema s inner join tskjema t using(sskjema_pkey) where
t.species::char(12) like 'TAGGMAKRELL%'::char(12)
and s.date >=20050101
and s.date <=20051231
"Nested Loop (cost=0.00..4049.18 rows=1 width=228) (actual
time=1260.988..252110.934 rows=150 loops=1)"
" Join Filter: ("inner".sskjema_pkey = "outer".sskjema_pkey)"
" -> Index Scan using speciesix on tskjema t (cost=0.00..6.01
rows=1 width=75) (actual time=0.256..50.875 rows=1703 loops=1)"
"Index Cond: ((species ~>=~ 'TAGGMAKRELL'::bpchar) AND
(species ~<~ 'TAGGMAKRELM'::bpchar))"
"Filter: (species ~~ 'TAGGMAKRELL%'::text)"
" -> Index Scan using dateix on sskjema s (cost=0.00..4025.13
rows=1443 width=157) (actual time=0.026..76.451 rows=14340
loops=1703)"
"Index Cond: ((date >= 20050101) AND (date <= 20051231))"
"Total runtime: 252111.940 ms"
Jonas:))
On 4/19/06, Martijn van Oosterhout wrote:
> On Wed, Apr 19, 2006 at 01:27:45PM +0200, Jonas Henriksen wrote:
> > Hi,
> > I have a problem with a slow query. (I have run vacuum full analyze!)
> > It seems that the query below works OK because the query planner
> > filters on the date first. It takes about 0.3 sec:
>
> Can we see an EXPLAIN ANALYZE of the two queries, so we can see what's
> actually happening? My guess is that in the second case, the index scan
> on sskjema matches more rows than it expects...
>
> Have a nice day,
> --
> Martijn van Oosterhout http://svana.org/kleptog/
> > From each according to his ability. To each according to his ability to
> > litigate.
>
>
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.1 (GNU/Linux)
>
> iD8DBQFERiCRIB7bNG8LQkwRAq+MAJ4rhGLzU1sYszrT7DUWzPH2+bjVzwCfS1ne
> 5y7A3WhI4PqfDaulFB2hPvc=
> =N5EG
> -END PGP SIGNATURE-
>
>
>
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] strange query filter problems
On Wed, Apr 19, 2006 at 01:27:45PM +0200, Jonas Henriksen wrote: > Hi, > I have a problem with a slow query. (I have run vacuum full analyze!) > It seems that the query below works OK because the query planner > filters on the date first. It takes about 0.3 sec: Can we see an EXPLAIN ANALYZE of the two queries, so we can see what's actually happening? My guess is that in the second case, the index scan on sskjema matches more rows than it expects... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] How to implement a "subordinate database"?
On 4/19/06, Kynn Jones <[EMAIL PROTECTED]> wrote: > > I keep bumping against this situation: I have a main database A, and I want > to implement a database B, that is distinct from A, but subordinate to it, > meaning that it refers to data in A, but not vice versa. > > I don't simply want to add new tables to A to implement B, because this > unnecessarily clutters A's schema with tables that entirely extraneous to > it. Hmm. Postgres supports table inheritance, but I don't think it supports schema or database inheritance in the way you want it to. http://www.postgresql.org/docs/8.1/interactive/tutorial-inheritance.html http://www.postgresql.org/docs/8.1/interactive/ddl-inherit.html -- Postgresql & php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] strange query filter problems
Hi,
I have a problem with a slow query. (I have run vacuum full analyze!)
It seems that the query below works OK because the query planner
filters on the date first. It takes about 0.3 sec:
EXPLAIN
SELECT
*
FROM sskjema s INNER JOIN tskjema t USING(sskjema_pkey)
WHERE t.species::char(12) LIKE 'TAGGMAKRELL%'::char(12)
AND s.date >=20050101
"Merge Join (cost=6.02..3899.33 rows=1 width=228)"
" Merge Cond: ("outer".sskjema_pkey = "inner".sskjema_pkey)"
" -> Index Scan using sskjema_pkey on sskjema s (cost=0.00..3868.95
rows=9738 width=157)"
"Filter: (date >= 20050101)"
" -> Sort (cost=6.02..6.03 rows=1 width=75)"
"Sort Key: t.sskjema_pkey"
"-> Index Scan using speciesix on tskjema t (cost=0.00..6.01
rows=1 width=75)"
" Index Cond: ((species ~>=~ 'TAGGMAKRELL'::bpchar) AND
(species ~<~ 'TAGGMAKRELM'::bpchar))"
" Filter: (species ~~ 'TAGGMAKRELL%'::text)"
However, if I add a second date-condition to further cut the
result-size, the species-column is used as the first filter, drasticly
increasing the query-time to more than a minute:
EXPLAIN
SELECT
*
FROM sskjema s INNER JOIN tskjema t USING(sskjema_pkey)
WHERE t.species::char(12) LIKE 'TAGGMAKRELL%'::char(12)
AND s.date >=20050101
AND s.date <=20051231
"Nested Loop (cost=0.00..4049.18 rows=1 width=228)"
" Join Filter: ("inner".sskjema_pkey = "outer".sskjema_pkey)"
" -> Index Scan using speciesix on tskjema t (cost=0.00..6.01
rows=1 width=75)"
"Index Cond: ((species ~>=~ 'TAGGMAKRELL'::bpchar) AND
(species ~<~ 'TAGGMAKRELM'::bpchar))"
"Filter: (species ~~ 'TAGGMAKRELL%'::text)"
" -> Index Scan using dateix on sskjema s (cost=0.00..4025.13
rows=1443 width=157)"
"Index Cond: ((date >= 20050101) AND (date <= 20051231))"
Any suggestions how to get the planner do the query in the best way?
regards Jonas:))
---(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
[GENERAL] How to implement a "subordinate database"?
I keep bumping against this situation: I have a main database A, and I want to implement a database B, that is distinct from A, but subordinate to it, meaning that it refers to data in A, but not vice versa. I don't simply want to add new tables to A to implement B, because this unnecessarily clutters A's schema with tables that entirely extraneous to it. Is there some other way? Thanks! kj
[GENERAL] code to cancel a running query, worked thread
i have the following peice of code, which is meant for
cancelling queries in between
import java.sql.*;
public class QueryExecutor implements Runnable {
/** * @param args */ private Thread
worker; private Params params; private Results
results; private volatile boolean cancelRequest; private
volatile boolean closeRequest;
private class Params { public Statement
statement; public String
query; public boolean
pending; } private class Results {
public ResultSet rs; public SQLException
exception; public boolean serviced; }
public QueryExecutor() { params = new
Params(); results = new Results(); worker = new
Thread(this); worker.start(); } /** *
Executes an SQL query. * The method can be interrupted by another
thread at any moment. * @return ResultSet if
execution successful * @exception SQLException if a database error
occurs * @exception InterruptedException if interrupted by another
thread **/
public synchronized ResultSet executeQuery(Statement statement,
String query) throws
SQLException, InterruptedException { //Set query
parameters synchronized(params)
{ params.statement =
statement; params.query =
query; params.pending =
true;
params.notify(); }
synchronized(results)
{ try
{
//Wait for the query to
complete
while(!results.serviced)
{
results.wait();
System.out.println("waiting for
results");
}
System.out.println("obtained
results");
if (results.exception != null)
{
throw
results.exception;
} } catch
(InterruptedException e) {
System.out.println("Cancelling");
cancel();
//throw e; } finally
{
results.serviced = false;
} return
results.rs; } }
private void cancel() { cancelRequest =
true; try
{
params.statement.cancel();
synchronized(results)
{
while(!results.serviced)
{
results.wait();
}
} } catch (SQLException e)
{
return; } catch (InterruptedException e)
{
return; } finally
{ cancelRequest =
false; } }
public void close() { closeRequest =
true; if (params.statement != null)
{ cancel(); } worker.interrupt(); try
{ worker.join(); } catch
(InterruptedException e) {} }
// The implementation of the Runnable interface (for the worker
thread) public void run() { ResultSet rs =
null; SQLException ex =
null; while(!closeRequest)
{ synchronized(params)
{
try
{
//Wait for query
parameters
while(!params.pending)
{
params.wait();
}
params.pending =
false;
} catch (InterruptedException e)
{
if (closeRequest)
{
return;
}
}
//Execute
query
try
{
rs =
params.statement.executeQuery(
params.query);
System.out.println(params.query);
} catch (SQLException e)
{
if (!cancelRequest)
{
ex =
e;
}
} }
//Set query
results
synchronized(results)
{
results.rs =
rs;
results.exception =
ex;
results.serviced =
true;
results.notify();
} } }}
in the front end i select a particular item , whcih will perform
executeQuery,
when i select another item, the prev query gets cancelled and new one is
executed.
however if i change my selection very fast, it seems that the worker thread
stops responding.
and then even if i click on other items, no query gets submitted.
Is the above peice of code fine, does the problem lie in the code which
calls the executeQuery of QueryExecutor?
Thanks,
regards
Surabhi
Re: [GENERAL] page is uninitialized?
It turns out I had a bad index on my category_product table. I dropped the index, then reindexed the whole table, then I wasable to successfully vacuum analyze.Thanks, Brendan Duddridge | CTO | 403-277-5591 x24 | [EMAIL PROTECTED] ClickSpace Interactive Inc. Suite L100, 239 - 10th Ave. SE Calgary, AB T2G 0V9 http://www.clickspace.com On Apr 19, 2006, at 2:23 AM, Brendan Duddridge wrote:Hi,Shortly after posting this message, I received the following error also:PANIC: right sibling is not next child in "category_product__is_active_idx"server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.The connection to the server was lost. Attempting reset: Failed.Are the two related in anyway? I'm starting to feel like my database is falling apart at the seams.Thanks, Brendan Duddridge | CTO | 403-277-5591 x24 | [EMAIL PROTECTED] ClickSpace Interactive Inc. Suite L100, 239 - 10th Ave. SE Calgary, AB T2G 0V9 http://www.clickspace.com On Apr 19, 2006, at 2:16 AM, Brendan Duddridge wrote:Hi,I was doing a vacuum analyze verbose on my database today and I noticed the following message printed out:WARNING: relation "category_product" page 128979 is uninitialized --- fixingWARNING: relation "category_product" page 128980 is uninitialized --- fixingWARNING: relation "category_product" page 128981 is uninitialized --- fixingWARNING: relation "category_product" page 128982 is uninitialized --- fixingCould that be due to updates occurring at the same time as vacuuming? There was a heavy process runningat the time that was updating the category_product table.Thanks, Brendan Duddridge | CTO | 403-277-5591 x24 | [EMAIL PROTECTED] ClickSpace Interactive Inc. Suite L100, 239 - 10th Ave. SE Calgary, AB T2G 0V9 http://www.clickspace.com
Re: [GENERAL] page is uninitialized?
Hi,Shortly after posting this message, I received the following error also:PANIC: right sibling is not next child in "category_product__is_active_idx"server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.The connection to the server was lost. Attempting reset: Failed.Are the two related in anyway? I'm starting to feel like my database is falling apart at the seams.Thanks, Brendan Duddridge | CTO | 403-277-5591 x24 | [EMAIL PROTECTED] ClickSpace Interactive Inc. Suite L100, 239 - 10th Ave. SE Calgary, AB T2G 0V9 http://www.clickspace.com On Apr 19, 2006, at 2:16 AM, Brendan Duddridge wrote:Hi,I was doing a vacuum analyze verbose on my database today and I noticed the following message printed out:WARNING: relation "category_product" page 128979 is uninitialized --- fixingWARNING: relation "category_product" page 128980 is uninitialized --- fixingWARNING: relation "category_product" page 128981 is uninitialized --- fixingWARNING: relation "category_product" page 128982 is uninitialized --- fixingCould that be due to updates occurring at the same time as vacuuming? There was a heavy process runningat the time that was updating the category_product table.Thanks, Brendan Duddridge | CTO | 403-277-5591 x24 | [EMAIL PROTECTED] ClickSpace Interactive Inc. Suite L100, 239 - 10th Ave. SE Calgary, AB T2G 0V9 http://www.clickspace.com
[GENERAL] page is uninitialized?
Hi,I was doing a vacuum analyze verbose on my database today and I noticed the following message printed out:WARNING: relation "category_product" page 128979 is uninitialized --- fixingWARNING: relation "category_product" page 128980 is uninitialized --- fixingWARNING: relation "category_product" page 128981 is uninitialized --- fixingWARNING: relation "category_product" page 128982 is uninitialized --- fixingCould that be due to updates occurring at the same time as vacuuming? There was a heavy process runningat the time that was updating the category_product table.Thanks, Brendan Duddridge | CTO | 403-277-5591 x24 | [EMAIL PROTECTED] ClickSpace Interactive Inc. Suite L100, 239 - 10th Ave. SE Calgary, AB T2G 0V9 http://www.clickspace.com
Re: [GENERAL] catch SQLException, error code for Foeign key violation,
On Wed, 19 Apr 2006, surabhi.ahuja wrote: what is the way to capture such exception in Cpp, are there any examples available for this? right now in cpp, i do this rStatus = PQresultStatus(result); but what is the specific error code, how to get that, See PQresultErrorField http://www.postgresql.org/docs/8.1/static/libpq-exec.html Kris Jurka ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
