[GENERAL] Varchar vs text
Is there a real difference between a varchar without a specified length and the text datatype?
Re: [GENERAL] Varchar vs text
The reason I ask is because the documentation says If character varying is used without length specifier, the type accepts strings of any size. The latter is a PostgreSQL extension. I wasn't sure if such an extension meant there was a level of over-head involved, or reduced its indexability. 2009/1/27 Richard Huxton d...@archonet.com Thom Brown wrote: Is there a real difference between a varchar without a specified length and the text datatype? No. Wish all questions were that easy :-) -- Richard Huxton Archonet Ltd
[GENERAL] Text search with ispell
I'm trying to figure out how to use PostgreSQL's fulltext search with an ispell dictionary. I'm having a bit of trouble figuring out where this norwegian.dict comes from though. When I install the norwegian ispell dictionary, i get 4 files, nb.aff, nb.hash, nn.aff and nn.hash. What I'm unable to figure out, is the steps needed to use this for PostgreSQL? -- Tommy -- 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] Text search with ispell
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tommy Gildseth schrieb: I'm trying to figure out how to use PostgreSQL's fulltext search with an ispell dictionary. I'm having a bit of trouble figuring out where this norwegian.dict comes from though. When I install the norwegian ispell dictionary, i get 4 files, nb.aff, nb.hash, nn.aff and nn.hash. What I'm unable to figure out, is the steps needed to use this for PostgreSQL? Which version are you running? It's important to know, because tsearch2 is integrated since version 8.3. The behaviour for implementing in earlier versions is therefore different ... Cheers Andy - -- St.Pauli - Hamburg - Germany Andreas Wenk -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFJfum6Va7znmSP9AwRAlN4AJ9odanCrD3R+gMzb7yzJjXWEKfCUACeN1Tv SmVDeFa6xemj53T2cMUFoyM= =khkB -END PGP SIGNATURE- -- 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] Varchar vs text
Thom Brown wrote: The reason I ask is because the documentation says If character varying is used without length specifier, the type accepts strings of any size. The latter is a PostgreSQL extension. I wasn't sure if such an extension meant there was a level of over-head involved, or reduced its indexability. No - some other DBs might have different implementations (and so restrictions) but varchar and text are basically interchangeable. They are different types though, so if you add custom casts you might want to do it for both. There is a limit on btree index size - about 2000 chars if I remember correctly. That's because you need to fit three index values on an 8KB page iirc (and there are overheads too). -- Richard Huxton Archonet Ltd -- 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] Text search with ispell
Andreas Wenk wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tommy Gildseth schrieb: I'm trying to figure out how to use PostgreSQL's fulltext search with an ispell dictionary. I'm having a bit of trouble figuring out where this norwegian.dict comes from though. When I install the norwegian ispell dictionary, i get 4 files, nb.aff, nb.hash, nn.aff and nn.hash. What I'm unable to figure out, is the steps needed to use this for PostgreSQL? Which version are you running? It's important to know, because tsearch2 is integrated since version 8.3. The behaviour for implementing in earlier versions is therefore different ... It will be running on version 8.3 -- Tommy Gildseth -- 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] Text search with ispell
On Tue, 27 Jan 2009, Tommy Gildseth wrote: I'm trying to figure out how to use PostgreSQL's fulltext search with an ispell dictionary. I'm having a bit of trouble figuring out where this norwegian.dict comes from though. When I install the norwegian ispell dictionary, i get 4 files, nb.aff, nb.hash, nn.aff and nn.hash. What I'm unable to figure out, is the steps needed to use this for PostgreSQL? you need to make a choice between two kinds of norwegian language - nn, nb, see http://en.wikipedia.org/wiki/Norwegian_language Then follow standard procedure described in documentation. Where did you get them ? Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- 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] Varchar vs text
I see. Thanks for clarifying! Thom 2009/1/27 Richard Huxton d...@archonet.com Thom Brown wrote: The reason I ask is because the documentation says If character varying is used without length specifier, the type accepts strings of any size. The latter is a PostgreSQL extension. I wasn't sure if such an extension meant there was a level of over-head involved, or reduced its indexability. No - some other DBs might have different implementations (and so restrictions) but varchar and text are basically interchangeable. They are different types though, so if you add custom casts you might want to do it for both. There is a limit on btree index size - about 2000 chars if I remember correctly. That's because you need to fit three index values on an 8KB page iirc (and there are overheads too). -- Richard Huxton Archonet Ltd
[GENERAL] plpython infinite loop
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I'm using posgresql 8.2.4 on Gentoo Linux. Is there anything that can be done about an infinite loop in plpython? I tired stopping the query (with PG Admin), but the process kept going on the server. I tried pg_cancel_backend but it kept running. I tried kill -INT and it ignored that as well. I found a response by Tom Lane from Oct. 3 2007, subject: Re: [GENERAL] pg_cancel_backend() does not work with buzz queries where he suggested the solution: Short answer: don't write infinite loops in plpython The query was a simple loop that was programmed by accident. It is using 100% of CPU. Is there a better way then restarting the server? Is this fixed in a more current version? Thanks Sim -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkl+53MACgkQjDX6szCBa+owmQCfWxyVBZdnMW3zSUqbatl83wrD 3vcAoJLrYJE8cjCkcWTJEgmkxL7U33sO =qHrx -END PGP SIGNATURE- -- 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] Text search with ispell
Oleg Bartunov wrote: On Tue, 27 Jan 2009, Tommy Gildseth wrote: I'm trying to figure out how to use PostgreSQL's fulltext search with an ispell dictionary. I'm having a bit of trouble figuring out where this norwegian.dict comes from though. When I install the norwegian ispell dictionary, i get 4 files, nb.aff, nb.hash, nn.aff and nn.hash. What I'm unable to figure out, is the steps needed to use this for PostgreSQL? you need to make a choice between two kinds of norwegian language - nn, nb, see http://en.wikipedia.org/wiki/Norwegian_language Then follow standard procedure described in documentation. Where did you get them ? Yes, I'm aware of that I need to choose one of those. I guess what I'm having problems with, is figuring out where the language.dict file comes from. I didn't find any such file in the rpm downloaded from the links at http://ficus-www.cs.ucla.edu/geoff/ispell.html#ftp-sites and also not in the inorwegian-package in the ubuntu apt repository. I have read through http://www.postgresql.org/docs/current/static/textsearch.html, but it's not quite clear to me, from that, what I need to do, to use an ispell dictionary with tsearch. -- Tommy Gildseth -- 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] Text search with ispell
Have you read http://www.postgresql.org/docs/current/static/textsearch-dictionaries.html#TEXTSEARCH-ISPELL-DICTIONARY We suggest to use dictionaries which come with openoffice, hunspell, probably has better support of composite words. On Tue, 27 Jan 2009, Tommy Gildseth wrote: Oleg Bartunov wrote: On Tue, 27 Jan 2009, Tommy Gildseth wrote: I'm trying to figure out how to use PostgreSQL's fulltext search with an ispell dictionary. I'm having a bit of trouble figuring out where this norwegian.dict comes from though. When I install the norwegian ispell dictionary, i get 4 files, nb.aff, nb.hash, nn.aff and nn.hash. What I'm unable to figure out, is the steps needed to use this for PostgreSQL? you need to make a choice between two kinds of norwegian language - nn, nb, see http://en.wikipedia.org/wiki/Norwegian_language Then follow standard procedure described in documentation. Where did you get them ? Yes, I'm aware of that I need to choose one of those. I guess what I'm having problems with, is figuring out where the language.dict file comes from. I didn't find any such file in the rpm downloaded from the links at http://ficus-www.cs.ucla.edu/geoff/ispell.html#ftp-sites and also not in the inorwegian-package in the ubuntu apt repository. I have read through http://www.postgresql.org/docs/current/static/textsearch.html, but it's not quite clear to me, from that, what I need to do, to use an ispell dictionary with tsearch. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- 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] dblink syntax question for remotely invoking void-returning procedures
Thank you, Merlin and Asko for your responses :-) Because my whole project
is using PL/Proxy I should have realised I could use that instead of dblink
in this case.
Thanks again,
- Ian
2009/1/26 Asko Oja asc...@gmail.com
Take a stab at plproxy if you want to remotly call functions. Should be
much better suited than dblink for that.
Regards
Asko
On Mon, Jan 26, 2009 at 4:07 PM, Merlin Moncure mmonc...@gmail.comwrote:
On 1/24/09, Ian Sollars ian.soll...@gmail.com wrote:
Hello everyone,
I've got some questions about dblink that I couldn't find answers to in
the
documentation. Any help would be much appreciated.
I need to invoke a function on a remote server that returns either void
or
text, and I'm trying to find a nice way of doing it.
did you try this?
perform * from dblink('test', 'select inserttest()') as t1(test text);
Anyways, I never write void returning functions. Another problem with
them (for example) is that they can't be called using the binary query
protocol. These are minor nits since the workaround is easy.
merlin
--
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 on Windows 7 Beta Build 7000 64bit - first results
On Tue, 27 Jan 2009 16:10:26 +0900, ITAGAKI Takahiro wrote: Harald Armin Massa haraldarminma...@gmail.com wrote: 4.) it seems to be no longer cool to have your data below Program Files / Program Files (x86) That was never cool or good practice. I'm not sure which directory should we create $PGDATA -- My Documents, Application Data, Local Settings or direct child of %USERPROFILE%, but anyway some of them would be better than Program Files. %USERNAME%/%APPDATA% These variables exist for good reasons.. regards Holger -- 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 on Windows 7 Beta Build 7000 64bit - first results
Holger Hoffstaette wrote: On Tue, 27 Jan 2009 16:10:26 +0900, ITAGAKI Takahiro wrote: I'm not sure which directory should we create $PGDATA -- My Documents, Application Data, Local Settings or direct child of %USERPROFILE%, but anyway some of them would be better than Program Files. %USERNAME%/%APPDATA% These variables exist for good reasons.. Yes. For user apps. They pretty much suck for services. //Magnus -- 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 on Windows 7 Beta Build 7000 64bit - first results
Holger, 4.) it seems to be no longer cool to have your data below Program Files / Program Files (x86) That was never cool or good practice. Yes, we discussed that on pg-installer some time ago. But we were in good bad company, as Microsoft SQL Server and Exchange did similiar things. Quod licet iovi I'm not sure which directory should we create $PGDATA -- My Documents, Application Data, Local Settings or direct child of %USERPROFILE%, but anyway some of them would be better than Program Files. %USERNAME%/%APPDATA% The default for bigger data seems to be \progdata on Windows 7. That is from observation only, as i.e. Ubisoft installs there. Not clear which %XX% that is... Nontheless: I usually pick my own data directory, as there have to be special provisions made concerning backup and viralscanners. THE MORE hurting problem on Windows7 is the inability to start the postgresql service. There must have been some changes to the service framework / security security which prevend the startup... Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - EuroPython 2009 will take place in Birmingham - Stay tuned! -- 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 on Windows 7 Beta Build 7000 64bit - first results
On Tue, Jan 27, 2009 at 12:18 PM, Harald Armin Massa haraldarminma...@gmail.com wrote: THE MORE hurting problem on Windows7 is the inability to start the postgresql service. There must have been some changes to the service framework / security security which prevend the startup... I posted a patch for that to -hackers a couple of hours ago. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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 on Windows 7 Beta Build 7000 64bit - first results
Harald Armin Massa wrote: THE MORE hurting problem on Windows7 is the inability to start the postgresql service. There must have been some changes to the service framework / security security which prevend the startup... Dave Page just posted a patch for that, so we'll hopefully have it fixed RSN. //Magnus -- 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 on Windows 7 Beta Build 7000 64bit - first results
Holger Hoffstaette wrote: On Tue, 27 Jan 2009 13:17:30 +0100, Magnus Hagander wrote: Holger Hoffstaette wrote: On Tue, 27 Jan 2009 16:10:26 +0900, ITAGAKI Takahiro wrote: I'm not sure which directory should we create $PGDATA -- My Documents, Application Data, Local Settings or direct child of %USERPROFILE%, but anyway some of them would be better than Program Files. %USERNAME%/%APPDATA% These variables exist for good reasons.. Yes. For user apps. They pretty much suck for services. Wah? Please explain. I was under the impression that a service running under some account has access to its profile and all the assorted things that come with it. Is this not the case? It does - in theory. It doesn't always work. For example, consider what happens when your files are on a roaming profile. And yes, profiles can more or less randomly move between being roaming and local-copy in my experience... And you really don't want that for your database files. //Magnus -- 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] Text search with ispell
Oleg Bartunov wrote: Have you read http://www.postgresql.org/docs/current/static/textsearch-dictionaries.html#TEXTSEARCH-ISPELL-DICTIONARY We suggest to use dictionaries which come with openoffice, hunspell, probably has better support of composite words. Thanks, that knocked me onto the right track. To easy to miss the blindingly obvious at times. :-) Works beautifully now. -- Tommy Gildseth -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] VDI of pglive distro
Hi for all! It's aviable to download de VDI image of the distro pglive[1] made by R. Bernier. To use it, you only need a VirtualBox running on your machine. It have more features like pyreplica, pgdevel84 (one of the last snapshots), bucardo on others. The link is http://www.quesapa.com.ar/pgsql.vid.gz [1] http://www.pglive.info -- Emanuel Calvo Franco ArPUG / AOSUG Member Postgresql Support Admin -- 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 on Windows 7 Beta Build 7000 64bit - first results
On Tue, 27 Jan 2009 13:17:30 +0100, Magnus Hagander wrote: Holger Hoffstaette wrote: On Tue, 27 Jan 2009 16:10:26 +0900, ITAGAKI Takahiro wrote: I'm not sure which directory should we create $PGDATA -- My Documents, Application Data, Local Settings or direct child of %USERPROFILE%, but anyway some of them would be better than Program Files. %USERNAME%/%APPDATA% These variables exist for good reasons.. Yes. For user apps. They pretty much suck for services. Wah? Please explain. I was under the impression that a service running under some account has access to its profile and all the assorted things that come with it. Is this not the case? thanks Holger -- 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 on Windows 7 Beta Build 7000 64bit - first results
On Tue, Jan 27, 2009 at 1:16 PM, Magnus Hagander mag...@hagander.net wrote: For example, consider what happens when your files are on a roaming profile. And yes, profiles can more or less randomly move between being roaming and local-copy in my experience... And you really don't want that for your database files. It can also be more awkward from a management POV - it ties the cluster more tightly to that service user account (with no gain in security), and makes it more awkward for admins to get at the config files certificates etc. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- 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] Text search with ispell
Tommy Gildseth wrote:
Oleg Bartunov wrote:
Have you read
http://www.postgresql.org/docs/current/static/textsearch-dictionaries.html#TEXTSEARCH-ISPELL-DICTIONARY
We suggest to use dictionaries which come with openoffice, hunspell,
probably
has better support of composite words.
Thanks, that knocked me onto the right track. To easy to miss the
blindingly obvious at times. :-)
Works beautifully now.
I may have been to quick to declare success.
The following works as expected, returning the individual words:
SELECT
ts_debug('norwegian', 'overbuljongterningpakkmesterassistent'),
ts_debug('norwegian', 'sjokoladefabrikk'),
ts_debug('norwegian', 'epleskrott');
-[ RECORD 1
]--
ts_debug | (asciiword,Word, all
ASCII,overbuljongterningpakkmesterassistent,{no_ispell,norwegian_stem},no_ispell,{buljong,terning,pakk,mester,assistent})
ts_debug | (asciiword,Word, all
ASCII,sjokoladefabrikk,{no_ispell,norwegian_stem},no_ispell,{sjokoladefabrikk,sjokolade,fabrikk})
ts_debug | (asciiword,Word, all
ASCII,epleskrott,{no_ispell,norwegian_stem},no_ispell,{epleskrott,eple,skrott})
But, the following does not:
SELECT
ts_debug('norwegian', 'hemsedalsdans'),
ts_debug('norwegian', 'lærdalsbrua'),
ts_debug('norwegian', 'hengesmykke');
-[ RECORD 1
]
ts_debug | (asciiword,Word, all
ASCII,hemsedalsdans,{no_ispell,norwegian_stem},norwegian_stem,{hemsedalsdan})
ts_debug | (word,Word, all
letters,lærdalsbrua,{no_ispell,norwegian_stem},norwegian_stem,{lærdalsbru})
ts_debug | (asciiword,Word, all
ASCII,hengesmykke,{no_ispell,norwegian_stem},norwegian_stem,{hengesmykk})
Would this be due to a limitation in the dictionary, or a
misconfiguration on my side?
Commands used are as follows:
CREATE TEXT SEARCH DICTIONARY no_ispell (
TEMPLATE = ispell,
DictFile = nb_NO,
AffFile = nb_NO,
StopWords = norwegian
);
and
ALTER TEXT SEARCH CONFIGURATION norwegian ALTER MAPPING FOR asciiword,
asciihword, hword_asciipart,word, hword, hword_part WITH no_ispell,
norwegian_stem;
--
Tommy Gildseth
--
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] Text search with ispell
On Tue, 27 Jan 2009, Tommy Gildseth wrote:
Tommy Gildseth wrote:
Oleg Bartunov wrote:
Have you read
http://www.postgresql.org/docs/current/static/textsearch-dictionaries.html#TEXTSEARCH-ISPELL-DICTIONARY
We suggest to use dictionaries which come with openoffice, hunspell,
probably
has better support of composite words.
Thanks, that knocked me onto the right track. To easy to miss the
blindingly obvious at times. :-)
Works beautifully now.
I may have been to quick to declare success.
The following works as expected, returning the individual words:
SELECT
ts_debug('norwegian', 'overbuljongterningpakkmesterassistent'),
ts_debug('norwegian', 'sjokoladefabrikk'),
ts_debug('norwegian', 'epleskrott');
-[ RECORD 1
]--
ts_debug | (asciiword,Word, all
ASCII,overbuljongterningpakkmesterassistent,{no_ispell,norwegian_stem},no_ispell,{buljong,terning,pakk,mester,assistent})
ts_debug | (asciiword,Word, all
ASCII,sjokoladefabrikk,{no_ispell,norwegian_stem},no_ispell,{sjokoladefabrikk,sjokolade,fabrikk})
ts_debug | (asciiword,Word, all
ASCII,epleskrott,{no_ispell,norwegian_stem},no_ispell,{epleskrott,eple,skrott})
But, the following does not:
SELECT
ts_debug('norwegian', 'hemsedalsdans'),
ts_debug('norwegian', 'l?rdalsbrua'),
ts_debug('norwegian', 'hengesmykke');
-[ RECORD 1
]
ts_debug | (asciiword,Word, all
ASCII,hemsedalsdans,{no_ispell,norwegian_stem},norwegian_stem,{hemsedalsdan})
ts_debug | (word,Word, all
letters,l?rdalsbrua,{no_ispell,norwegian_stem},norwegian_stem,{l?rdalsbru})
ts_debug | (asciiword,Word, all
ASCII,hengesmykke,{no_ispell,norwegian_stem},norwegian_stem,{hengesmykk})
Would this be due to a limitation in the dictionary, or a misconfiguration on
my side?
sorry, I don't know norwegian, what do you mean ? Did you complain that
no_ispell doesn't recognize these words ?
Commands used are as follows:
CREATE TEXT SEARCH DICTIONARY no_ispell (
TEMPLATE = ispell,
DictFile = nb_NO,
AffFile = nb_NO,
StopWords = norwegian
);
and
ALTER TEXT SEARCH CONFIGURATION norwegian ALTER MAPPING FOR asciiword,
asciihword, hword_asciipart,word, hword, hword_part WITH no_ispell,
norwegian_stem;
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
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] Varchar vs text
Thom Brown thombr...@gmail.com writes: The reason I ask is because the documentation says If character varying is used without length specifier, the type accepts strings of any size. The latter is a PostgreSQL extension. I wasn't sure if such an extension meant there was a level of over-head involved, or reduced its indexability. Extension means it's not in the SQL standard. It's not meant to imply anything about performance. There is some potential overhead from using varchar instead of text because of the extra dummy cast nodes that are likely to be present in your query expressions (since all the native functions are declared to take/return text, not varchar). In most cases I'd think you'd be hard-put to measure any difference though. 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] Text search with ispell
Oleg Bartunov wrote:
On Tue, 27 Jan 2009, Tommy Gildseth wrote:
Tommy Gildseth wrote:
Oleg Bartunov wrote:
Have you read
http://www.postgresql.org/docs/current/static/textsearch-dictionaries.html#TEXTSEARCH-ISPELL-DICTIONARY
We suggest to use dictionaries which come with openoffice, hunspell,
probably
has better support of composite words.
Thanks, that knocked me onto the right track. To easy to miss the
blindingly obvious at times. :-)
Works beautifully now.
I may have been to quick to declare success.
The following works as expected, returning the individual words:
SELECT
ts_debug('norwegian', 'overbuljongterningpakkmesterassistent'),
ts_debug('norwegian', 'sjokoladefabrikk'),
ts_debug('norwegian', 'epleskrott');
-[ RECORD 1
]--
ts_debug | (asciiword,Word, all
ASCII,overbuljongterningpakkmesterassistent,{no_ispell,norwegian_stem},no_ispell,{buljong,terning,pakk,mester,assistent})
ts_debug | (asciiword,Word, all
ASCII,sjokoladefabrikk,{no_ispell,norwegian_stem},no_ispell,{sjokoladefabrikk,sjokolade,fabrikk})
ts_debug | (asciiword,Word, all
ASCII,epleskrott,{no_ispell,norwegian_stem},no_ispell,{epleskrott,eple,skrott})
But, the following does not:
SELECT
ts_debug('norwegian', 'hemsedalsdans'),
ts_debug('norwegian', 'l?rdalsbrua'),
ts_debug('norwegian', 'hengesmykke');
-[ RECORD 1
]
ts_debug | (asciiword,Word, all
ASCII,hemsedalsdans,{no_ispell,norwegian_stem},norwegian_stem,{hemsedalsdan})
ts_debug | (word,Word, all
letters,l?rdalsbrua,{no_ispell,norwegian_stem},norwegian_stem,{l?rdalsbru})
ts_debug | (asciiword,Word, all
ASCII,hengesmykke,{no_ispell,norwegian_stem},norwegian_stem,{hengesmykk})
Would this be due to a limitation in the dictionary, or a
misconfiguration on my side?
sorry, I don't know norwegian, what do you mean ? Did you complain that
no_ispell doesn't recognize these words ?
Yes, I'm sorry, I should have explained better.
The words hemsedalsdans, hengesmykke and lærdalsbrua, are
concatenations of the words Hemsedal and dans, henge and smykke and
Lærdal and bru. Hemsedal and Lærdal are in fact geographic names, so I'm
not sure it would handle that at all anyway. Both parts of the word,
hengesmykke, is in the dictionary though, ie. both henge and smykke. It
seems that some words it is able to properly spilt, and then some it
doesn't recognise.
The problem I'm trying to work around, is that as far as I can tell,
tsearch doesn't support truncation, ie. searching for *smykke or
hemsedal* etc.
--
Tommy Gildseth
--
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] Varchar vs text
Thanks for elaborating on that Tom. I understand what it means by extension now. The reason I looked into it in the first place was because someone at work said that varchar was an alias for text, which didn't quite sound right. And I had automatically used the data-type text for any varying text fields since there is no performance/storage hit in PostgreSQL for such data, unlike some other RBDMSs. It's interesting to know of the non-nativity of varchar, even if the practical differences are negligable. :) Thanks again Thom 2009/1/27 Tom Lane t...@sss.pgh.pa.us Thom Brown thombr...@gmail.com writes: The reason I ask is because the documentation says If character varying is used without length specifier, the type accepts strings of any size. The latter is a PostgreSQL extension. I wasn't sure if such an extension meant there was a level of over-head involved, or reduced its indexability. Extension means it's not in the SQL standard. It's not meant to imply anything about performance. There is some potential overhead from using varchar instead of text because of the extra dummy cast nodes that are likely to be present in your query expressions (since all the native functions are declared to take/return text, not varchar). In most cases I'd think you'd be hard-put to measure any difference though. regards, tom lane
[GENERAL] FW: information about tables via psql
Hi, I am in process of learning psql. I'm trying to extract information about tables - as many details as possible. Something similar to what I'm used to see using Ingres RDBMS - table structure, creation date, number of rows, primary keys, is it journalled, etc. In Ingres in order to see that information I use commands 'help tablename' and more detailed version 'help table tablename'. Is there any postgres equivalent of these commands? With psql I used '\d tablename' and '\d+ tablename'. None of those showed me what I need. See below. Thanks in advance, Nina eq=# \d+ c_ass \g Table public.c_ass Column| Type | Modifiers | Description -+---+--+- arrid | character(16) | not null | solid | character(16) | not null | wt_flag | character(1) | not null default ' '::bpchar | res | real | | wt | real | | dist| real | | az | real | | octant | character(2) | not null default ' '::bpchar | mag1| real | | av_mag1_flg | character(1) | not null default ' '::bpchar | mag1type| character(4) | not null default ' '::bpchar | mag1_res| real | not null default 0 | cphase | character(8) | not null default ' '::bpchar | emerang | real | | delta | real | | Has OIDs: no Ingres = * help c_ass \g Executing . . . Name: c_ass Owner:dba Created: 2008-05-02 10:03:09 Type: user table Version: II2.6 Column Information: Key Column Name Type Length Nulls Defaults Seq arridchar 16 no no 2 solidchar 16 no no 1 wt_flag char1 no yes res float 4 yesnull wt float 4 yesnull * help table c_ass \g Executing . . . Name: c_ass Owner:dba Created: 2008-05-02 10:03:09 Location: ii_database Type: user table Version: II2.6 Page size:8192 Cache priority: 0 Alter table version: 0 Alter table totwidth: 87 Row width:87 Number of rows: 3350762 Storage structure:btree Compression: none Duplicate Rows: not allowed Number of pages: 83329 Overflow data pages: 0 Journaling: enabled Base table for view: no Optimizer statistics: none Column Information: Key Column Name Type Length Nulls Defaults Seq arridchar 16 no no 2 solidchar 16 no no 1 wt_flag char1 no yes res float 4 yesnull wt float 4 yesnull Secondary indexes: Index Name Structure Keyed On c_ass_idxisam arrid, solid
Re: [GENERAL] Serial Jumping
On Tue, 2009-01-27 at 05:55 +0100, db.subscripti...@shepherdhill.biz wrote: I have a table with BIG SERIAL field as Primary KEY. During high load, entries in the BIG SERIAL field are jumped. One could see a row with 1367 and expecting the next INSERT to be 1368, one would end up getting 1369. Please is this normal? Yes, but there is a way to get rid of that: http://www.varlena.com/GeneralBits/130.php Regards, -- Devrim GÜNDÜZ, RHCE devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Text search with ispell
On Tue, 27 Jan 2009, Tommy Gildseth wrote: sorry, I don't know norwegian, what do you mean ? Did you complain that no_ispell doesn't recognize these words ? Yes, I'm sorry, I should have explained better. The words hemsedalsdans, hengesmykke and l?rdalsbrua, are concatenations of the words Hemsedal and dans, henge and smykke and L?rdal and bru. Hemsedal and L?rdal are in fact geographic names, so I'm not sure it would handle that at all anyway. Both parts of the word, hengesmykke, is in the dictionary though, ie. both henge and smykke. It seems that some words it is able to properly spilt, and then some it doesn't recognise. you may improve dictionary, affix file should have COMPOUNDFLAG z dict file should contain 'henge', 'smykke' with that flag 'z'. Where did you get dictionary ? The problem I'm trying to work around, is that as far as I can tell, tsearch doesn't support truncation, ie. searching for *smykke or hemsedal* etc. 8.4 version will support prefix search hemsedal*. But you could always write your own dictionary or just use dict_xsyn dictionary for such kinds exceptions. http://www.postgresql.org/docs/8.3/static/dict-xsyn.html Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- 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] very long update gin index troubles back?
No matter if I drop the trigger that update agg content and the fact that I'm just updating d, postgresql will update the index? Yes, due to MVCC. Update of row could produce new version (tuple) and new version should be index as old one. -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] New 8.4 hot standby feature
I have merged the last hot standby patch (v9g) to 8.4 devel and I am pleased
with the experience. This is promising stuff. Perhaps it is a bit too soon to
ask questions here but here it is:
1. Speed of recovery
With a archive_timeout of 60 seconds, it can take about 4 minutes before I see
the reflected changes in the replica. This is normal since, in addition to
the WAL log shipping, it takes more time to do the recovery itself. Still, is
there any way besides the archive_timeout config option to speed up the
recovery of WAL logs on the hot standby?
2. last modified since timestamp:
Is there a way to get the last modified since timestamp on the hot standby
replica? Since the replication is asynchronous, it is necessary to know how
up to date the replication is. In our case, the timestamp is used this way:
select * from resource where not_modified_since =
to_timestamp('$not_modified_since', '/MM/DD HH:MI:SS');
The $not_modified_since is set to now() for the next time this query will be
run on the master database. This way the application keeps a cache and it is
not necessary to fetch everything every time. With an asynchronous replica
however, now() cannot be used and so I am looking into other possibilities
to get an accurate last modified since on the hot standby itself. I
tried select pg_last_recovered_xact_timestamp();
(http://wiki.postgresql.org/wiki/Hot_Standby) but this is not 100% accurate.
It looks like it has more to do with recovery transactions then the original
database itself.
So the question is: is there any clean way to get the last_modified_since
without making modifications to the schema?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] compiling libpq.lb into qsqlpsql.dll
Hello all. I have a problem and tech support at troll tech has been unable to help me resolve. I am trying to compile libpq.lib from postgresql 8.3.5 into QT's version 4.4.3 qsqlpsql.dll. using Visual Studio 2008 Built libpq.lib and libpq.dll with no problesm using nmake. Now comes time to build the qsqlpsql.dll following the way out of date instructions. Got all paths for includes and libs taken care of. Now compiling from nmake get billionz link errors LNK2019: unresolved external symbol I then imported the pro and make files in Visual Studio project to better figure out why it can't find external symbols Right off the bat Visual Studio reduces the number of external symbols not found to 5 1libpq.lib(fe-connect.obj) : error LNK2019: unresolved external symbol __imp__deletesecuritycont...@4 referenced in function _closePGconn 1libpq.lib(fe-connect.obj) : error LNK2019: unresolved external symbol __imp__freecredentialshan...@4 referenced in function _closePGconn 1libpq.lib(fe-auth.obj) : error LNK2019: unresolved external symbol _freecontextbuf...@4 referenced in function _pg_SSPI_continue 1libpq.lib(fe-auth.obj) : error LNK2019: unresolved external symbol _initializesecurityconte...@48 referenced in function _pg_SSPI_continue 1libpq.lib(fe-auth.obj) : error LNK2019: unresolved external symbol _acquirecredentialshand...@36 referenced in function _pg_SSPI_startup Now i'm stuck and can't figure out the next step. my guess is i don't have all the paths for include and libs set correctly. The idea here is get SSL built in qsqlpsql.dll which by default its not enabled. But i wanted to start off just to get plain jane libpq to compile with qsqlpsql.dll before i make sure libpq has SSL turned on. thanks
Re: [GENERAL] New 8.4 hot standby feature
On 1/27/09, Gabi Julien gabi.jul...@broadsign.com wrote: I have merged the last hot standby patch (v9g) to 8.4 devel and I am pleased with the experience. This is promising stuff. Perhaps it is a bit too soon to ask questions here but here it is: 1. Speed of recovery With a archive_timeout of 60 seconds, it can take about 4 minutes before I see the reflected changes in the replica. This is normal since, in addition to the WAL log shipping, it takes more time to do the recovery itself. Still, is there any way besides the archive_timeout config option to speed up the recovery of WAL logs on the hot standby? you can manually throw pg_switch_xlog(), In practice, this is more of an issue on development boxes than anything if you server is at all busy. see: http://developer.postgresql.org/pgdocs/postgres/functions-admin.html merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Hot standby
1. Speed of recovery
With a archive_timeout of 60 seconds, it can take about 4 minutes before I see
the reflected changes in the replica. This is normal since, in addition to
the WAL log shipping, it takes more time to do the recovery itself. Still, is
there any way besides the archive_timeout config option to speed up the
recovery of WAL logs on the hot standby?
2. last modified since timestamp:
I have merged the last hot standby patch (v9g) to 8.4 devel and I am pleased
with the experience. This is promising stuff. Perhaps it is a bit too soon to
ask the question on this forum but here it is: Is there a way to get
the last modified since timestamp on the hot standby replica? Since the
replication is asynchronous, it is necessary to know how up to date the
replication is. In our case, the timestamp is used this way:
select * from resource where not_modified_since =
to_timestamp('$not_modified_since', '/MM/DD HH:MI:SS');
The $not_modified_since is set to now() for the next time this query will be
run on the master database. This way the application keeps a cache and it is
not necessary to fetch everything every time. With an asynchronous replica
however, now() cannot be used and so I am looking into other possibilities
to get an accurate last modified since on the hot standby itself. I
tried select pg_last_recovered_xact_timestamp();
(http://wiki.postgresql.org/wiki/Hot_Standby) but this is not 100% accurate.
It looks like it has more to do with recovery transactions then the original
database itself.
So the question is: is there any clean way to get the last_modified_since
without making modifications to the schema?
--
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] New 8.4 hot standby feature
On Tue, 2009-01-27 at 12:58 -0500, Merlin Moncure wrote: you can manually throw pg_switch_xlog(), In practice, this is more of an issue on development boxes than anything if you server is at all busy. That won't speed up recovery, that will just force the WAL segment to be archived. It's still up to the standby to find the log and replay it. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] performance advice needed: join vs explicit subselect
Hello all, maybe some general advice can be had on this: table test_results modified_by integer foreign key staff(pk), intended_reviewer integer foreign key staff(pk), actual_reviewer integer foreign key staff(pk) (this table will contain millions of rows) table staff pk integer name text (this table will contain at most 50 rows) Now I want to set up a view which aggregates test results with staff names for all three foreign keys. This would mean I would either have to - join test_results to staff three times, once for each of the foreign keys, this is going to be messy with tracking table aliases, duplicate column names etc - write three explicit sub-selects for the columns I want to denormalize into the view definition Is there general advice as to which of the alternatives is worse under most if not all circumstances ? Thanks, Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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] FW: information about tables via psql
On Tue, Jan 27, 2009 at 11:26:13AM -0500, Markova, Nina wrote: I am in process of learning psql. I'm trying to extract information about tables - as many details as possible. Something similar to what I'm used to see using Ingres RDBMS Postgres and Ingres are different database engines, so the output from the various utilities will be different. table structure, Not sure what you mean by this, but if you mean the columns, their data types and associated constraints then \d should be what you want. creation date, PG doesn't record this anywhere. number of rows, You have to explicitly get this by doing: SELECT COUNT(*) FROM table; The reason is that it's an expensive operation (in terms of disk IOs) because a whole table scan has to be performed. I'd guess Ingress has this information to hand at the cost of worse performance in the presence of multiple writers. If you want a lower cost solution for PG you can look in the statistics for your tables; but this will be somewhat out of date. Something like this works for me: SELECT n_live_tup FROM pg_stat_user_tables WHERE relid = 'mytable'::REGCLASS; primary keys, again, \d is what you want is it journalled, Not sure what you mean here; but everything in PG is written to the WAL. You can't control this. -- Sam http://samason.me.uk/ -- 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] New 8.4 hot standby feature
On Tue, 2009-01-27 at 12:53 -0500, Gabi Julien wrote: I have merged the last hot standby patch (v9g) to 8.4 devel and I am pleased with the experience. This is promising stuff. Perhaps it is a bit too soon to ask questions here but here it is: 1. Speed of recovery With a archive_timeout of 60 seconds, it can take about 4 minutes before I see the reflected changes in the replica. This is normal since, in addition to the WAL log shipping, it takes more time to do the recovery itself. Still, is there any way besides the archive_timeout config option to speed up the recovery of WAL logs on the hot standby? Is the recovery itself the bottleneck? There's a performance improvement submitted here: http://archives.postgresql.org/message-id/a778a7260810280033n43f70d36x8c437eacf9a54...@mail.gmail.com But I haven't been following the development of it closely, so you'll have to read the thread to see whether it will meet your needs or not. Regards, Jeff Davis -- 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] Re: how to implement a foreign key type constraint against a not unique column
On Jan 25, 2009, at 3:07 AM, Jasen Betts wrote: I want to use this column as a foreign key on a column in another table (column 2), but cannot without a full unique index. a full unique index is easy use an expression that's null for -1. create unique index foobar on foo( ( case fee when -1 then null else fee end ) ); anything wrong with create unique index foobar on foo where fee -1 ? Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,497f50e9747031810420427! -- 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] performance advice needed: join vs explicit subselect
Karsten Hilbert wrote: Hello all, maybe some general advice can be had on this: table test_results modified_by integer foreign key staff(pk), intended_reviewer integer foreign key staff(pk), actual_reviewer integer foreign key staff(pk) (this table will contain millions of rows) table staff pk integer name text (this table will contain at most 50 rows) Now I want to set up a view which aggregates test results with staff names for all three foreign keys. This would mean I would either have to - join test_results to staff three times, once for each of the foreign keys, this is going to be messy with tracking table aliases, duplicate column names etc - write three explicit sub-selects for the columns I want to denormalize into the view definition Select testresults.*, Modifer.Name, Intended.name, Actual.name from testresults left join (Select pk, name from staff) Modifer on Modifer.pk = testresults.modified_by left join (Select pk, name from staff) Intended on Reviewer.pk = testresults.intended_reviewer left join (Select pk, name from staff) Actual on pk = testresults.actual_reviewer This is what i think you are after. You can do this via nested queries also for each name -- 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] performance advice needed: join vs explicit subselect
typo sorry justin wrote: Karsten Hilbert wrote: Hello all, maybe some general advice can be had on this: table test_results modified_by integer foreign key staff(pk), intended_reviewer integer foreign key staff(pk), actual_reviewer integer foreign key staff(pk) (this table will contain millions of rows) table staff pk integer name text (this table will contain at most 50 rows) Now I want to set up a view which aggregates test results with staff names for all three foreign keys. This would mean I would either have to - join test_results to staff three times, once for each of the foreign keys, this is going to be messy with tracking table aliases, duplicate column names etc - write three explicit sub-selects for the columns I want to denormalize into the view definition Select testresults.*, Modifer.Name, Intended.name, Actual.name from testresults left join (Select pk, name from staff) Modifer on Modifer.pk = testresults.modified_by left join (Select pk, name from staff) Intended on Inteded.pk = testresults.intended_reviewer left join (Select pk, name from staff) Actual on Actual.pk = testresults.actual_reviewer This is what i think you are after. You can do this via nested queries also for each name -- 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] Slow first query despite LIMIT and OFFSET clause
On Jan 26, 2009, at 4:41 AM, Phoenix Kiula wrote: Appreciate any thoughts. My query is: explain analyze SELECT testimonials.url ,testimonials.alias ,testimonials.aliasEntered ,testimonials.title ,testimonials.modify_date ,testimonials.id ,visitcount.visit_count ,visitcount.unique_count ,visitcount.modify_date ,coalesce( extract(epoch from now()) - extract(epoch from visitcount.modify_date), 0) ,(select count(id) from testimonials WHERE testimonials.user_id = 'superman' and testimonials.user_known = 1 and testimonials.status = 'Y' ) AS total FROM testimonials LEFT JOIN visitcount ON testimonials.id = visitcount.id WHERE testimonials.user_id = 'superman' and testimonials.user_known = 1 and testimonials.status = 'Y' ORDER BY testimonials.modify_date desc OFFSET 0 LIMIT 10 ; QUERY PLAN Limit (cost=224.68..224.71 rows=10 width=187) (actual time=453.429..453.539 rows=10 loops=1) InitPlan - Aggregate (cost=63.52..63.53 rows=1 width=8) (actual time=89.268..89.271 rows=1 loops=1) - Index Scan using new_idx_userknown on testimonials (cost=0.00..63.41 rows=42 width=8) (actual time=0.039..49.968 rows=10149 loops=1) Index Cond: ((user_id)::text = 'superman'::text) Filter: (status = 'Y'::bpchar) - Sort (cost=161.16..161.26 rows=42 width=187) (actual time=453.420..453.464 rows=10 loops=1) Sort Key: testimonials.modify_date - Nested Loop Left Join (cost=0.00..160.02 rows=42 width=187) (actual time=89.384..395.008 rows=10149 loops=1) - Index Scan using new_idx_userknown on testimonials (cost=0.00..63.41 rows=42 width=171) (actual time=0.061..50.990 rows=10149 loops=1) Index Cond: ((user_id)::text = 'superman'::text) Filter: (status = 'Y'::bpchar) - Index Scan using visitcount_pkey1 on visitcount (cost=0.00..2.28 rows=1 width=24) (actual time=0.007..0.010 rows=1 loops=10149) Index Cond: (testimonials.id = visitcount.id) Total runtime: 461. 682 ms (15 rows) Does that query plan look any better without the select count(id) from testimonials? If so you may be better off keeping track of those counts in a separate table updated by triggers on the testimonials table. Whether that really helps depends on how variable your selectors are to determine those counts. If those counts are generally very low the benefit will probably be minimal. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,497f5466747032672819277! -- 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 do an UPDATE for all the fields that do NOT break a constraint?
On Jan 26, 2009, at 2:09 PM, Phoenix Kiula wrote: I wonder if this is an SQL limitation or something I'm missing in the PG manual, but I need to run an update on my database (to replace the value of a column to match a new design structure). Easiest is probably to add a new column for the new values and add a constraint to that, deprecating (or even dropping) the old column from your design. Don't forget to vacuum afterwards. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,497f5540747032091416566! -- 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] performance advice needed: join vs explicit subselect
On Tue, Jan 27, 2009 at 07:12:05PM +0100, Karsten Hilbert wrote: Hello all, maybe some general advice can be had on this: table test_results modified_by integer foreign key staff(pk), intended_reviewer integer foreign key staff(pk), actual_reviewer integer foreign key staff(pk) (this table will contain millions of rows) table staff pk integer name text (this table will contain at most 50 rows) Now I want to set up a view which aggregates test results with staff names for all three foreign keys. This would mean I would either have to - join test_results to staff three times, once for each of the foreign keys, this is going to be messy with tracking table aliases, duplicate column names etc if you've only got three columns it shouldn't be too bad should it? - write three explicit sub-selects for the columns I want to denormalize into the view definition This would look a bit prettier, but PG tends not to optimize at all. It always executes it as a subplan and hence will only work nicely when you've got a very small subset of the test_results coming back. PG will *sometimes* remove subexpressions, but doesn't seem very predictable about it: SELECT id FROM ( SELECT a.id, (SELECT b.name FROM bar b WHERE a.tid = b.tid) FROM foo a) x; PG seems to recognize that it can remove the subselect in the above which is nice, but in other situations it doesn't seem to. -- Sam http://samason.me.uk/ -- 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] performance advice needed: join vs explicit subselect
On Jan 27, 2009, at 7:12 PM, Karsten Hilbert wrote: Hello all, maybe some general advice can be had on this: table test_results modified_by integer foreign key staff(pk), intended_reviewer integer foreign key staff(pk), actual_reviewer integer foreign key staff(pk) (this table will contain millions of rows) table staff pk integer name text (this table will contain at most 50 rows) Now I want to set up a view which aggregates test results with staff names for all three foreign keys. This would mean I would either have to - join test_results to staff three times, once for each of the foreign keys, this is going to be messy with tracking table aliases, duplicate column names etc - write three explicit sub-selects for the columns I want to denormalize into the view definition Is there general advice as to which of the alternatives is worse under most if not all circumstances ? I did something similar once using expression logic for my aggregates: SELECT SUM(CASE WHEN modified_by = pk THEN 1 ELSE 0 END) AS modified_by_count, SUM(CASE WHEN intended_reviewer = pk THEN 1 ELSE 0 END) AS intended_reviewer_count, SUM(CASE WHEN actual_reviewer = pk THEN 1 ELSE 0 END) AS actual_reviewer_count FROM test_results, staff WHERE pk IN (modified_by, intended_reviewer, actual_reviewer) Mind, this will very probably do a sequential scan over the product of both tables, but at least now the staff table is in that product only once. In actuality I didn't use CASE statements but cast the boolean results of the expressions directly to integer, something like SUM((modified_by = pk)::int), but that cast may no longer work since 8.3. I no longer have access to the project that I used this on, so I can't verify unfortunately. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,497f5aa8747035160810079! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Fwd: Re: [GENERAL] New 8.4 hot standby feature
On Tuesday 27 January 2009 13:13:32 you wrote: On Tue, 2009-01-27 at 12:53 -0500, Gabi Julien wrote: I have merged the last hot standby patch (v9g) to 8.4 devel and I am pleased with the experience. This is promising stuff. Perhaps it is a bit too soon to ask questions here but here it is: 1. Speed of recovery With a archive_timeout of 60 seconds, it can take about 4 minutes before I see the reflected changes in the replica. This is normal since, in addition to the WAL log shipping, it takes more time to do the recovery itself. Still, is there any way besides the archive_timeout config option to speed up the recovery of WAL logs on the hot standby? Is the recovery itself the bottleneck? Yes, the logs are shipped every minute but the recevory is 3 or 4 times longer. There's a performance improvement submitted here: http://archives.postgresql.org/message-id/a778a7260810280033n43f70d36x8c437 eacf9a54...@mail.gmail.com But I haven't been following the development of it closely, so you'll have to read the thread to see whether it will meet your needs or not. Thanks I will take a look at it. Also, I came across the record log shipping feature too in my research: http://www.postgresql.org/docs/current/static/warm-standby.html#WARM-STANDBY-RECORD Could this help? If the logs are smaller then I could potentially afford shipping then at a higher frequency. Regards, Jeff Davis --- -- 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] performance advice needed: join vs explicit subselect
On Tue, Jan 27, 2009 at 06:48:11PM +, Sam Mason wrote:
table test_results
modified_by integer foreign key staff(pk),
intended_reviewer integer foreign key staff(pk),
actual_reviewer integer foreign key staff(pk)
(this table will contain millions of rows)
table staff
pk integer
name text
(this table will contain at most 50 rows)
Now I want to set up a view which aggregates test results
with staff names for all three foreign keys. This would mean
I would either have to
- join test_results to staff three times, once for each
of the foreign keys, this is going to be messy with
tracking table aliases, duplicate column names etc
if you've only got three columns it shouldn't be too bad should it?
This is what one deserves for thinking to be able to distill
the essence of a problem :-)
The view in question is in fact a lot more complicated. This
is the best I've been able to come up with so far (and it is
still slow - slow as in 3-4 seconds for 20 records out of
(currently only) 50 !):
create view clin.v_test_results as
select
cenc.fk_patient
as pk_patient,
-- test_result
tr.pk as pk_test_result,
tr.clin_when,
-- unified
vttu.unified_code,
vttu.unified_name,
case when coalesce(trim(both from tr.val_alpha), '') = ''
then tr.val_num::text
else case when tr.val_num is null
then tr.val_alpha
else tr.val_num::text || ' (' || tr.val_alpha || ')'
end
end as unified_val,
coalesce(tr.val_target_min, tr.val_normal_min)
as unified_target_min,
coalesce(tr.val_target_max, tr.val_normal_max)
as unified_target_max,
coalesce(tr.val_target_range, tr.val_normal_range)
as unified_target_range,
tr.soap_cat,
tr.narrative
as comment,
-- test result data
tr.val_num,
tr.val_alpha,
tr.val_unit,
vttu.conversion_unit,
tr.val_normal_min,
tr.val_normal_max,
tr.val_normal_range,
tr.val_target_min,
tr.val_target_max,
tr.val_target_range,
tr.abnormality_indicator,
tr.norm_ref_group,
tr.note_test_org,
tr.material,
tr.material_detail,
-- test type data
vttu.code_tt,
vttu.name_tt,
vttu.coding_system_tt,
vttu.comment_tt,
vttu.code_unified,
vttu.name_unified,
vttu.coding_system_unified,
vttu.comment_unified,
-- episode/issue data
epi.description
as episode,
-- status of last review
coalesce(rtr.fk_reviewed_row, 0)::bool
as reviewed,
rtr.is_technically_abnormal
as is_technically_abnormal,
rtr.clinically_relevant
as is_clinically_relevant,
rtr.comment
as review_comment,
(select
short_alias || ' (' ||
coalesce(title || ' ', '') ||
coalesce(firstnames || ' ', '') ||
coalesce(lastnames, '') ||
')'
from dem.v_staff
where pk_staff = rtr.fk_reviewer
) as last_reviewer,
rtr.modified_when
as last_reviewed,
coalesce (
(rtr.fk_reviewer = (select pk from dem.staff where db_user =
current_user)),
False
)
as review_by_you,
coalesce (
(tr.fk_intended_reviewer = rtr.fk_reviewer),
False
)
as review_by_responsible_reviewer,
-- potential review status
(select
short_alias || ' (' ||
coalesce(title || ' ', '') ||
coalesce(firstnames || ' ', '') ||
coalesce(lastnames, '') ||
')'
from dem.v_staff
where pk_staff = tr.fk_intended_reviewer
) as responsible_reviewer,
coalesce (
(tr.fk_intended_reviewer = (select pk from dem.staff where
db_user = current_user)),
False
)
as you_are_responsible,
case when ((select 1 from dem.staff where db_user = tr.modified_by) is
null)
then '' || tr.modified_by || ''
else (select short_alias from dem.staff where db_user =
tr.modified_by)
end
as modified_by,
tr.modified_when,
tr.row_version as row_version,
-- management keys
-- clin.clin_root_item
tr.pk_item,
tr.fk_encounter as pk_encounter,
tr.fk_episode as pk_episode,
-- test_result
tr.fk_type as pk_test_type,
tr.fk_intended_reviewer as pk_intended_reviewer,
tr.xmin as xmin_test_result,
Re: Fwd: Re: [GENERAL] New 8.4 hot standby feature
On Tue, 2009-01-27 at 14:28 -0500, Gabi Julien wrote: Could this help? If the logs are smaller then I could potentially afford shipping then at a higher frequency. See if there are times during which the recovery process isn't doing anything (i.e. just waiting for WAL data). If so, something like this might help. If it's constantly working as hard as it can, then probably not. An important question you should ask yourself is whether it can keep up in the steady state at all. If the primary is producing segments faster than the standby is recovering them, I don't think there's any way around that. Regards, Jeff Davis -- 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] performance advice needed: join vs explicit subselect
Karsten Hilbert karsten.hilb...@gmx.net writes: The view in question is in fact a lot more complicated. This is the best I've been able to come up with so far (and it is still slow - slow as in 3-4 seconds for 20 records out of (currently only) 50 !): What does EXPLAIN ANALYZE say about it? Also, what is the use-case you are concerned about --- selecting the whole view contents, or selecting WHERE something-or-other? 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] performance advice needed: join vs explicit subselect
On Tue, Jan 27, 2009 at 04:42:02PM -0500, Tom Lane wrote: Karsten Hilbert karsten.hilb...@gmx.net writes: The view in question is in fact a lot more complicated. This is the best I've been able to come up with so far (and it is still slow - slow as in 3-4 seconds for 20 records out of (currently only) 50 !): What does EXPLAIN ANALYZE say about it? Also, what is the use-case you are concerned about --- selecting the whole view contents, or selecting WHERE something-or-other? The query that's run by my application (wiki.gnumed.de) is select *, xmin_test_result from clin.v_test_results where pk_patient = 138 --- this is a variable order by clin_when desc, pk_episode, unified_name ; the explain analyze of which is (I've actually gotten it to work better in the meantime as you can see): SET BEGIN QUERY PLAN - Sort (cost=8512.91..8512.92 rows=1 width=721) (actual time=2039.771..2039.787 rows=14 loops=1) Sort Key: tr.clin_when, tr.fk_episode, (COALESCE(ttu.name, tt1.name)) Sort Method: quicksort Memory: 22kB InitPlan - Seq Scan on staff (cost=0.00..1.07 rows=1 width=4) (actual time=0.014..0.014 rows=0 loops=1) Filter: (db_user = current_user()) - Seq Scan on staff (cost=0.00..1.07 rows=1 width=4) (actual time=0.014..0.014 rows=0 loops=1) Filter: (db_user = current_user()) - Nested Loop Left Join (cost=3.29..8510.75 rows=1 width=721) (actual time=145.824..2039.427 rows=14 loops=1) - Nested Loop Left Join (cost=3.29..27.82 rows=1 width=671) (actual time=4.230..5.298 rows=14 loops=1) - Nested Loop Left Join (cost=3.29..23.66 rows=1 width=646) (actual time=4.209..5.061 rows=14 loops=1) Join Filter: (tt1.pk = ltt2ut.fk_test_type) - Nested Loop (cost=2.20..21.42 rows=1 width=565) (actual time=4.089..4.444 rows=14 loops=1) - Merge Join (cost=2.20..20.79 rows=1 width=469) (actual time=4.069..4.201 rows=14 loops=1) Merge Cond: (cenc.pk = tr.fk_encounter) - Index Scan using encounter_pkey on encounter cenc (cost=0.00..294.43 rows=16 width=8) (actual time=1.470..3.691 rows=29 loops=1) Filter: (fk_patient = 138) - Sort (cost=2.20..2.29 rows=34 width=465) (actual time=0.279..0.330 rows=34 loops=1) Sort Key: tr.fk_encounter Sort Method: quicksort Memory: 25kB - Seq Scan on test_result tr (cost=0.00..1.34 rows=34 width=465) (actual time=0.027..0.141 rows=34 loops=1) - Index Scan using test_type_pkey on test_type tt1 (cost=0.00..0.62 rows=1 width=96) (actual time=0.007..0.009 rows=1 loops=14) Index Cond: (tt1.pk = tr.fk_type) - Hash Join (cost=1.09..2.19 rows=4 width=89) (actual time=0.012..0.031 rows=4 loops=14) Hash Cond: (ttu.pk = ltt2ut.fk_test_type_unified) - Seq Scan on test_type_unified ttu (cost=0.00..1.04 rows=4 width=85) (actual time=0.003..0.008 rows=4 loops=14) - Hash (cost=1.04..1.04 rows=4 width=8) (actual time=0.023..0.023 rows=4 loops=1) - Seq Scan on lnk_ttype2unified_type ltt2ut (cost=0.00..1.04 rows=4 width=8) (actual time=0.006..0.013 rows=4 loops=1) - Index Scan using episode_pkey on episode epi (cost=0.00..4.15 rows=1 width=29) (actual time=0.009..0.011 rows=1 loops=14) Index Cond: (tr.fk_episode = epi.pk) - Index Scan using unique_review_per_row on reviewed_test_results rtr (cost=0.00..0.62 rows=1 width=50) (actual time=0.005..0.008 rows=1 loops=14) Index Cond: (tr.pk = rtr.fk_reviewed_row) SubPlan - Seq Scan on staff (cost=0.00..1.06 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=14) Filter: (db_user = $20) - Seq Scan on staff (cost=0.00..1.06 rows=1 width=0) (actual time=0.006..0.007 rows=1 loops=14) Filter: (db_user = $20) - Subquery Scan v_staff (cost=15.73..4240.07 rows=1 width=128) (actual time=29.739..74.520 rows=1 loops=14) - Nested Loop (cost=15.73..4240.04 rows=1 width=279) (actual time=29.731..74.510 rows=1 loops=14) Join Filter: (s.fk_identity = i.pk)
Re: [GENERAL] performance advice needed: join vs explicit subselect
On Tue, Jan 27, 2009 at 04:42:02PM -0500, Tom Lane wrote: What does EXPLAIN ANALYZE say about it? Also, what is the use-case you are concerned about --- selecting the whole view contents, or selecting WHERE something-or-other? Oh, and the use case is to select all the test_results which belong to a certain patient: where pk_patient = scalar Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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] performance advice needed: join vs explicit subselect
Karsten Hilbert karsten.hilb...@gmx.net writes: the explain analyze of which is (I've actually gotten it to work better in the meantime as you can see): Looks like most of the problem is in the subquery scans on v_staff, which seems to be a rather expensive view :-(. Maybe you can simplify that a bit. 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] performance advice needed: join vs explicit subselect
On Tue, Jan 27, 2009 at 05:30:23PM -0500, Tom Lane wrote: Karsten Hilbert karsten.hilb...@gmx.net writes: the explain analyze of which is (I've actually gotten it to work better in the meantime as you can see): Looks like most of the problem is in the subquery scans on v_staff, which seems to be a rather expensive view :-(. Maybe you can simplify that a bit. Thanks so much. I wasn't quite sure how to correlate the seemingly expensive parts of the explain with the view/query parts. Will experiment with that... Well, going directly to the dem.staff table below dem.v_staff forces me to forego the actual name of the staff entry - but the alias will need to suffice ;-) This brings down query time from 2000ms to 7ms. Our doctors won't complain about slow lab data retrieval anymore ... ;-) Thanks, Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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] New 8.4 hot standby feature
Hi, On Wed, Jan 28, 2009 at 4:28 AM, Gabi Julien gabi.jul...@broadsign.com wrote: Yes, the logs are shipped every minute but the recevory is 3 or 4 times longer. Are you disabling full_page_writes? It may slow down recovery several times. Thanks I will take a look at it. Also, I came across the record log shipping feature too in my research: http://www.postgresql.org/docs/current/static/warm-standby.html#WARM-STANDBY-RECORD Could this help? If the logs are smaller then I could potentially afford shipping then at a higher frequency. No. Even if the logs are shipped frequently, they cannot be applied until the log file fills. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- 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] [lapug] Seeking volunteers for SCALE (one more time)
Hi Richard, I'm available to volunteer for a couple of hours or so. Let me know what time slots you're looking to cover. Thanks! -Noel - Original Message - From: Richard Broersma richard.broer...@gmail.com Sent: Mon, 1/26/2009 12:09pm To: Los Angeles PostgreSQL Users Group la...@postgresql.org Cc: PostgreSQL General pgsql-general@postgresql.org Subject: [lapug] Seeking volunteers for SCALE (one more time) This call is going out to PostgreSQL enthusiasts. We are seeking volunteers to assist the PostgreSQL booth for this years Southern California Linux Expo: http://scale7x.socallinuxexpo.org The Exhibit hall will be open on Saturday the February 21st and Sunday the 22nd between the hours of 10am and 5pm. The time that the booth will need attendants is between the hours of 10 a.m. and 5 p.m. http://scale7x.socallinuxexpo.org/conference-info/hotel_info This will be a great opportunity to meet PostgreSQL community leaders and local LAPUG members. For anyone what would like to volunteer, please email me with your contact information so that we can make arrangements. Also on a side note, LAPUG will be meeting at SCALE's PostgreSQL BOF. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via lapug mailing list (la...@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/lapug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Rollback of Query Cancellation
Dear All, Yesterday I canceled a running query because it was taking long time (more than 12 minutes) to delete lots of records. Today when I executed the same query it hardly took few seconds to finish. It clearly explores that PostgreSQL does not perform rollback action. Is it true? Regards, Abdul Rehman.
Re: [GENERAL] Rollback of Query Cancellation
Abdul Rahman wrote: Dear All, Yesterday I canceled a running query because it was taking long time (more than 12 minutes) to delete lots of records. Today when I executed the same query it hardly took few seconds to finish. It clearly explores that PostgreSQL does not perform rollback action. Is it true? Regards, Abdul Rehman. The transaction would have been rolled back. this makes me think the delete was wrapped in individual transactions for a set of records or per record. With out more information an intelligent answer is kind of hard to give. What does the sql statement look like? Where was the statement run? How was the statement killed? What do the logs show?
Re: [GENERAL] Rollback of Query Cancellation
Thanks Justin, Answers of your technical questions are as under: What does the sql statement look like? delete from ci_cif_v where req_id='0824100207'; Where was the statement run? On SQL pan of pgAdmin-III How was the statement killed? By clicking the Cancel Query button - What do the logs show? Message just showed the time it took to delete certain number of records.
Re: [GENERAL] Rollback of Query Cancellation
On Wed, Jan 28, 2009 at 12:19 AM, Abdul Rahman abr_...@yahoo.com wrote: Dear All, PostgreSQL does not perform rollback action. Is it true? no. postgresql executes all statements that are outside an explicit transaction in its own implicit one [1] executing commit at the end, if you cancelled the query the commit never executed and the records were never deleted... even more to the point, postgres never delete tuples on delete just mark them as invalid since transaction number X. at commit time the transaction is marked as correctly finished and all changes are WAL logged then suddenly changes take effect... rollback never mark the transaction as finished and doesn't save WAL records (that meaning that changes never spread to the datafiles) actually ROLLBACK is very cheap just don't save changes, COMMIT makes all the work... now, your post make me think that you think the ROLLBACK never executed based on... reponse time? very unscientific (there are plenty other reasons for that to happen) [1] http://www.postgresql.org/docs/8.3/static/tutorial-transactions.html -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] Rollback of Query Cancellation
On Wed, Jan 28, 2009 at 12:56 AM, Abdul Rahman abr_...@yahoo.com wrote: What do the logs show? Message just showed the time it took to delete certain number of records. can you show that message? copy 'n pasted from logs!! -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] Rollback of Query Cancellation
Welcome Jcasanov, Here is the output of the log files: 2009-01-27 09:24:18 FATAL: the database system is starting up 2009-01-27 09:24:19 LOG: database system was shut down at 2009-01-26 18:34:53 2009-01-27 09:24:19 LOG: checkpoint record is at 0/1B9F92C8 2009-01-27 09:24:19 LOG: redo record is at 0/1B9F92C8; undo record is at 0/0; shutdown TRUE 2009-01-27 09:24:19 LOG: next transaction ID: 0/335372; next OID: 19380 2009-01-27 09:24:19 LOG: next MultiXactId: 1; next MultiXactOffset: 0 2009-01-27 09:24:19 LOG: database system is ready 2009-01-27 18:52:43 LOG: received fast shutdown request 2009-01-27 18:52:44 LOG: shutting down 2009-01-27 18:52:44 LOG: database system is shut down 2009-01-27 18:52:44 LOG: logger shutting down and 2009-01-28 09:42:14 FATAL: the database system is starting up 2009-01-28 09:42:14 LOG: database system was shut down at 2009-01-27 18:52:44 2009-01-28 09:42:14 LOG: checkpoint record is at 0/1B9F9368 2009-01-28 09:42:14 LOG: redo record is at 0/1B9F9368; undo record is at 0/0; shutdown TRUE 2009-01-28 09:42:14 LOG: next transaction ID: 0/336490; next OID: 19380 2009-01-28 09:42:14 LOG: next MultiXactId: 1; next MultiXactOffset: 0 2009-01-28 09:42:15 LOG: database system is ready 2009-01-28 09:42:15 FATAL: the database system is starting up
Re: [GENERAL] Rollback of Query Cancellation
On Wed, Jan 28, 2009 at 1:29 AM, Abdul Rahman abr_...@yahoo.com wrote: Welcome Jcasanov, Here is the output of the log files: 2009-01-27 09:24:18 FATAL: the database system is starting up 2009-01-27 09:24:19 LOG: database system was shut down at 2009-01-26 18:34:53 2009-01-27 09:24:19 LOG: checkpoint record is at 0/1B9F92C8 2009-01-27 09:24:19 LOG: redo record is at 0/1B9F92C8; undo record is at 0/0; shutdown TRUE 2009-01-27 09:24:19 LOG: next transaction ID: 0/335372; next OID: 19380 2009-01-27 09:24:19 LOG: next MultiXactId: 1; next MultiXactOffset: 0 2009-01-27 09:24:19 LOG: database system is ready all these messages are 'cause the database is starting up 2009-01-27 18:52:43 LOG: received fast shutdown request 2009-01-27 18:52:44 LOG: shutting down 2009-01-27 18:52:44 LOG: database system is shut down 2009-01-27 18:52:44 LOG: logger shutting down the database was shutting down and 2009-01-28 09:42:14 FATAL: the database system is starting up 2009-01-28 09:42:14 LOG: database system was shut down at 2009-01-27 18:52:44 2009-01-28 09:42:14 LOG: checkpoint record is at 0/1B9F9368 2009-01-28 09:42:14 LOG: redo record is at 0/1B9F9368; undo record is at 0/0; shutdown TRUE 2009-01-28 09:42:14 LOG: next transaction ID: 0/336490; next OID: 19380 2009-01-28 09:42:14 LOG: next MultiXactId: 1; next MultiXactOffset: 0 2009-01-28 09:42:15 LOG: database system is ready 2009-01-28 09:42:15 FATAL: the database system is starting up starting up again none of these are saying the time for some records being deleted, read my prior post about why is not possible that message (commit never executed) -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] Rollback of Query Cancellation
Thanks Jaime, Plz consider the actual log files to explore the issue in detail. Because I have pasted the log files of client machine, I am using (sorry). 2009-01-27 18:29:25 STATEMENT: delete from ci_cin_type_v where req_id='0824100207' delete from ci_cust_type_v where req_id='0824100207' delete from ci_dependents_v where req_id='0824100207' delete from ci_employer_v where req_id='0824100207' delete from ci_cor_sig_v where req_id='0824100207' delete from ci_corporate_v where req_id='0824100207' delete from ci_individual_v where req_id='0824100207' delete from ci_cif_v where req_id='0824100207' 2009-01-27 18:29:41 ERROR: relation ci_cust_type_v does not exist 2009-01-27 18:29:41 STATEMENT: delete from ci_cust_type_v where req_id='0824100207' 2009-01-27 18:52:08 LOG: could not receive data from client: No connection could be made because the target machine actively refused it. 2009-01-27 18:52:08 LOG: unexpected EOF on client connection 2009-01-27 18:52:08 LOG: could not receive data from client: No connection could be made because the target machine actively refused it. 2009-01-27 18:52:08 LOG: unexpected EOF on client connection Here, I am talking about the last query (Bold_Italic). Now my question is still stands. Kindly continue to up your valuable text. From: Jaime Casanova jcasa...@systemguards.com.ec To: Abdul Rahman abr_...@yahoo.com Cc: pgsql-general@postgresql.org Sent: Wednesday, January 28, 2009 11:38:59 AM Subject: Re: Rollback of Query Cancellation On Wed, Jan 28, 2009 at 1:29 AM, Abdul Rahman abr_...@yahoo.com wrote: Welcome Jcasanov, Here is the output of the log files: 2009-01-27 09:24:18 FATAL: the database system is starting up 2009-01-27 09:24:19 LOG: database system was shut down at 2009-01-26 18:34:53 2009-01-27 09:24:19 LOG: checkpoint record is at 0/1B9F92C8 2009-01-27 09:24:19 LOG: redo record is at 0/1B9F92C8; undo record is at 0/0; shutdown TRUE 2009-01-27 09:24:19 LOG: next transaction ID: 0/335372; next OID: 19380 2009-01-27 09:24:19 LOG: next MultiXactId: 1; next MultiXactOffset: 0 2009-01-27 09:24:19 LOG: database system is ready all these messages are 'cause the database is starting up 2009-01-27 18:52:43 LOG: received fast shutdown request 2009-01-27 18:52:44 LOG: shutting down 2009-01-27 18:52:44 LOG: database system is shut down 2009-01-27 18:52:44 LOG: logger shutting down the database was shutting down and 2009-01-28 09:42:14 FATAL: the database system is starting up 2009-01-28 09:42:14 LOG: database system was shut down at 2009-01-27 18:52:44 2009-01-28 09:42:14 LOG: checkpoint record is at 0/1B9F9368 2009-01-28 09:42:14 LOG: redo record is at 0/1B9F9368; undo record is at 0/0; shutdown TRUE 2009-01-28 09:42:14 LOG: next transaction ID: 0/336490; next OID: 19380 2009-01-28 09:42:14 LOG: next MultiXactId: 1; next MultiXactOffset: 0 2009-01-28 09:42:15 LOG: database system is ready 2009-01-28 09:42:15 FATAL: the database system is starting up starting up again none of these are saying the time for some records being deleted, read my prior post about why is not possible that message (commit never executed) -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157
Re: [GENERAL] Rollback of Query Cancellation
Very nice! Most probably it was waiting for something else. This is the reason the query executed today and clearly showed certain number of rows deleted. But what ELSE? From: Sim Zacks s...@compulab.co.il To: Abdul Rahman abr_...@yahoo.com Sent: Wednesday, January 28, 2009 12:08:28 PM Subject: Re: [GENERAL] Rollback of Query Cancellation -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Its very possible that the reason that the query was because there was a lock on the table. If you run select * from pg_stat_activity while the query is running there is a waiting flag that indicates whether your query is actually being executed or if it is waiting for something else. Sim Abdul Rahman wrote: Dear All, Yesterday I canceled a running query because it was taking long time (more than 12 minutes) to delete lots of records. Today when I executed the same query it hardly took few seconds to finish. It clearly explores that PostgreSQL does not perform rollback action. Is it true? Regards, Abdul Rehman. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkmABGwACgkQjDX6szCBa+oOzwCguiRslM90EAKY/wo34htqtXUH hWIAoLkALRcBYEJ7jW5ordKRX73jqyR9 =TqIi -END PGP SIGNATURE-
Re: [GENERAL] Rollback of Query Cancellation
On Wed, Jan 28, 2009 at 2:10 AM, Abdul Rahman abr_...@yahoo.com wrote: Thanks Jaime, Plz consider the actual log files to explore the issue in detail. Because I have pasted the log files of client machine, I am using (sorry). 2009-01-27 18:29:25 STATEMENT: delete from ci_cin_type_v where req_id='0824100207' delete from ci_cust_type_v where req_id='0824100207' delete from ci_dependents_v where req_id='0824100207' delete from ci_employer_v where req_id='0824100207' delete from ci_cor_sig_v where req_id='0824100207' delete from ci_corporate_v where req_id='0824100207' delete from ci_individual_v where req_id='0824100207' delete from ci_cif_v where req_id='0824100207' then you have a lot of deletes, are there executing inside a transaction? are you calling a trigger? 2009-01-27 18:29:41 ERROR: relation ci_cust_type_v does not exist this table does not exist 2009-01-27 18:29:41 STATEMENT: delete from ci_cust_type_v where req_id='0824100207' 2009-01-27 18:52:08 LOG: could not receive data from client: No connection could be made because the target machine actively refused it. 2009-01-27 18:52:08 LOG: unexpected EOF on client connection 2009-01-27 18:52:08 LOG: could not receive data from client: No connection could be made because the target machine actively refused it. 2009-01-27 18:52:08 LOG: unexpected EOF on client connection guess this messages are received after the CANCEL QUERY if the series of deletes are all executed inside a transaction then they all were rollback if not only the last one (the one that generates the error) was rolledback -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
