[GENERAL] (Never?) Kill Postmaster?
Hi there, I read dozens of times the TIP 2: Don't 'kill -9' the postmaster... Now, what am I supposed to do if I launched a query which takes ages, and which I want to interrupt? Thanks for any advice, Stef Stefan Schwarzer Lean Back and Relax - Enjoy some Nature Photography: http://photoblog.la-famille-schwarzer.de Appetite for Global Data? UNEP GEO Data Portal: http://geodata.grid.unep.ch
Re: [GENERAL] (Never?) Kill Postmaster?
2007/10/24, Stefan Schwarzer [EMAIL PROTECTED]: Hi there, I read dozens of times the TIP 2: Don't 'kill -9' the postmaster... Now, what am I supposed to do if I launched a query which takes ages, and which I want to interrupt? Thanks for any advice, Stef Hello you have to use more gently way select pg_cancel_backend() http://www.postgresql.org/docs/current/interactive/functions-admin.html Pavel Stefan Schwarzer Lean Back and Relax - Enjoy some Nature Photography: http://photoblog.la-famille-schwarzer.de Appetite for Global Data? UNEP GEO Data Portal: http://geodata.grid.unep.ch ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] (Never?) Kill Postmaster?
On Wed, 2007-10-24 at 07:57 +0200, Stefan Schwarzer wrote:
Hi there,
I read dozens of times the TIP 2: Don't 'kill -9' the postmaster...
Now, what am I supposed to do if I launched a query which takes ages,
and which I want to interrupt?
look for the query's procpid and then issue a select
pg_cancel_backend('the_id')
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] function result cache for pl/pgsql
2007/10/24, Peter Manchev [EMAIL PROTECTED]: I was thinking about pl/pgsql and comparing it with pl/sql function result cache, featured in Oracle 11g - see http://www.oracle.com/technology/oramag/oracle/07-sep/o57plsql.html Is it possible to get pl/pgsql function result cache functionality into PostgreSQL? plpgsql doesn't allow access to shared memory now. You can use plper for caching some resuts. I am not sure if result cache is generally accaptable technique, but I thing, I can write similar functionality for orafce if you need anything hurry, use plperl http://www.postgresql.org/docs/8.2/interactive/plperl-global.html and $_SHARED array. Regards Pavel Stehule ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] (Never?) Kill Postmaster?
Pavel Stehule wrote: 2007/10/24, Stefan Schwarzer [EMAIL PROTECTED]: I read dozens of times the TIP 2: Don't 'kill -9' the postmaster... Now, what am I supposed to do if I launched a query which takes ages, and which I want to interrupt? you have to use more gently way select pg_cancel_backend() http://www.postgresql.org/docs/current/interactive/functions-admin.html The problem is that pg_cancel_backend() is somewhat unreliable at cancelling wayward queries. You can try other options for kill though, other than -9, which is kind of a last resort. -- Tommy Gildseth ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] (Never?) Kill Postmaster?
Ow Mun Heng wrote:
look for the query's procpid and then issue a select
pg_cancel_backend('the_id')
Does it do any harm if I kill (either with signal 9 or signal 15) the
single backend process (and not the postmaster)?
Regards,
Christian
--
Deriva GmbH Tel.: +49 551 489500-42
Financial IT and Consulting Fax: +49 551 489500-91
Hans-Böckler-Straße 2 http://www.deriva.de
D-37079 Göttingen
Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Using Postgres as a embedded / SQL Lite database on Windows
Craig Hawkes, 24.10.2007 05:14: - running as a service What happens in the unlikely event that they already have a version of Postgres installed? As far as I know you can happily install different versions in parallel. You just need to make sure the service name is unique (e.g. by using your application's name), that you initialize the PGDATA directory somewhere that is app specific and finally make sure you are not using the standard port for the communication. I don't think an already installed Postgres would be harmed then. Thomas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] can't compile Pl/Java
Roger Mason wrote: I need to install Pl/java in my 8.0.12 PostgreSQL. According to the documentation on the PL/java wiki I must compile from source. When I run make the following errors occur: [...] i686-pc-linux-gnu-gcc -c -O2 -march=pentium-m -pipe -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fpic -I. -I/usr/include/postgresql/pgsql/server -I/usr/include/postgresql/pgsql/internal -D_GNU_SOURCE -DPKGLIBDIR=\/usr/lib/postgresql\ -I/usr/local/share/pljava-1.3.0/src/C/include -I/usr/local/share/pljava-1.3.0/build/jni -DPGSQL_MAJOR_VER=8 -DPGSQL_MINOR_VER=0 -DPGSQL_PATCH_VER=12 -I/home/rmason/.gentoo/java-config-2/current-user-vm/include -I/home/rmason/.gentoo/java-config-2/current-user-vm/include/linux /usr/local/share/pljava-1.3.0/src/C/pljava/type/JavaWrapper.c -o type/JavaWrapper.o In file included from /usr/local/share/pljava-1.3.0/src/C/include/pljava/JNICalls.h:12, from /usr/local/share/pljava-1.3.0/src/C/include/pljava/PgObject.h:12, from /usr/local/share/pljava-1.3.0/src/C/include/pljava/PgObject_priv.h:12, from /usr/local/share/pljava-1.3.0/src/C/include/pljava/type/Type_priv.h:12, from /usr/local/share/pljava-1.3.0/src/C/pljava/type/JavaWrapper.c:10: /usr/local/share/pljava-1.3.0/src/C/include/pljava/pljava.h:29:22: error: postgres.h: No such file or directory [...] Then follow many other errors. The first error (not finding postgres.h) is puzzling because gcc is looking in the right place: /usr/include/postgresql/server/postgres.h Is it? I see no -I/usr/include/postgresql/server in the gcc command line. Please check again! Yours, Laurenz Albe ---(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] Start DB giving fatal message.(linux)
Farhan Khan wrote: Because of some OS failure I have to reinstall linux and postgres and now database start gives me following error message. Any pointers will be apprciated ... [EMAIL PROTECTED]:postgresql-8.2.5$ /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data LOG: database system was shut down at 2007-10-23 16:30:49 PDT LOG: checkpoint record is at 0/42C408 LOG: redo record is at 0/42C408; undo record is at 0/0; shutdown TRUE LOG: next transaction ID: 0/593; next OID: 10820 LOG: next MultiXactId: 1; next MultiXactOffset: 0 LOG: database system is ready FATAL: database db_test1 does not exist FATAL: database db_test1 does not exist FATAL: database db_test1 does not exist FATAL: database db_test1 does not exist db_test1 was one the databases I was using before reinstalling everything. Note during re-installation, I have formatted the harddrive which has erased the /usr/local/pgsql/bin/postgres folder ... but there were some stuff that persisted as it was on NFS (this includes the installer for postgres) ... BTW, where does postgres saves all db related information which is used at db start time. Is there a utility to edit this information?? Your cluster data are stored in /usr/local/pgsql/data. That is also where your databases live unless you defined them in a tablespace somewhere else. Does the server start so that you can connect to database postgres? The correct thing to do for you would be to restore from a backup, but I guess you know that. Yours, Laurenz Albe ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] (Never?) Kill Postmaster?
Hi, First you need to identify the correct postgresql process. Postgresql spawns an individual server process for each database connection. They look something like this: postgres 27296 7089 9 08:00 ?00:05:52 postgres: username databasename [local] idle If a query was running then it would say 'SELECT' instead of 'idle'. You can send a SIGINT (ie, -2) to that process to cancel a query, eg kill -2 27296 In most systems SIGINT is the default for kill so you could just do kill pid. The tip is ''kill -9' the postmaster', which has two important differences to the scenario I just described: 1) kill -9 means the OS kills the process without allowing it to clean up after itself 2) The postmaster is the master postgresql backend process. If you want to kill a single query you would not want to kill that. Regards // Mike From: Stefan Schwarzer [mailto:[EMAIL PROTECTED] Sent: Wednesday, 24 October 2007 3:58 PM To: pgsql-general@postgresql.org Subject: (Never?) Kill Postmaster? Hi there, I read dozens of times the TIP 2: Don't 'kill -9' the postmaster... Now, what am I supposed to do if I launched a query which takes ages, and which I want to interrupt? Thanks for any advice, Stef Stefan Schwarzer Lean Back and Relax - Enjoy some Nature Photography: http://photoblog.la-famille-schwarzer.de Appetite for Global Data? UNEP GEO Data Portal: http://geodata.grid.unep.ch
Re: [GENERAL] (Never?) Kill Postmaster?
2007/10/24, Christian Schröder [EMAIL PROTECTED]:
Ow Mun Heng wrote:
look for the query's procpid and then issue a select
pg_cancel_backend('the_id')
Does it do any harm if I kill (either with signal 9 or signal 15) the
single backend process (and not the postmaster)?
shared memory can be broken, and probably somewhere can stay some
garbage. It's depend on query.
Pavel
Regards,
Christian
--
Deriva GmbH Tel.: +49 551 489500-42
Financial IT and Consulting Fax: +49 551 489500-91
Hans-Böckler-Straße 2 http://www.deriva.de
D-37079 Göttingen
Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] deadlock detected, only selects (not select-for-update)
hi, i got the following error-message: ERROR: deadlock detected DETAIL: Process 32618 waits for ShareLock on transaction 1137032034; blocked by process 16136. Process 16136 waits for ShareLock on transaction 1137045910; blocked by process 32618. (postgres 7.4 here) i checked the mailing-list-archives, and by such problems people always have inserts/updates or at least select-for-updates. the strange thing is, that the code that apparently produces this error message does not have any of these.. it's mostly like: (please note, that i cannot reproduce this problem, and also, i am not logging all the sql commands. so i cannot be 100% sure that it was caused by the following code, but from other tests, these are the commands that are executed when the error happens) BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; now a lot of selects, but nothing complicated, all of the form of SELECT x FROM y WHERE a=b, with some simple joins, and SELECT DISTINCT and ORDER BY. END; can any of these commands cause the mentioned deadlock? (of course, there are other processes that write to the database at this time, but the one that reported the deadlock was this one) as i said, i cannot reproduce it, so it's still possible, that when the error happened, for some strange reason, also some other sql-commands were executed, but i do not think so. thanks, gabor ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Selecting K random rows - efficiently!
It has been suggested [1] that a good way to select K random rows efficiently from a table is to 1) add a new column, random_number, to the table and initialize it with random() 2) perform the following query: SELECT * FROM mydata WHERE random_number = (SELECT RANDOM() OFFSET 0) ORDER BY random_number ASC LIMIT K Here K is the number of random rows to pick. E.g. 100. The benefit in this solution is that the random_number column can be indexed, allowing the query to be performed using a simple and fast index scan. However, there is a couple of major drawbacks in this method: 1) The smaller random_number is, the less likely is it that it will be picked when using this method. Example: A random_number close to zero will only have a very small probability to be selected. The solution is to reassign random_number every now and then in order to even out the selection probabilities over time. PROBLEM: If the number of rows are large (e.g. 200.000 or even a million or more), the update query: UPDATE mydata SET random_number = random(); might be very resource demanding, take a lot of time and pretty much slow down all other transactions because it eats up all resources. 2) The query to select K random rows orders the rows by random_number and selects the first K rows that have random_number larger than some other random number R. But what happens if there is less than K rows with random_value = R? How can the rest of the random rows be picked efficiently? Any ideas on how to deal with these drawbacks? References: [1] http://tinyurl.com/tyg4m ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re : [GENERAL] pg_dump auto login
Warren, make sure that the pgpass.conf contains the correct entries (server name, user name, password). If you are prompted for a password, it's because pg_dump could not find a matching entry to what is specified in your pg_dump command. You can make a test with pgAdmin and if it prompts your for a password then you can type it then and ask to save it (that will be in your pgpass.conf) ... then next time, you will not be prompted any more in pg_dump or pgAdmin. Cheers, [EMAIL PROTECTED] The Computing Froggy - Message d'origine De : Warren [EMAIL PROTECTED] À : pgsql-general@postgresql.org Envoyé le : Mardi, 23 Octobre 2007, 5h37mn 14s Objet : [GENERAL] pg_dump auto login I have two different machines that run pg_dump in a batch file. One prompts for a password and the other one does not. I am running version 8.1 on Windows XP. Both machines have a pgpass.conf file in the right place. The Administrator user runs the batch file. How do I get pg_dump to run without prompting for a password? Thanks, Warren ---(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 _ Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail
Re: [GENERAL] Selecting K random rows - efficiently!
Another way to look at the problem is: How do I sample a subset of size K efficiently? A query like SAMPLE 1000 OF (SELECT * FROM mydata WHERE some condition) should return 1000 random rows from the select statement so that two consecutive evaluations of the query would only with very little probability return the same 1000 rows. (Yes, I know that SAMPLE 1000 OF is not valid SQL) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] deadlock detected, only selects (not select-for-update)
Gábor Farkas [EMAIL PROTECTED] writes: BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; now a lot of selects, but nothing complicated, all of the form of SELECT x FROM y WHERE a=b, with some simple joins, and SELECT DISTINCT and ORDER BY. END; can any of these commands cause the mentioned deadlock? No, a plain select without a FOR SHARE or FOR UPDATE can't cause a deadlock. One thing to be aware of is that In 7.4 foreign keys can cause spurious deadlocks. Two updates against different records in a table will lock the referenced keys. If they lock the referenced keys in different orders then it's possible for them to deaadlock. This is addressed in more recent versions of PostgreSQL. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] (Never?) Kill Postmaster?
Michael Harris [EMAIL PROTECTED] writes: The tip is ''kill -9' the postmaster', which has two important differences to the scenario I just described: 1) kill -9 means the OS kills the process without allowing it to clean up after itself 2) The postmaster is the master postgresql backend process. If you want to kill a single query you would not want to kill that. Right: the tip is to not kill -9 the parent process; it's not saying anything about child server processes. If you've got a child process that's unresponsive to SIGINT then you can send it a SIGKILL instead; the downside is that this will force a restart of the other children too, that is you're interrupting all database sessions not only the one. But Postgres will recover automatically and I don't think I've ever heard of anyone getting data corruption as a result of such a thing. SIGKILL on the parent is an entirely different thing. You'll have to manually restart Postgres, possibly do some manual cleanup, and there's a small but nonzero chance of ensuing data corruption ... especially if you fat-finger any of the manual steps. Plus there simply isn't any good reason to do it. The postmaster should always respond to more gentle shutdown signals, because it doesn't run any user-provided commands that could send it off into the weeds. Hence the TIP. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Install plJava
Hi there, I trying to install pljava, but I'm getting error in this command line: C:\Documents and Settings\Joao Paulojava -cp C:\\Program Files\\PostgreSQL\\8. 2\\share\\pljava\\deploy.jar;C:\\Program Files\\PostgreSQL\\8.2\\jdbc\\postgre sql-8.2-504.jdbc3.jar org.postgresql.pljava.deploy.Deployer -install -user comp iere -database compiere -password compiere org.postgresql.util.PSQLException: ERROR: could not load library C:/Program Fil es/PostgreSQL/8.2/lib/pljava.dll: The specified module could not be found. at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryEx ecutorImpl.java:1548) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutor Impl.java:1316) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.ja va:191) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Stat ement.java:452) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(Abstract Jdbc2Statement.java:337) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Stat ement.java:329) at org.postgresql.pljava.deploy.Deployer.initJavaHandlers(Deployer.java: 474) at org.postgresql.pljava.deploy.Deployer.main(Deployer.java:269) The file pljava.dll exist in directory, why this error? Someone can help me? Thanks! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] (Never?) Kill Postmaster?
In response to Stefan Schwarzer [EMAIL PROTECTED]: Hi there, I read dozens of times the TIP 2: Don't 'kill -9' the postmaster... Now, what am I supposed to do if I launched a query which takes ages, and which I want to interrupt? Thanks for any advice, Tracing through the other threads, this is obviously not the regular wayward query, but one that won't die by the normal methods. Unfortunately, I came across this recently, and the only solution I found was to do a pg_ctl restart -m i (Yes, I tried -m f first). Luckily, the db in question was such that the front ends didn't suffer horribly from this and reconnected, and that the database finished up its recovery in a timely manner. Hopefully, I can generate a reproducible example so I can file a bug, but haven't gotten that far with it yet. -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Selecting K random rows - efficiently!
On Wed, Oct 24, 2007 at 10:59:46AM +0200, cluster wrote: Another way to look at the problem is: How do I sample a subset of size K efficiently? A query like SAMPLE 1000 OF (SELECT * FROM mydata WHERE some condition) How important is true randomness? To get the best possible distribution most algorithms require you to either know how many rows there are, or require you to scan the whole table (or index). With some simplifying assumptions, you can try extracting them from an index, with the caveat that if your index is unbalanced in any way, the selection won't be random. should return 1000 random rows from the select statement so that two consecutive evaluations of the query would only with very little probability return the same 1000 rows. (Yes, I know that SAMPLE 1000 OF is not valid SQL) Presumably your table is very much bigger than that, in which I suppose the not-entirely-random is unlikely to play much of a role. Search the archives, there have been solutions proposed before, though they probably arn't very quick... Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
[GENERAL] initdb: file /usr/local/share/postgresql/snowball_create.sql does not exist
Hi, After installing Postgresql 8.3 beta1 on a fresh FreeBSD 6.2 (port databases/postgresql83-server) initdb gives error: # /usr/local/etc/rc.d/postgresql initdb initdb: file /usr/local/share/postgresql/snowball_create.sql does not exist This means you have a corrupted installation or identified the wrong directory with the invocation option -L. Is this expected at this time, or maybe a faulty port? Thanks. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Concatenate two queries - how?
Hi there, I have two queries, which I would like to bring together to form one result. The first query is a simple SELECT on a table of national statistics. SELECT COALESCE(c.name, ), year_start AS year, value FROM fish_catch AS d LEFT JOIN countries AS c ON c.id = id_country WHERE (year_start = 1995 OR year_start = 2000 ) AND (name = 'Afghanistan' OR name = 'Albania' ) The second query is an aggregation-on-the-fly of these national statistics to its regions. The result is for example not Germany, France, Algeria, ..., but Europe, Africa, ... SELECT COALESCE(r.name, ), year_start AS year, SUM(value) AS value FROM life_expect AS d LEFT JOIN countries_view AS c ON c.id = id_country RIGHT JOIN regions AS r ON r.id = c.reg_id WHERE (year_start = 1995 OR year_start = 2000 ) AND (r.name = 'Europe') GROUP BY r.name, year_start Now, I want to enable queries which display national as well as regional values. I could probably work with independent queries, but I think it would be cleaner and more efficient to get everything into a single query. Can someone give me a hint how this would work? Thanks a lot! Stef ---(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] initdb: file /usr/local/share/postgresql/snowball_create.sql does not exist
rihad wrote: Hi, After installing Postgresql 8.3 beta1 on a fresh FreeBSD 6.2 (port databases/postgresql83-server) initdb gives error: # /usr/local/etc/rc.d/postgresql initdb initdb: file /usr/local/share/postgresql/snowball_create.sql does not exist This means you have a corrupted installation or identified the wrong directory with the invocation option -L. Is this expected at this time, or maybe a faulty port? Faulty port, most likely. They are forgetting to install the snowball_create.sql file perhaps, which is new in 8.3. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Concatenate two queries - how?
am Wed, dem 24.10.2007, um 15:08:51 +0200 mailte Stefan Schwarzer folgendes: Now, I want to enable queries which display national as well as regional values. I could probably work with independent queries, but I think it would be cleaner and more efficient to get everything into a single query. Can someone give me a hint how this would work? Thanks a lot! select ... UNION select ... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Concatenate two queries - how?
Use union ??? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Stefan Schwarzer Sent: Wednesday, October 24, 2007 9:09 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Concatenate two queries - how? Hi there, I have two queries, which I would like to bring together to form one result. The first query is a simple SELECT on a table of national statistics. SELECT COALESCE(c.name, ), year_start AS year, value FROM fish_catch AS d LEFT JOIN countries AS c ON c.id = id_country WHERE (year_start = 1995 OR year_start = 2000 ) AND (name = 'Afghanistan' OR name = 'Albania' ) The second query is an aggregation-on-the-fly of these national statistics to its regions. The result is for example not Germany, France, Algeria, ..., but Europe, Africa, ... SELECT COALESCE(r.name, ), year_start AS year, SUM(value) AS value FROM life_expect AS d LEFT JOIN countries_view AS c ON c.id = id_country RIGHT JOIN regions AS r ON r.id = c.reg_id WHERE (year_start = 1995 OR year_start = 2000 ) AND (r.name = 'Europe') GROUP BY r.name, year_start Now, I want to enable queries which display national as well as regional values. I could probably work with independent queries, but I think it would be cleaner and more efficient to get everything into a single query. Can someone give me a hint how this would work? Thanks a lot! Stef ---(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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Concatenate two queries - how?
I don't know whether I did understand you entirely, but you might want to take a look at the UNION clause: http://www.postgresql.org/docs/8.2/interactive/sql-select.html#SQL-UNION -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Im Auftrag von Stefan Schwarzer Gesendet: Mittwoch, 24. Oktober 2007 15:09 An: pgsql-general@postgresql.org Betreff: [GENERAL] Concatenate two queries - how? Hi there, I have two queries, which I would like to bring together to form one result. The first query is a simple SELECT on a table of national statistics. SELECT COALESCE(c.name, ), year_start AS year, value FROM fish_catch AS d LEFT JOIN countries AS c ON c.id = id_country WHERE (year_start = 1995 OR year_start = 2000 ) AND (name = 'Afghanistan' OR name = 'Albania' ) The second query is an aggregation-on-the-fly of these national statistics to its regions. The result is for example not Germany, France, Algeria, ..., but Europe, Africa, ... SELECT COALESCE(r.name, ), year_start AS year, SUM(value) AS value FROM life_expect AS d LEFT JOIN countries_view AS c ON c.id = id_country RIGHT JOIN regions AS r ON r.id = c.reg_id WHERE (year_start = 1995 OR year_start = 2000 ) AND (r.name = 'Europe') GROUP BY r.name, year_start Now, I want to enable queries which display national as well as regional values. I could probably work with independent queries, but I think it would be cleaner and more efficient to get everything into a single query. Can someone give me a hint how this would work? Thanks a lot! Stef ---(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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] using libpq.lib in Microsoft C++ (managed)
Hi All. I've the necessity to call some functions of libpq.lib from my code. The problem is that my code is Managed C++ (as defined by Microsoft VisualStudio 2005). Managed C++ has a very special memory management and I don't know if using libpq.lib (an old C library) is a safe procedure. I've Already had problems using libpq.lib in a multi-threaded standard C++ and so I'm quite doubtful that all will work fine in Managed C++. Has some one had experience concerning my doubts? Thanks in advance. Luca -- Email.it, the professional e-mail, gratis per te: http://www.email.it/f Sponsor: In REGALO un GIOCO! Scegli GPBikes 3D,Bubble Boom, Rock City Empire Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=6732d=20071024
Re: [GENERAL] using libpq.lib in Microsoft C++ (managed)
On Wed, Oct 24, 2007 at 03:06:03PM +0200, [EMAIL PROTECTED] wrote: Hi All. I've the necessity to call some functions of libpq.lib from my code. The problem is that my code is Managed C++ (as defined by Microsoft VisualStudio 2005). Managed C++ has a very special memory management and I don't know if using libpq.lib (an old C library) is a safe procedure. I've Already had problems using libpq.lib in a multi-threaded standard C++ and so I'm quite doubtful that all will work fine in Managed C++. Has some one had experience concerning my doubts? If you are using Managed C++, you should probably be using npgsql instead of libpq. //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Extracting Index Creation Script
Hello, I answered my own question. :) I continued looking last night after I posted this and found a really easy way to get this info... select * from pg_indexes where tablename like 'YOURTABLENAME' This will give you the DDL to create your indexes. Regards, Paul Paul Silveira wrote: Hello, Does anyone have any good code to extract the metadata needed to create indexes on a specific table? The Client Tools (like pgadmin-III) presents that code so I'm sure it's extractable but could not find it in my trace that I ran while operating pgadmin... Thanks in advance, Paul -- View this message in context: http://www.nabble.com/Extracting-Index-Creation-Script-tf4680848.html#a13384579 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] 8.3b1 in production?
Hi, Does anyone have an idea how risky it is to start using 8.3b1 in production, with the intention of upgrading to release (or newer beta) as soon as it becomes available? Risky compared to running a release, that is. Beta - release upgrades might be less tricky than 8.2 - 8.3. Thank you. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Selecting K random rows - efficiently!
How important is true randomness? The goal is an even distribution but currently I have not seen any way to produce any kind of random sampling efficiently. Notice the word efficiently. The naive way of taking a random sample of size K: (SELECT * FROM mydata ORDER BY random() LIMIT K) is clearly not an option for performance reasons. It shouldn't be necessary to explain why. :-) Search the archives, there have been solutions proposed before, though they probably arn't very quick... As the subject suggests, performance really matters and searching the archives only results in poor solutions (my first post explains why). ---(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] 8.3b1 in production?
rihad [EMAIL PROTECTED] writes: Does anyone have an idea how risky it is to start using 8.3b1 in production, with the intention of upgrading to release (or newer beta) as soon as it becomes available? Risky compared to running a release, that is. Beta - release upgrades might be less tricky than 8.2 - 8.3. At this point you're guaranteed to need a dump/reload between beta1 and beta2 ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Constraints involving a system table
Hello, I would like to create a constraint that involves the system table. CREATE TABLE metadata_tables ( schemaname text REFERENCES pg_namespace(nspname) ON DELETE CASCADE CONSTRAINT valid_schema CHECK (schemaname != 'public' AND schemaname != 'information_schema' AND schemaname NOT LIKE 'pg_%'), tablename text, source text ) WITH (OIDS=FALSE) ; Basically, I only want to allow that the user can use schemanames present in the current database as input in the column schemaname. When I try to create this table, I get an error that it is a system table. ERROR: permission denied: pg_namespace is a system catalog However, when looking at the system table definition, it has GRANT SELECT ON TABLE pg_namespace TO public; - so everybody should be able to query a system table. Why is this not working? What other privileges besides SELECT would one need to define a constraint? Thanks for any hint, Andreas -- Andreas Neumann Böschacherstrasse 6, CH-8624 Grüt/Gossau, Switzerland Email: [EMAIL PROTECTED], Web: * http://www.carto.net/ (Carto and SVG resources) * http://www.carto.net/neumann/ (personal page) * http://www.svgopen.org/ (SVG Open Conference) * http://www.geofoto.ch/ (Georeferenced Photos of Switzerland) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Automating Backup Restore
Hello all, Does someone have a script that backups a database from one server and restores it to another server??? I am NEW to Postgresql so I am starting from scratch... so, in essence - what I want to do is (I CURRENTLY DO THIS MANUALLY): Server1 (IS IT POSSIBLE TO DUMP DIRECTLY TO SERVER2??) pg_dump filename.dmp scp filename.dmp to SERVER2 Server2 psql -d mydb -f filename.dmp vacuum reindex Any hints or tips are welcomed... Thanks...Michelle. -- View this message in context: http://www.nabble.com/Automating-Backup---Restore-tf4684052.html#a13384762 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] 8.3b1 in production?
On Wednesday 24 October 2007 09:59:20 rihad wrote: Hi, Does anyone have an idea how risky it is to start using 8.3b1 in production, with the intention of upgrading to release (or newer beta) as soon as it becomes available? Risky compared to running a release, that is. Beta - release upgrades might be less tricky than 8.2 - 8.3. I'm pretty sure b2 is going to require a fresh initdb (due to bugs found). So I'd advice against that. Thank you. jan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- -- Jan de Visser [EMAIL PROTECTED] Baruk Khazad! Khazad ai-menu! -- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Automating Backup Restore
On Wednesday 24 October 2007 10:39:23 smiley2211 wrote: Does someone have a script that backups a database from one server and restores it to another server??? I am NEW to Postgresql so I am starting from scratch... $ pg_dump -h server1 database | psql -h server2 jan -- -- Jan de Visser [EMAIL PROTECTED] Baruk Khazad! Khazad ai-menu! -- ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] google
where does a 500 lb gorilla sit? http://www.networkworld.com/news/2007/102307-mysql-to-get-injection-of.html?netht=102407dailynews1nladname=102407dailynews The companies are in close contact, Axmark said. The Google engineer in charge of its MySQL deployments spent several days at MySQL's internal developer meeting in Heidelberg, Germany, last month. Google has also released source code for some MySQL tools it developed. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Automating Backup Restore
Jan de Visser wrote: On Wednesday 24 October 2007 10:39:23 smiley2211 wrote: Does someone have a script that backups a database from one server and restores it to another server??? I am NEW to Postgresql so I am starting from scratch... $ pg_dump -h server1 database | psql -h server2 jan Further to this, Shane Ambler posted this response just yesterday in response to a similar question: If you want to copy your database to a development machine occasionally, then if they have the same hardware and postgres version then you should be able to copy the data files across while the production server is stopped without problems. Some differences between CPU and postgres version can stop this from working. You could also do the dump/restore directly from one machine to another if they are on the same network without having the dump file to transfer across and import which will give similar results. eg. instead of - pg_dumpall mybackupfile psql -h my.other.machine.address mybackupfile you can - pg_dumpall | psql -h my.other.machine.address If you have different cpu's (one Intel and one AMD64) or different postgres versions then you will have to do the dump/restore even if it is sent straight from one to another. See this thread: http://archives.postgresql.org/pgsql-general/2007-10/msg01178.php brian ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] jdbc: ERROR transaction is read-only
I am using the postgresql-8.2.506.jdbc4.jar file My jdbc connections are setReadOnly(false) When attempting an insert I get org.postgresql.util.PSQLException: ERROR: transaction is read-only ideas? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] function result cache for pl/pgsql
IMHO, pl/perl;s global hash %_SHARED does not provide the same performance. With pl/perl with some data cached we will get getter performance, that is sure, but the body of the function will be evaluated anyway, so there will be some miliseconds lost. On the other hand, the body of a function with in-memory result cache clause will not be evaluated if the supplied arguments' result is already cached, and the function will just instantly return the result - which is the timesaver. So, I would prefer to have such function clause implemented in pgsql, maybe as add-on. Best, Peter _ Peek-a-boo FREE Tricks Treats for You! http://www.reallivemoms.com?ocid=TXT_TAGHMloc=us ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] google
On Oct 24, 2007, at 8:44 AM, Ray Stell wrote: where does a 500 lb gorilla sit? http://www.networkworld.com/news/2007/102307-mysql-to-get-injection- of.html?netht=102407dailynews1nladname=102407dailynews The companies are in close contact, Axmark said. The Google engineer in charge of its MySQL deployments spent several days at MySQL's internal developer meeting in Heidelberg, Germany, last month. Google has also released source code for some MySQL tools it developed. You've got the wrong database's mailing list. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Automating Backup Restor
On 10/24/07, smiley2211 [EMAIL PROTECTED] wrote: Hello all, Does someone have a script that backups a database from one server and restores it to another server??? I am NEW to Postgresql so I am starting from scratch... so, in essence - what I want to do is (I CURRENTLY DO THIS MANUALLY): Server1 (IS IT POSSIBLE TO DUMP DIRECTLY TO SERVER2??) pg_dump filename.dmp scp filename.dmp to SERVER2 Server2 psql -d mydb -f filename.dmp vacuum reindex server1$ pg_dump mydb | psql -h server2 -d mydb Regards M
Re: [GENERAL] 8.3b1 in production?
rihad [EMAIL PROTECTED] writes: Hi, Does anyone have an idea how risky it is to start using 8.3b1 in production, with the intention of upgrading to release (or newer beta) as soon as it becomes available? Risky compared to running a release, that is. Beta - release upgrades might be less tricky than 8.2 - 8.3. Well nobody's going to be able to guess at what problems haven't been found yet. All we can say decisively is what bugs have already been found: . On Windows UTF8 encoding isn't allowed . VACUUM does an unnecessarily large amount of I/O . Toaster could cause failures on machines with strict alignment . Resources limits in Windows limit the number of clients . pg_tablespace_size() on pg_global fails even for superuser . ABI break with old libpq for applications which depend on encoding IDs (such as initdb -- you can't run initdb with an 8.2 libpq against an 8.3 server) . invalid tsvector input could cause crashes . ALTER COLUMN TYPE would reset the index's options, possibly moving it to the default tablespace or worse Also: . A new data type, txid, was added . Several new contrib modules were added to aid tsearch migration . Some tsearch functions were removed or modified . tsearch word categories were redefined and renamed . Make plan invalidation work for dropped sequences (etc) . Be careful to get share lock on each page before computing its free space. . This avoids useless checkpoint activity if XLogWrite is executed when we have a very stale local copy of RedoRecPtr. . Teach planagg.c that partial indexes specifying WHERE foo IS NOT NULL can be used to perform MIN(foo) or MAX(foo) . Remove an Assert that's been obsoleted by recent changes in the parsetree representation of DECLARE CURSOR. Report and fix by Heikki. . Ensure that the result of evaluating a function during constant-expression simplification gets detoasted before it is incorporated into a Const node. . Make dumpcolors() have tolerable performance when using 32-bit chr, as we do . Make role is not permitted to log in errors not be hidden . Remove quotes around locale names in some places for consistency. . Add missing entry for PG_WIN1250 encoding, per gripe from Pavel Stehule. Also enable translation of PG_WIN874 -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] PostgreSQL and AutoCad
Is there any way of converting text from an AutoCad (.dwg ot .dxf) file into a PostgreSQL Database?? Bob Pawley
[GENERAL] Solaris binaries for pgAdmin III
I need to install the pgAgent daemon on Solaris but I don't see a binary for download except for earlier versions. How soon will v1.8 for Solaris be available in binary format? Thanks! Jon
[GENERAL] subversion support?
Are there plans to support a plug-in to a version control system like subversion for DDL? We really need to version our functions we will be writing but currently, we have to go through some hoops to get the function code into subversion. Jon
Re: [GENERAL] Constraints involving a system table
ok - so the trigger is the problem. I am aware that one should not mess around with system tables. is there a workaround? Maybe creating a view or a function? Thanks, Andreas Richard Huxton wrote: Andreas Neumann wrote: Hello, I would like to create a constraint that involves the system table. I'm afraid you can't (at present, anyway) Why is this not working? What other privileges besides SELECT would one need to define a constraint? Well, you need to be able to add a trigger to the system table (which you can't do), otherwise it can't check what to do when you delete something from pg_namespace. I think there are two reasons for this: 1. Adding a badly coded trigger to a system table can make your life very difficult. 2. PG uses a few shortcuts behind the scenes when accessing system tables. -- -- Andreas Neumann Böschacherstrasse 6 CH-8624 Grüt (Gossau ZH) Switzerland Phone: ++41-44-2736668 Email: [EMAIL PROTECTED] Web: http://www.carto.net/neumann/ SVG Examples: http://www.carto.net/papers/svg/samples/ SVG.Open: http://www.svgopen.org/ ---(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] subversion support?
Roberts, Jon wrote: Are there plans to support a plug-in to a version control system like subversion for DDL? We really need to version our functions we will be writing but currently, we have to go through some hoops to get the function code into subversion. This is hoops? svn ci your_functions.sql How do you do things now? b ---(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] subversion support?
Yeah. I think having to save the function to disk and then leave pgAdmin to execute subversion commands is going through hoops. Also, pgAdmin should be integrated so that you are notified if the function in the database is different from the last committed version. A visual diff should be there so you can see what the differences are. Jon -Original Message- From: brian [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 24, 2007 1:59 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] subversion support? Roberts, Jon wrote: Are there plans to support a plug-in to a version control system like subversion for DDL? We really need to version our functions we will be writing but currently, we have to go through some hoops to get the function code into subversion. This is hoops? svn ci your_functions.sql How do you do things now? b ---(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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Automating Backup Restore
THANKS ALL... -- View this message in context: http://www.nabble.com/Automating-Backup---Restore-tf4684052.html#a13392360 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] subversion support?
On Wed, 24 Oct 2007 14:11:24 -0500 Roberts, Jon [EMAIL PROTECTED] wrote: Yeah. I think having to save the function to disk and then leave pgAdmin to execute subversion commands is going through hoops. Also, pgAdmin should be integrated so that you are notified if the function in the database is different from the last committed version. A visual diff should be there so you can see what the differences are. Would you be willing to sponsor such development? :) Joshua D. Drake Jon -Original Message- From: brian [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 24, 2007 1:59 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] subversion support? Roberts, Jon wrote: Are there plans to support a plug-in to a version control system like subversion for DDL? We really need to version our functions we will be writing but currently, we have to go through some hoops to get the function code into subversion. This is hoops? svn ci your_functions.sql How do you do things now? b ---(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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ signature.asc Description: PGP signature
Re: [GENERAL] Concatenate two queries - how?
In article [EMAIL PROTECTED], A. Kretschmer [EMAIL PROTECTED] writes: am Wed, dem 24.10.2007, um 15:08:51 +0200 mailte Stefan Schwarzer folgendes: Now, I want to enable queries which display national as well as regional values. I could probably work with independent queries, but I think it would be cleaner and more efficient to get everything into a single query. Can someone give me a hint how this would work? Thanks a lot! select ... UNION select ... Apparently Stefan doesn't know about UNION, and thus he probably doesn't know that UNION ALL is almost always preferrable. ---(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] Using Postgres as a embedded / SQL Lite database on Windows
Hi OK, Sorry but I did search, and did not find anything useful. Maybe putting forward a embedded solution as part of the question was the wrong option. If I could reword: Given that we have a large estiblished client base running a Delphi/Paradox solution, and that we would like to replace Paradox with a much better SQL engine, I was looking for comments as to how Postgres maybe suitable. We will be implementing more multi-user features - it really is only single user at this stage, plus we would like to be able to use better SQL - espically for Reporting - where currently we have hundreds of lines of Delphi code which could be replaced with a one or two reasonable SQL Queries. There will really be two targets, replacing the existing single user solutions, and providing a larger solution with multi-user etc. There is however large base of similar code between these solutions, and it would be perferable if they could use the same back-end engine or something very simliar. I know some people will aruge that you should use the right tool for the job, and I guess I am tring to see if there is a right tool which will work for both these. My main/only real hesiation with Postgres is around supporting non IT users, mainly around installation. I am keen to here peoples comments on this, and what steps could be taken to mitigate these. Currently the main alternate which is being considered is SQL Server, using SQL Express, SQL Mobile/Compact. But I see this as limiting use to only features supported by the Compact edition, or having to install SQL Express - which I can see as being worst than supporting Postgres Installs. Thank you for your comments Craig On 10/24/07, Scott Marlowe [EMAIL PROTECTED] wrote: On 10/23/07, Craig Hawkes [EMAIL PROTECTED] wrote: Hi Everyone I have a question re using postgres as a embedded database on Windows. This question gets asked about once every three months. Searching the archives should turn up some previous discussions on the subject. First a little background: I have been using Postgres for a number of years on Linux, and it is great. I am now working with a company which develops Windows software using Delphi. This has been successful and they have a number of users (1000's). This currently uses a Paradox database. For various reasons (some I am sure you can guess!) we would like to move away from paradox, and Postgres is a good option. I would like to hear feed back from others, has anyone else deployed Postgres in this type of environment? Are there options for running postgres, maybe as a process (maybe the wrong term) within the main application - this could be started and stopped by the application? No, and it's not likely to happen. this would make postgresql subject to silent corruption if the application crashed, and no one wants to support that on top of the version we already have. I am thinking some type of options similar to SQLLite would be great for a single user install - but we also want to be able to A) use the full power of Postgres, B) set-up as a full server in some other cases. Sadly, there ain't no such thing as a free lunch here. Either you install the real thing, and deal with the issues of administration and such, or you go with sqllite or it's brethren. I'd look into building an appliance computer for the customers. Something in a 1U rack mount with a pair of SATA or SAS drives mounted in a sw RAID-1 would do nicely for this kind of thing. Think of it as delivering the whole solution. Deliver it with a super simple custom gui on top to create accounts or phpmyadmin or something. The further away from a database on every desktop you get, generally the better off you'll be. If embedded fits this project better, then look elsewhere, it's really not pgsql's strong suit.
Re: [GENERAL] FATAL: could not reattach to shared memory
Bruce Momjian escribió: This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold Update: I have installed PostgreSQL 8.2.5 and move database from old to new server. This was 2 weeks ago. New Server is a Windows 2003 Server running other services too. Until now, this problem has gone out and PosgresSQL is running like a charm on the new server. :-) Greetings. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] GRANT error
So, I'm working with some grant/revoke scripts today and occasionally I see this while running tests: STATEMENT: GRANT select, insert, update, delete ON public.tablename TO app; ERROR: tuple concurrently updated What's going on? Do I actually need a lock on the table to ensure this doesn't happen? I haven't seen anything in the docs with regards to concurrency and granting permissions on objects. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Fragments in tsearch2 headline
Hi, (I first posted it via google groups and realised that I have to be subscribed; now posting directly) I searched the list but couldn't find anyone raising the issue (or it might simply be my way of using the tool). I'd like to search through some text documents for words and generate headlines. The search works fine but, if the words are far apart in the document, the headline only highlights one of the words. Enlarging the headline with Min/MaxWords is not an option as I have limited space for displaying it. Is there an easy way to generate a headline from separate fragments containing the search words and maybe separated by ...? An option is to generate separate headlines and concatenate them before displaying (with a problem when the words are in the same fragment). Another option would be to somehow get the found words position in the document (anyone knows how?) and generate the headline myself. Any other ideas? Thanks. -- Catalin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Install plJava
João Paulo Zavanela wrote: The file pljava.dll exist in directory, why this error? Someone can help me? PL/Java has it's own mailing list here: http://gborg.postgresql.org/mailman/listinfo/pljava-dev I think it is still active, but I'm not sure. Sorry, I'm short on time. Search the archives there; I think this problem has come up before. -- Guy Rouillier ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Using Postgres as a embedded / SQL Lite database on Windows
On 10/25/07, Craig Hawkes [EMAIL PROTECTED] wrote: There will really be two targets, replacing the existing single user solutions, and providing a larger solution with multi-user etc. There is however large base of similar code between these solutions, and it would be perferable if they could use the same back-end engine or something very simliar. I know some people will aruge that you should use the right tool for the job, and I guess I am tring to see if there is a right tool which will work for both these. I guess you could be looking at an intermediate abstraction layer that will allow your application to communicate with whatever database that newly added tier can talk to. My main/only real hesiation with Postgres is around supporting non IT users, mainly around installation. I am keen to here peoples comments on this, and what steps could be taken to mitigate these. The immediate option that springs to mind would be to offer consultancy and installation support. Thank you for your comments Craig Cheers, Andrej P.S.: Please, do not top post :} ... see my sig for reasons. -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---(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] Migration questions for upcoming 8.3 release and fts
Hi all; I know -hackers is the preferred place to discuss beta releases, but I suspect that this is a further-reaching wuestion among the general community than is typical so I am posting it here. I have been looking at the changes expected for 8.3 and have noticed that tsearch2 has been modified and merged with core. I think this is a great idea, but it poses problems for applications which must run both both on 8.2 and 8.3 with fts capabilities. I am trying to determine the best way forward so that LedgerSMB can support 8.3 as soon as it is released. Reading through various email list archives it looks like the function names have changed. I guess I am trying to determine the best way forward. 1) How safe is it likely to be to create a set of functions in 8.3 which mimic 8.2/tsearch2 interfaces? Is this likely to create any serious conflicts? Would such a project be sufficiently useful that a pg_foundry project might be helpful? 2) If this is a problem, is the community interested in (possibly as a pg-foundry project) an abstraction layer for supporting both sets of interfaces? Best Wishes, Chris Travers ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Selecting K random rows - efficiently!
cluster wrote: It has been suggested [1] that a good way to select K random rows efficiently from a table is to 1) add a new column, random_number, to the table and initialize it with random() 2) perform the following query: SELECT * FROM mydata WHERE random_number = (SELECT RANDOM() OFFSET 0) ORDER BY random_number ASC LIMIT K Here K is the number of random rows to pick. E.g. 100. The benefit in this solution is that the random_number column can be indexed, allowing the query to be performed using a simple and fast index scan. However, there is a couple of major drawbacks in this method: 1) The smaller random_number is, the less likely is it that it will be picked when using this method. Example: A random_number close to zero will only have a very small probability to be selected. When the above query returns L rows (where L K) then, you need to append the first K - L rows from the table to simulate a ring without start or end. (Conveniently, this also solves the problem of not finding K rows because the random start value was too large.) The second set of rows can certainly be fetched using a second SELECT statement. Whether this can be computed efficiently as a single SELECT statement I am not sure but you might try something like this: (SELECT 1 AS seq, * FROM mydata WHERE random_number = (SELECT RANDOM() OFFSET 0) ORDER BY random_number ASC LIMIT K) UNION ALL (SELECT 2 AS seq, * FROM mydata ORDER BY random_number ASC LIMIT K) ORDER BY seq ASC, random_number ASC LIMIT K; This should provide each row with an equal chance of being selected while requiring the database to fetch at most 2 * K rows. Regards, Paul Tillotson ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Crosstab Problems
Tom Lane wrote:
Jorge Godoy [EMAIL PROTECTED] writes:
Em Thursday 18 October 2007 16:37:59 Joe Conway escreveu:
The row is pretty useless without a rowid in this context -- it seems
like the best thing to do would be to skip those rows entirely. Of
course you could argue I suppose that it ought to throw an ERROR and
bail out entirely. Maybe a good compromise would be to skip the row but
throw a NOTICE?
If I were using it and having this problem I'd rather have an ERROR.
I can think of four reasonably credible alternatives:
1. Treat NULL rowid as a category in its own right. This would conform
with the behavior of GROUP BY and DISTINCT, for instance.
4. Silently ignore rows with NULL rowid.
After looking closer I realized that #4 was my original intention, and
there was even code attempting to implement it, but just not very well ;-(.
In any case, the attached changes the behavior to #1 for both flavors of
crosstab (the original crosstab(text, int) and the usually more useful
crosstab(text, text)).
It is appropriate for 8.3 but not back-patching as it changes behavior
in a non-backward compatible way and is probably too invasive anyway.
I'll do something much simpler just to prevent crashing for 8.2 and earlier.
If there are no objections I'll apply Thursday.
Joe
Index: tablefunc.c
===
RCS file: /opt/src/cvs/pgsql/contrib/tablefunc/tablefunc.c,v
retrieving revision 1.47
diff -c -r1.47 tablefunc.c
*** tablefunc.c 3 Mar 2007 19:32:54 - 1.47
--- tablefunc.c 25 Oct 2007 02:11:06 -
***
*** 355,360
--- 355,361
crosstab_fctx *fctx;
int i;
int num_categories;
+ bool firstpass = false;
MemoryContext oldcontext;
/* stuff done only on the first call of the function */
***
*** 469,474
--- 470,476
funcctx-max_calls = proc;
MemoryContextSwitchTo(oldcontext);
+ firstpass = true;
}
/* stuff done on every call of the function */
***
*** 500,506
HeapTuple tuple;
Datum result;
char **values;
! bool allnulls = true;
while (true)
{
--- 502,508
HeapTuple tuple;
Datum result;
char **values;
! bool skip_tuple = false;
while (true)
{
***
*** 530,555
rowid = SPI_getvalue(spi_tuple, spi_tupdesc, 1);
/*
! * If this is the first pass through the values for this rowid
! * set it, otherwise make sure it hasn't changed on us. Also
! * check to see if the rowid is the same as that of the last
! * tuple sent -- if so, skip this tuple entirely
*/
if (i == 0)
- values[0] = pstrdup(rowid);
-
- if ((rowid != NULL) (strcmp(rowid, values[0]) == 0))
{
! if ((lastrowid != NULL) (strcmp(rowid, lastrowid) == 0))
break;
! else if (allnulls == true)
! allnulls = false;
/*
! * Get the next category item value, which is alway
* attribute number three.
*
! * Be careful to sssign the value to the array index based
* on which category we are presently processing.
*/
values[1 + i] = SPI_getvalue(spi_tuple, spi_tupdesc, 3);
--- 532,574
rowid = SPI_getvalue(spi_tuple, spi_tupdesc, 1);
/*
! * If this is the first pass through the values for this
! * rowid, set the first column to rowid
*/
if (i == 0)
{
! if (rowid)
! values[0] = pstrdup(rowid);
! else
! values[0] = NULL;
!
! /*
! * Check to see if the rowid is the same as that of the last
! * tuple sent -- if so, skip this tuple entirely
! */
! if (!firstpass
! (((lastrowid == NULL) (rowid == NULL)) ||
! ((lastrowid != NULL)
! (rowid != NULL)
! (strcmp(rowid, lastrowid) == 0
! {
! skip_tuple = true;
break;
! }
! }
+ /*
+ * If rowid hasn't changed on us, continue building the
+ * ouput tuple.
+ */
+ if ((rowid values[0] (strcmp(rowid, values[0]) == 0)) ||
+ ((rowid == NULL) (values[0] == NULL)))
+ {
/*
! * Get the next category item value, which is always
* attribute number three.
*
! * Be careful to assign the value to the array index based
* on which category we are presently processing.
*/
values[1 + i] = SPI_getvalue(spi_tuple, spi_tupdesc, 3);
***
*** 572,584
call_cntr = --funcctx-call_cntr;
break;
}
!
! if (rowid != NULL)
! xpfree(rowid);
}
xpfree(fctx-lastrowid);
-
if (values[0] != NULL)
{
/*
--- 591,600
call_cntr = --funcctx-call_cntr;
break;
}
! xpfree(rowid);
}
xpfree(fctx-lastrowid);
if (values[0] != NULL)
{
/*
***
*** 586,597
* calls
*/
Re: [GENERAL] Selecting K random rows - efficiently!
Here's how I would do it. This assumes a static table that doesn't
change a lot.
1: find the row count n of the table.
2: randomly assign 1 through n to each row randomly. How to do this
is a whole not post.
3: create a sequence. If you always need 10 or 100 random rows, set
the increment to that number. set it to cycle at the size of the
table.
4: select nextval('sequence') =nv and use it in a select:
select * from myrandomtable where id between nv and nv+100; -- or
whatever your increment is.
There are refinements to this. The advantages, with a static data
set, are that you can cluster on the randomized id and get chunks of
the random dataset VERY quickly, and you won't repeat the results
until you start over. you can re-randomize the table every x hours or
days or weeks to meet your needs. If you don't want to re-randomize
it during the day, just put the random data set into it however many
times you need to so that it won't roll over until the next day/week
etc...
Does that make sense?
If your data changes all the time, you've got a more difficult problem
to deal with.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Using Postgres as a embedded / SQL Lite database on Windows
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/24/07 15:04, Craig Hawkes wrote: Hi OK, Sorry but I did search, and did not find anything useful. Maybe putting forward a embedded solution as part of the question was the wrong option. If I could reword: Given that we have a large estiblished client base running a Delphi/Paradox solution, and that we would like to replace Paradox with a much better SQL engine, I was looking for comments as to how Postgres maybe suitable. We will be implementing more multi-user features - it really is only single user at this stage, plus we would like to be able to use better SQL - espically for Reporting - where currently we have hundreds of lines of Delphi code which could be replaced with a one or two reasonable SQL Queries. Hmmm. Is Paradox that bad? Or are you pinning too many hopes on SQL? There will really be two targets, replacing the existing single user solutions, and providing a larger solution with multi-user etc. There is however large base of similar code between these solutions, and it would be perferable if they could use the same back-end engine or something very simliar. I know some people will aruge that you should use the right tool for the job, and I guess I am tring to see if there is a right tool which will work for both these. My main/only real hesiation with Postgres is around supporting non IT users, mainly around installation. I am keen to here peoples comments on this, and what steps could be taken to mitigate these. Install PostgreSQL on your box, run the app and see how much maintenance it needs, and how much can be scripted. If PG is installed as a service (which it should be, no?), then certainly there is an API call to start the postgresql service at the beginning of your app and another to stop the service during app termination. Currently the main alternate which is being considered is SQL Server, using SQL Express, SQL Mobile/Compact. But I see this as limiting use to only features supported by the Compact edition, or having to install SQL Express - which I can see as being worst than supporting Postgres Installs. I think that you're just going to have to create a pilot project to see how it fits your individual needs. - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHIAehS9HxQb37XmcRApZYAJ9uI5NHgVF19MytK2M+7+6xHGhlNACfR1bL qRhO+gSrIF5ow7lbQUbkWqo= =5vCS -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Crosstab Problems
Joe Conway [EMAIL PROTECTED] writes: Tom Lane wrote: 1. Treat NULL rowid as a category in its own right. This would conform with the behavior of GROUP BY and DISTINCT, for instance. In any case, the attached changes the behavior to #1 for both flavors of crosstab (the original crosstab(text, int) and the usually more useful crosstab(text, text)). It is appropriate for 8.3 but not back-patching as it changes behavior in a non-backward compatible way and is probably too invasive anyway. Um, if the previous code crashed in this case, why would you worry about being backward-compatible with it? You're effectively changing the behavior anyway, so you might as well make it do what you've decided is the right thing. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
