Re: [GENERAL] optimizer ignoring primary key and doing sequence scan
Scott Marlowe ha scritto: On Mon, Jul 14, 2008 at 1:54 PM, Chris Hoy <[EMAIL PROTECTED]> wrote: Hi I have a number of tables in my database where the queries appear to ignoring the primary key and doing a seq scan instead, however other tables appear to be fine. I can see any difference between them. Is their any way of determination why the otimizer isn't picking up the primary key? Version 8.3.3 windows An example of a non working table is: select * from industries where industryid = 1; "Seq Scan on industries (cost=0.00..1.02 rows=1 width=116) (actual time=0.011..0.013 rows=1 loops=1)" According to this there's only one row in the table. why WOULD postgresql use an index when it can just scan the one row table in a split second. I agree with you that it can depend on the size of the table but where you can read that the table contains only one row? I try with my table (39910 rows, no index on column note) explain analyze select * from table where note='single example'; Seq Scan on table (cost=0.00..2458.88 rows=13 width=327) (actual time=10.901..481.896 rows=1 loops=1) On the postgres manual I can find "Estimated number of rows output by this plan node (Again, only if executed to completion.)" regarding the third parameter of the explain Where is my error? Edoardo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
download latest points to wrong place [was Re: [GENERAL] Installing PostgreSQL without using CygWin]
Dann Corbit wrote: The current version of the Installer installs a native build. Cygwin has not been used in the standard Windows build for a long time. Go here: http://www.postgresql.org/ftp/binary/v8.3.1/win32/ Get this: http://wwwmaster.postgresql.org/download/mirrors-ftp?file=%2Fbinary%2Fv8.3.1%2Fwin32%2Fpostgresql-8.3.1-1.zip << The 'latest' directory on http://www.postgresql.org/ftp/binary/ points to 8.3.1 not 8.3.3 This is the one you need http://wwwmaster.postgresql.org/download/mirrors-ftp?file=%2Fbinary%2Fv8.3.3%2Fwin32%2Fpostgresql-8.3.3-1.zip klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installing PostgreSQL without using CygWin
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of ken andrew Sent: Sunday, July 13, 2008 10:27 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Installing PostgreSQL without using CygWin Hi, I would like to know if there is a way to install PostgreSQL in Windows NT Server 2003 without installing CygWin. The client insists on not installing CygWin. >> The current version of the Installer installs a native build. Cygwin has not been used in the standard Windows build for a long time. Go here: http://www.postgresql.org/ftp/binary/v8.3.1/win32/ Get this: http://wwwmaster.postgresql.org/download/mirrors-ftp?file=%2Fbinary%2Fv8.3.1%2Fwin32%2Fpostgresql-8.3.1-1.zip <<
Re: [GENERAL] Out of memry with large result set
[EMAIL PROTECTED] wrote: I am doing a query via psql on a huge database, and I want to have its output piped to other unix processes. As the result set is very big, I've got: "out of memory for query result". How can I manage that, without playing with cursors, as I do not want to change the sql query ? Under mysql, I have the same issue, but by using: mysql -quick, I have what I want. Is there something equivalent under postgresql ? Or should I use another clients ? Try copy (query) to stdout. For me, psql sits at 4.9mb ram on a 3x10^16 row query. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Backing up and deleting a database.
We have a database that grows in size quite quickly. Of course we backup nightly and keep a weeks worth of data However we need to keep a few months data online, but the rest can be archived as it will be unlikley that it will be used again. As I see it we can: 1) Run a query to drop/delete old data, the downside here is that we lose it. 2) Stop the database (this is important because clients are writing to it), back it up, delete it and recreate the database. Has anyone done this? Do they have a script for htis? I would appreciate any comments about what approaches have been used that work. Thanks for any info. Andrew -- ___ Andrew J. P. Maclean Centre for Autonomous Systems The Rose Street Building J04 The University of Sydney 2006 NSW AUSTRALIA Ph: +61 2 9351 3283 Fax: +61 2 9351 7474 URL: http://www.acfr.usyd.edu.au/ ___ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 8.3.3 Complie issue
> I think we've seen this before: do you have utf8_and_euc_jis_2004.c
> in that directory? There seem to be some broken versions of tar out
> there that can't deal with extracting such a long file name from the
> distribution tarball.
Thanks Tom,
The "c" on the end was missing.
Fixed that, how about this
echo '{ global:' >exports.list
gawk '/^[^#]/ {printf "%s;\n",$1}' exports.txt >>exports.list
echo ' local: *; };' >>exports.list
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline
-fno-strict-aliasing -fpic -shared -Wl,-soname,libpq.so.5
-Wl,--version-script=exports.list fe-auth.o fe-connect.o fe-exec.o fe-misc.o
fe-print.o fe-lobj.o fe-protocol2.o fe-protocol3.o pqexpbuffer.o pqsignal.o
fe-secure.o md5.o ip.o wchar.o encnames.o noblock.o pgstrcasecmp.o thread.o
strlcpy.o -L../../../src/port -lcrypt -Wl,-rpath,'/removeExt2/pgsql8.3/lib' -o
libpq.so.5.1
/usr/i386-slackware-linux/bin/ld:exports.list:1: parse error in VERSION script
collect2: ld returned 1 exit status
make[3]: *** [libpq.so.5.1] Error 1
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 8.3.3 Complie issue
Tom Lane wrote: > "Harvey, Allan AC" <[EMAIL PROTECTED]> writes: > > Compile issue I was hoping someone might have a suggestion for: > > > make[3]: Entering directory > > `/removeExt2/postgresql-8.3.3/src/backend/utils/mb/conversion_procs/utf8_and_euc_jis_2004' > > make[3]: *** No rule to make target `utf8_and_euc_jis_2004.o', needed by > > `libutf8_and_euc_jis_2004.so.0.0'. Stop. > > I think we've seen this before: do you have utf8_and_euc_jis_2004.c > in that directory? There seem to be some broken versions of tar out > there that can't deal with extracting such a long file name from the > distribution tarball. Midnight Commander seems to cause this too. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Out of memry with large result set
Hello, I am doing a query via psql on a huge database, and I want to have its output piped to other unix processes. As the result set is very big, I've got: "out of memory for query result". How can I manage that, without playing with cursors, as I do not want to change the sql query ? Under mysql, I have the same issue, but by using: mysql -quick, I have what I want. Is there something equivalent under postgresql ? Or should I use another clients ? Thanks, Olivier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Installing PostgreSQL without using CygWin
Hi, I would like to know if there is a way to install PostgreSQL in Windows NT Server 2003 without installing CygWin. The client insists on not installing CygWin. Please help. :D Thanks. Ken Andrew Send instant messages to your online friends http://uk.messenger.yahoo.com
[GENERAL] Inconsistency with stemming/stop words in Tsearch2
Hi, having an issue with Tsearch2 and how stop words lexemes are
sometimes being utilized and sometimes not. I would expect the
behavior for to_tsquery for the three variations of "what", "what's"
and "whats" to be consistent (using 'en_stem') and for all variations
to be ignored since they all result in a stop word of "what".
However, this is not the case as to_tsquery("whats") returns the stop
word "what" as a result. Even more confusing is that if one were to
look at the lexize results below, they are inconsistent with the
to_tsquery results below. This seems like a bug to me.
goodrec_2=# select lexize('en_stem', 'what''s');
lexize
{what}
goodrec_2=# select lexize('en_stem', 'whats');
lexize
{what}
goodrec_2=# select lexize('en_stem', 'what');
lexize
{}
goodrec_2=# select to_tsquery('what''s');
NOTICE: query contains only stopword(s) or doesn't contain lexeme
(s), ignored
to_tsquery
goodrec_2=# select to_tsquery('whats');
to_tsquery
'what'
goodrec_2=# select to_tsquery('what');
NOTICE: query contains only stopword(s) or doesn't contain lexeme
(s), ignored
Re: [GENERAL] 8.3.3 Complie issue
"Harvey, Allan AC" <[EMAIL PROTECTED]> writes: > Compile issue I was hoping someone might have a suggestion for: > make[3]: Entering directory > `/removeExt2/postgresql-8.3.3/src/backend/utils/mb/conversion_procs/utf8_and_euc_jis_2004' > make[3]: *** No rule to make target `utf8_and_euc_jis_2004.o', needed by > `libutf8_and_euc_jis_2004.so.0.0'. Stop. I think we've seen this before: do you have utf8_and_euc_jis_2004.c in that directory? There seem to be some broken versions of tar out there that can't deal with extracting such a long file name from the distribution tarball. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] FAQ correction for Windows 2000/XP
Bruce Momjian escribió: > Dan Dascalescu wrote: > > I'd like to submit a correction for question "2.1) How do I setup a > > datasource?" in the FAQ. The existing text reads: > > > > "For Windows, use the ODBC Administrator in Control Panel. Here you > > can add, modify, or delete data sources." > > > > On Windows XP, however, there is no Control Panel applet for ODBC > > management. The answer should read: > > > > "Go to Programs -> Administrative Tools -> Data Sources and Add the > > PostgreSQL Unicode driver". > > Uh, I am not sure where you saw this FAQ but this is not an FAQ we > manage. Where did you see it? http://psqlodbc.projects.postgresql.org/faq.html#2.1 -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 8.3.3 Complie issue
Hi All, Compile issue I was hoping someone might have a suggestion for: Did ./configure --prefix /removeExt2/pgsql8.3 make make[3]: Entering directory `/removeExt2/postgresql-8.3.3/src/backend/utils/mb/conversion_procs/utf8_and_euc_jis_2004' make[3]: *** No rule to make target `utf8_and_euc_jis_2004.o', needed by `libutf8_and_euc_jis_2004.so.0.0'. Stop. make[3]: Leaving directory `/removeExt2/postgresql-8.3.3/src/backend/utils/mb/conversion_procs/utf8_and_euc_jis_2004' Allan 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] FAQ correction for Windows 2000/XP
Dan Dascalescu wrote: > I'd like to submit a correction for question "2.1) How do I setup a > datasource?" in the FAQ. The existing text reads: > > "For Windows, use the ODBC Administrator in Control Panel. Here you > can add, modify, or delete data sources." > > On Windows XP, however, there is no Control Panel applet for ODBC > management. The answer should read: > > "Go to Programs -> Administrative Tools -> Data Sources and Add the > PostgreSQL Unicode driver". Uh, I am not sure where you saw this FAQ but this is not an FAQ we manage. Where did you see it? -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cause of error message?
Bayless Kirtley wrote: An old app is giving a new error message. I query a table and make one update. Later I come back to query the table again and get this error: ERROR: current transaction is aborted, commands ignored until end of transaction block The error only occurs after I have made an update. As long as no updates, I can query multiple times. Can anyone give me an idea of what sort of actions or errors usually cause this error? It means that you have an open transaction and the statement before you get this message failed. e.g. postgres=# begin; BEGIN postgres=# update bob set abc=1; ERROR: relation "bob" does not exist postgres=# select version(); ERROR: current transaction is aborted, commands ignored until end of transaction block postgres=# You need to find which statement failed first and fix it or rollback and have your app deal with the failure. The postgres log is a good place to start looking. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cause of error message?
On Mon, 2008-07-14 at 19:44 -0500, Bayless Kirtley wrote: > An old app is giving a new error message. I query a table and make one > update. Later I come back to query the table again and get this error: > > ERROR: current transaction is aborted, commands ignored until end of > transaction block > > The error only occurs after I have made an update. As long as no > updates, I can query multiple times. Can anyone give me an idea of > what sort of actions or errors usually cause this error? > > Thanks, > Bayless > I believe that this might be caused by a syntax error. Do you have any error handling code that might be hiding the error from you? -Mark -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Cause of error message?
An old app is giving a new error message. I query a table and make one update. Later I come back to query the table again and get this error: ERROR: current transaction is aborted, commands ignored until end of transaction block The error only occurs after I have made an update. As long as no updates, I can query multiple times. Can anyone give me an idea of what sort of actions or errors usually cause this error? Thanks, Bayless
Re: [GENERAL] plperl installation
On 15/07/2008, JD Wong <[EMAIL PROTECTED]> wrote: > Hey all I'm want to use plperl but directory does not exist and I did not > specifically enable it during installation. -Does anybody know how to > install it post-install? Which OS is this on? If it's Linux, which distro? And how did you install, from source? > Thanks > -JD Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] vacuum taking an unusually long time
On Mon, Jul 14, 2008 at 3:08 PM, Mason Hale <[EMAIL PROTECTED]> wrote: > Vacuum operations on several tables are taking much longer than they > previously were. > We currently have 3 autovacuum processes that have been running more than 3 > days each. > The tables are large (between 40 and 90GB each). > Postgresql version is 8.3.1 > maintenance_work_mem is 512MB (on a 32GB server). > Any ideas what would make vacuum take so long? > What can I do to speed things up? Have you adjusted your vacuum / autovacuum cost parameters up? that will certainly slow down vacuums. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] plperl installation
Hey all I'm want to use plperl but directory does not exist and I did not specifically enable it during installation. -Does anybody know how to install it post-install? Thanks -JD
Re: [GENERAL] statement timeout and pg_dump
Scott Marlowe wrote: > On Mon, Jul 14, 2008 at 1:02 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > > "Scott Marlowe" <[EMAIL PROTECTED]> writes: > >> I just got hoisted by my own petard when a pg_dump failed due to > >> statement timeout and I didn't notice because I was running the dump > >> nohup and didn't read the nohup.out file to see the errors. It wasn't > >> a big deal, the data wasn't critical operational data or anything. > > > >> But it got me to thinking that maybe pg_dump should disable > >> statement_timeout when it runs. > > > > You mean like this? > > > > http://archives.postgresql.org/pgsql-committers/2008-05/msg00026.php > > Very cool. I assume it's not in 8.3.3 then? No, because this is a functionality change, rather than a clear bug. -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] vacuum taking an unusually long time
Vacuum operations on several tables are taking much longer than they previously were.We currently have 3 autovacuum processes that have been running more than 3 days each. The tables are large (between 40 and 90GB each). Postgresql version is 8.3.1 maintenance_work_mem is 512MB (on a 32GB server). Any ideas what would make vacuum take so long? What can I do to speed things up? thanks, - Mason
Re: [GENERAL] SPACE FOR POSTGRESQL DATABASE
Scott Marlowe wrote: > On Thu, Jul 10, 2008 at 8:18 AM, aravind chandu <[EMAIL PROTECTED]> wrote: > > Hello, > > > > Can you please how much space does postgresql database occupies? > > A good rule of thumb is that it will take about 1.5 times as much > space in the db as it does in a flat file. that's just an > approximation. The actual size of each type etc is defined in the > other links in this thread. Folks, we have an FAQ about this: http://www.postgresql.org/docs/faqs.FAQ.html#item4.5 -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] optimizer ignoring primary key and doing sequence scan
On Mon, Jul 14, 2008 at 1:54 PM, Chris Hoy <[EMAIL PROTECTED]> wrote: > > Hi > > I have a number of tables in my database where the queries appear to > ignoring the primary key and doing a seq scan instead, however other tables > appear to be fine. I can see any difference between them. > > Is their any way of determination why the otimizer isn't picking up the > primary key? > > Version 8.3.3 windows > > An example of a non working table is: > > select * from industries where industryid = 1; > "Seq Scan on industries (cost=0.00..1.02 rows=1 width=116) (actual > time=0.011..0.013 rows=1 loops=1)" According to this there's only one row in the table. why WOULD postgresql use an index when it can just scan the one row table in a split second. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] optimizer ignoring primary key and doing sequence scan
Hi I have a number of tables in my database where the queries appear to ignoring the primary key and doing a seq scan instead, however other tables appear to be fine. I can see any difference between them. Is their any way of determination why the otimizer isn't picking up the primary key? Version 8.3.3 windows An example of a non working table is: select * from industries where industryid = 1; "Seq Scan on industries (cost=0.00..1.02 rows=1 width=116) (actual time=0.011..0.013 rows=1 loops=1)" CREATE TABLE industries ( industryid serial NOT NULL, industryname character varying(45) NOT NULL, prn_key integer, CONSTRAINT pk_industry PRIMARY KEY (industryid), CONSTRAINT un_industry UNIQUE (industryname) ) A example of woring one explain select * from currencies where currencyid = 1 "Index Scan using pk_currencyid on currencies (cost=0.00..8.27 rows=1 width=196)" CREATE TABLE currencies ( currencyid serial NOT NULL, currencyname character varying(85) NOT NULL, prn_key integer, CONSTRAINT pk_currencyid PRIMARY KEY (currencyid) ) Chris No virus found in this outgoing message. Checked by AVG - http://www.avg.com Version: 8.0.138 / Virus Database: 270.4.10/1551 - Release Date: 14/07/2008 06:49 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Feature: FOR UPDATE SKIP LOCKED
On Jul 8, 2008, at 7:49 PM, Goldeneye Solutions Information wrote: I’m been reading up on FOR UPDATE NOWAIT and it looks like It was added in 8.1. How difficult is it to add FOR UPDATE SKIP LOCKED or something similar? (basically skip locked rows / oracle syntax) More background here: http://forge.mysql.com/worklog/task.php?id=3597 It would be quite useful to implement a database queue. Although FOR UPDATE NOWAIT and trying again can work as well as other techniques, just skipping over the locks has its advantages (simplicity and zero wait) Patches welcome. :) You could likely use the patch that added NOWAIT as a template and be pretty close to something. You should bounce the idea off of -hackers first if you want to do this. If you're not looking to hack the backend code, I'd suggest doing a random OFFSET in your select. You'd need to first do a select to pick a row, then try to actually lock it. You could also have an old record stick around a long time that way, so I'd suggest forcibly trying OFFSET 0 on some non-trivial number of attempts (say 10%). You might be able to achieve the same effect by applying a function to your random number that pushes it towards 0. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] statement timeout and pg_dump
On Mon, Jul 14, 2008 at 1:02 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Scott Marlowe" <[EMAIL PROTECTED]> writes: >> I just got hoisted by my own petard when a pg_dump failed due to >> statement timeout and I didn't notice because I was running the dump >> nohup and didn't read the nohup.out file to see the errors. It wasn't >> a big deal, the data wasn't critical operational data or anything. > >> But it got me to thinking that maybe pg_dump should disable >> statement_timeout when it runs. > > You mean like this? > > http://archives.postgresql.org/pgsql-committers/2008-05/msg00026.php Very cool. I assume it's not in 8.3.3 then? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] statement timeout and pg_dump
"Scott Marlowe" <[EMAIL PROTECTED]> writes: > I just got hoisted by my own petard when a pg_dump failed due to > statement timeout and I didn't notice because I was running the dump > nohup and didn't read the nohup.out file to see the errors. It wasn't > a big deal, the data wasn't critical operational data or anything. > But it got me to thinking that maybe pg_dump should disable > statement_timeout when it runs. You mean like this? http://archives.postgresql.org/pgsql-committers/2008-05/msg00026.php regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] UPDATE runs slow in a transaction
Viktor Rosenfeld <[EMAIL PROTECTED]> writes: > the script below runs very fast when executed alone. But when I call > it from within a transaction block it's so slow that I have to abort > it after a while. Specifically the second-to-last UPDATE seems to > take forever within a transaction while it completes in about 3 > seconds outside a transaction. Since the table you're working on was just created in the same transaction, there's been no opportunity for autovacuum to run an ANALYZE on it; that's probably preventing selection of a good plan. Try throwing in an "ANALYZE tmp" after you load the table. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to remove the possibility to search the parts of a compound word with hyphen
"Fco. Mario Barcala" =?ISO-8859-1?Q?Rodr=EDguez?= <[EMAIL PROTECTED]> writes: > Is it possible to remove the possibility of searching the parts of a > word which include a hyphen? I know that the default parser identifies > all possibilities (the compound word and the individual part ones), but > I would like to make possible to search only for the compound word, but > not for the parts. Sure, just set up your text search configuration so that word-parts are discarded instead of being passed through to the dictionary(s). regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] statement timeout and pg_dump
I just got hoisted by my own petard when a pg_dump failed due to statement timeout and I didn't notice because I was running the dump nohup and didn't read the nohup.out file to see the errors. It wasn't a big deal, the data wasn't critical operational data or anything. But it got me to thinking that maybe pg_dump should disable statement_timeout when it runs. Any opinions on this? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [pdxpug] Pg booth staffing at OSCON
> I'm wondering how much of the conference we have access to with an > exhibitor badge? Obviously not tutorials, but what about regular > technical sessions? You get into the exhibit hall when it is closed to regular attendees. You get lunch. I'm not sure that there are any perks beyond that. -- teknotus Take Notice (503) 409-1735 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [pdxpug] Pg booth staffing at OSCON
>-Original Message- >From: [EMAIL PROTECTED] >[mailto:[EMAIL PROTECTED] On Behalf Of Daniel Johnson >Sent: Monday, July 14, 2008 10:07 AM >To: gabrielle >Cc: [EMAIL PROTECTED]; >pgsql-general@postgresql.org; [EMAIL PROTECTED] >Subject: Re: [pdxpug] Pg booth staffing at OSCON > >On Sun, Jul 13, 2008 at 4:31 PM, gabrielle <[EMAIL PROTECTED]> wrote: >> OSCON is fast approaching - just one more week! >> >> I need some people to help out with booth staffing for >Thursday. Sign up here: >> http://wiki.postgresql.org/wiki/Oscon_2008_signup >> >> Staffing the booth is a ton o' fun and a great way to meet people! > >So what info will you need to get us on the exhibitor list? > >I'm starting a new job this week, but they said I can get the OSCON >days off so I can fill in the booth for many of the empty spots. I >don't want to do the booth during the keynotes (and what would be the >point anyways no one will be in the exhibitor hall), and I'd kinda >like to schedule it around when things of interest are happening in >OSCAMP, but who knows what that schedule will be until the conference >actually starts. > >-- >Sent via pdxpug mailing list ([EMAIL PROTECTED]) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pdxpug > I'm wondering how much of the conference we have access to with an exhibitor badge? Obviously not tutorials, but what about regular technical sessions? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to remove the possibility to search the parts of a compound word with hyphen
Hi all: I have been searching the mailing lists about hyphens but I coudn't find the answer to this question: Is it possible to remove the possibility of searching the parts of a word which include a hyphen? I know that the default parser identifies all possibilities (the compound word and the individual part ones), but I would like to make possible to search only for the compound word, but not for the parts. Using one example of the parsers section in documentation, i would like to make possible to search lógico-matemática but not lógico or matemática individually. Would have I to write a new parser or are there any configuration issues to do this? Thanks, Mario Barcala -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [pdxpug] Pg booth staffing at OSCON
On Sun, Jul 13, 2008 at 4:31 PM, gabrielle <[EMAIL PROTECTED]> wrote: > OSCON is fast approaching - just one more week! > > I need some people to help out with booth staffing for Thursday. Sign up here: > http://wiki.postgresql.org/wiki/Oscon_2008_signup > > Staffing the booth is a ton o' fun and a great way to meet people! So what info will you need to get us on the exhibitor list? I'm starting a new job this week, but they said I can get the OSCON days off so I can fill in the booth for many of the empty spots. I don't want to do the booth during the keynotes (and what would be the point anyways no one will be in the exhibitor hall), and I'd kinda like to schedule it around when things of interest are happening in OSCAMP, but who knows what that schedule will be until the conference actually starts. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] NEAR operator
Hello look to fuzzystrmatch module http://www.postgresql.org/docs/8.3/interactive/fuzzystrmatch.html Regards Pavel 2008/7/14 Fco. Mario Barcala Rodríguez <[EMAIL PROTECTED]>: > Is there in PostgreSQL something like the full-text NEAR operator of > Oracle? That is, to obtain the documents which include two words > separated by less than an specified numbers of words (distance). > > I didn't find anything related with this issue inside the documentation > or mailing list archives. > > Thanks, > > Mario Barcala > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] UPDATE runs slow in a transaction
Hi, the script below runs very fast when executed alone. But when I call it from within a transaction block it's so slow that I have to abort it after a while. Specifically the second-to-last UPDATE seems to take forever within a transaction while it completes in about 3 seconds outside a transaction. The Postgres manual states that statements should ran faster within a transaction. I wonder if I have to up a configuration parameter to give Postgres more resources or if the problem lies elsewhere. Thanks for your help, Viktor The script: -- add columns left_token, right_token and copy values from token_index ALTER TABLE _struct ADD left_token integer; ALTER TABLE _struct ADD right_token integer; UPDATE _struct SET left_token = token_index; UPDATE _struct SET right_token = token_index; -- set left, right values for non-terminals -- (use temporary table to get rid of joins between struct and rank) CREATE TABLE tmp AS SELECT r.pre, r.post, s.id, s.left_token, s.right_token FROM _rank r, _struct s WHERE r.struct_ref = s.id; CREATE INDEX idx_tmp_pre_post ON tmp (pre, post); UPDATE tmp SET left_token = (SELECT min(t2.left_token) FROM tmp t2 WHERE t2.pre >= tmp.pre AND t2.pre <= tmp.post); UPDATE tmp SET right_token = (SELECT max(t2.right_token) FROM tmp t2 WHERE t2.pre >= tmp.pre AND t2.pre <= tmp.post); -- copy left, right values for everything CREATE INDEX tmp_id ON tmp (id); UPDATE _struct SET left_token = (SELECT DISTINCT left_token FROM tmp WHERE _struct.id = tmp.id); -- the UPDATE above takes ages when called within a transaction UPDATE _struct SET right_token = (SELECT DISTINCT right_token FROM tmp WHERE _struct.id = tmp.id); -- clean up DROP TABLE tmp; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] lo_import invalid large object descriptor
Markus Mehrwald <[EMAIL PROTECTED]> writes: > I tried to insert an image in my database with lo_import. The error I > get is "invalid large object descriptor: 0". lo_import has to be called within a transaction block (BEGIN/COMMIT). The documentation is not very clear about that :-( (Actually, rather than fix the documentation we probably ought to fix the code --- there's enough infrastructure in libpq now that it could tell whether it needs to start its own transaction or not.) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] NEAR operator
Is there in PostgreSQL something like the full-text NEAR operator of Oracle? That is, to obtain the documents which include two words separated by less than an specified numbers of words (distance). I didn't find anything related with this issue inside the documentation or mailing list archives. Thanks, Mario Barcala -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] lo_import invalid large object descriptor
Hello, I tried to insert an image in my database with lo_import. The error I get is "invalid large object descriptor: 0". If I try to create an oid I get a valid one but with lo_import it seems not to work. The connection is ok as well. The folloing code produces the output after the code. if (PQstatus(connDB) == CONNECTION_OK) std::cout << "CONNECTION_OK" << std::endl; Oid oid = lo_creat(connDB, INV_READ | INV_WRITE); std::cout << oid << std::endl; std::cout << PQerrorMessage(connDB) << std::endl; std::cout << lo_import(connDB, "/tmp/image.vtk") << std::endl; std::cout << PQerrorMessage(connDB) << std::endl; Output: CONNECTION_OK 25211 0 FEHLER: ungültiger Large-Object-Deskriptor: 0 Here is the version string of my db: PostgreSQL 8.3.3 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.3.0 20080428 (Red Hat 4.3.0-8) Can anyone tell me please what is wrong? Thank you, Markus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installing PostgreSQL without using CygWin
On 14/07/2008 10:47, ken andrew wrote: I would like to know if there is a way to install PostgreSQL in Windows NT Server 2003 without installing CygWin. The client insists on not installing CygWin. Sure - there's been a native Win32 port for quite some time now. You can compile from source or use a Windows Installer package - follow the "binaries" link from the main website. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Installing PostgreSQL without using CygWin
Hi, I would like to know if there is a way to install PostgreSQL in Windows NT Server 2003 without installing CygWin. The client insists on not installing CygWin. Please help. :D Thanks. Ken Andrew Send instant messages to your online friends http://uk.messenger.yahoo.com
