[GENERAL] postgres_fdw: Running default expressions on foreign server
Hi Tom, I realize that postgres_fdw on 9.3 doesn’t support default expressions that run on the foreign server. In my case, I have a unique, auto-incrementing ID column that the remote server keeps track of in a sequence. The local foreign table doesn’t have access to this and tries to INSERT with IDs that have already been taken in the original table on the remote server. After seeing this post: http://www.postgresql.org/message-id/26654.1380145...@sss.pgh.pa.us, I’m hopeful honoring these default expressions in the foreign server will be supported at some point. I’m working on a project now that uses the postgres_fdw extensively. Do you know if this will be fixed in 9.4? I figure it’s worth checking since 9.4 is scheduled to be released any day now, before I start rewriting the whole project. Thanks, Tommy Duek
Re: [GENERAL] Access to postgres conversion
Hi. You could try /Access to PostgreSQL /from Bullzip.com. I liked the dump file that this creates. Small neat and it is free! Tommy. It's 10 tables. that's all. No reports. I will follow your suggestion. Thanks for the help On Wed, May 25, 2011 at 4:25 PM, John R Pierce pie...@hogranch.com mailto:pie...@hogranch.com wrote: On 05/25/11 12:42 PM, akp geek wrote: Dear all - I would like to know if any one has migrated database from MS access to Postgres . We use postgres 9.0.2 on solaris . Are there any open source tools that you have used to do this task. Can you please share your experiences ? how many tables is this database? is there more to it than just tables? (Access isn't really a database, its a data-centric rapid application development system). postgres won't do the reports, forms, macros, etc (ok, it has user definable procedures/functions, but they aren't in vbasic, and they don't work anything like access programs do). if its just a few tables, it might just be easiest to dump those tables as CSV files, then import them one by one into equivalent tables in postgres via COPY FROM -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org mailto:pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] rotate psql output
On 09/30/2010 02:11 PM, Matthew Seaman wrote: On 30/09/2010 12:31, Vincenzo Romano wrote: 2010/9/30 Ben Carberyben.carb...@gmail.com: Strange if this can't be done, I would have thought it a common request! Just curiosity. Is there any other DB capable of such a thing? MySQL does this using an alternate end-of-statement character: ';' or '\g' gives the usual orientation, '\G' gives the rotated orientation. No, that is exactly the same as \x or psql -x does. -- 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] rotate psql output
Ben Carbery wrote: I have a query that returns many columns but few rows. I would like to display output horizontally instead of vertically, i.e. rotating by 90 degress, so column headings appear in the left margin, and the output is not 'wrapped'. Is this possible? I have had no luck searching for this as rotate usually means log rotation. BC Have you tried \x ? -- Tommy Gildseth DBA, Gruppe for databasedrift Universitetet i Oslo, USIT m: +47 45 86 38 50 t: +47 22 85 29 39 -- 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] Hosting Account with PostgreSQL and PHP?
Andre Lopes wrote: I have contacted again the support center on a2hosting.com http://a2hosting.com and the answer was that is no manual creation of triggers on PostgreSQL, bu the guy have send to me a link with MySQL information about the subject, https://support.a2hosting.com/index.php?_m=knowledgebase_a=viewarticlekbarticleid=500 https://support.a2hosting.com/index.php?_m=knowledgebase_a=viewarticlekbarticleid=500 There are more a2hosting customers here that can create Triggers? This gives me the feeling that the support center doesn't know what they're talking about, and thinks Postgres works the same way as MySQL, where you do indeed have to be an administrator to create triggers. -- Tommy Gildseth DBA, Gruppe for databasedrift Universitetet i Oslo, USIT m: +47 45 86 38 50 t: +47 22 85 29 39 -- 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] PHP and PostgreSQL boolean data type
Thom Brown wrote: Is this a limitation of libpq or a flawed implementation in the php library? And if this is just the case for backwards-compatibility, is there a way to switch it to a more sensible PHP data type? Using PDO(http://no.php.net/pdo) will at least give you native values for true/false. Arrays, I don't know, since I don't use them. -- 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] Best way to mask password in DBLINK
Ow Mun Heng wrote: I'm starting to use DBLink / DBI-Link and one of the bad things is that the password is out in the clear. What can I do to prevent it from being such? How do I protect it from 'innocent' users? If I'm not mistaken, it's possible to put your password in the .pgpass file in the postgres-users home folder, on the server where the postgres cluster is running. -- 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] Best way to mask password in DBLINK
Ow Mun Heng wrote:
-Original Message-
From: Tommy Gildseth [mailto:tommy.gilds...@usit.uio.no]
Ow Mun Heng wrote:
I'm starting to use DBLink / DBI-Link and one of the bad things is that
the password is out in the clear.
What can I do to prevent it from being such? How do I protect it from
'innocent' users?
If I'm not mistaken, it's possible to put your password in the .pgpass
file in the postgres-users home folder, on the server where the postgres
cluster is running.
Isn't that how one connects using the CLI? Eg: via psql?
My connection string looks like this.
SELECT aaa
FROM dblink('dbname=hmxmms host=xxx.xxx.xxx.xxx user=yyy
password=zzz'::text, 'SELECT * from tablename'::text) b4(aaa xxx );
I've placed the above as a view hence the user/pass is being hardcoded(?) of
sorts
Just leave out the password=zzz part of the connection string.
--
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] Best way to mask password in DBLINK
Ow Mun Heng wrote: -Original Message- From: Magnus Hagander [mailto:mag...@hagander.net] On Wed, Aug 12, 2009 at 09:30, Ow Mun Hengow.mun.h...@wdc.com wrote: From: Tommy Gildseth [mailto:tommy.gilds...@usit.uio.no] Ow Mun Heng wrote: I'm starting to use DBLink / DBI-Link and one of the bad things is that the password is out in the clear. What can I do to prevent it from being such? How do I protect it from 'innocent' users? If I'm not mistaken, it's possible to put your password in the .pgpass file in the postgres-users home folder, on the server where the postgres cluster is running. Isn't that how one connects using the CLI? Eg: via psql? You need to put it in the .pgpass file of the postgres user - the one that runs the server. .pgpass is dealt with by libpq, and DBLink and DBI-Link both use libpq to connect to the remote server. The View is owned by the user operator not postgres Does it make a difference? My understanding of your words are that it _does_ make a difference and If I put it into the .pgpass of the postgres user then all is fine. No, it doesn't matter which role owns the database object. The system user trying to connect to the remote cluster via dblink, is the user which owns the postgres process, ie. normally the postgres system user. libpq will therefor look for the .pgpass file in the postgres system users home folder, irrespective of which role owns the database, or which role is used to connect to the database 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] Converting SQL to pg
mar...@cornhobble.com wrote: I must be tired. Any suggestions on converting the following to postgresql? UPDATE IGNORE yose5_user_newflags AS flags, yose5_messages AS msg SET flags.forum_id = msg.forum_id WHERE flags.message_id = msg.message_id AND flags.message_id IN (15580, 15581, 15706, 15712, 15713, 15719, 15888) Untested, but I imagine it would be something like this. UPDATE yose5_user_newflags AS flags SETforum_id = msg.forum_id FROM yose5_messages AS msg WHERE flags.message_id = msg.message_id AND flags.message_id IN (15580, 15581, 15706, 15712, 15713, 15719, 15888) PostgreSQL doesn't have anything similar to IGNORE (afaik), but depending on what you want to do, you may be able to work around that with deferred constraints etc. -- Tommy Gildseth DBA, Gruppe for databasedrift Universitetet i Oslo, USIT m: +47 45 86 38 50 t: +47 22 85 29 39 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Finding time of last pg_stat_reset
I'm trying to figure out if there's any way to find when statistics was last reset. Previously when we were using 8.2, we had stats_reset_on_server_start set to on, and then assumed pg_stat_get_backend_start as the start time for collected stats. Is there any way to do this in 8.3, without f.ex adding a call to pg_stat_reset() in our startup scripts? -- 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] How to restore a SQL-ASCII encoded database to a new UTF-8 db?
Postgres User wrote: Hi, I have a database that was created with SQL-ASCII encoding (unfortunately). I ran pg_restore to load the struct and data into a new database with UTF-8 encoding but no surprise- I'm seeing this error for a number of tables: pg_restore: [archiver (db)] COPY failed: ERROR: invalid byte sequence for encod ing UTF8 Any idea on how I can copy the data between these databases without any data loss? For some reason I thought that a conversion to Unicode would be easy. Provided you haven't actually any characters from different character sets or invalid characters in the dump, you may be able to import it just by changing the client encoding in the dump. There's probably a line saying something like SET CLIENT_ENCODING=SQL-ASCII; If you change that to SET CLIENT_ENCODING=Whatever_encoding_your_data_is_in; You may be able to import it. IIRC, PostgreSQL doesn't do any automatic conversion between SQL-ASCII - Any encoding, but if you put the correct encoding, PostgreSQL will deal with the conversion automatically. -- Tommy Gildseth DBA, Gruppe for databasedrift Universitetet i Oslo, USIT m: +47 45 86 38 50 t: +47 22 85 29 39 -- 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] Crash of Postgresql on Windows
Andrew Gould wrote: What does '--' do? -- Is an SQL comment -- Tommy Gildseth DBA, Gruppe for databasedrift Universitetet i Oslo, USIT m: +47 45 86 38 50 t: +47 22 85 29 39 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Text search segmentation fault
While trying to create a new dictionary for use with PostgreSQL text search, I get a segfault. My Postgres version is 8.3.5 The dictionary I use is the Norwegian Bokmål Nynorsk (Norway) pack for OOo 2.x downloaded from http://wiki.services.openoffice.org/wiki/Dictionaries#Norwegian_.28Norway.29 I've also uploaded the dictionary and affix-file used to this location: http://folk.uio.no/tommygi/postgres/nb_no.dict http://folk.uio.no/tommygi/postgres/nb_no.affix These are unpacked from the zip-file I got from the above location, and converted to UTF-8 with the following commands: - iconv -f latin1 -t utf-8 nb_NO.dic nb_no.dict - iconv -f latin1 -t utf-8 nb_NO.aff nb_no.affix The command I use is this: CREATE TEXT SEARCH DICTIONARY no_ispell ( TEMPLATE = ispell, DictFile = nb_no, AffFile = nb_no, StopWords = norwegian ); I've uploaded a strace of the process at http://folk.uio.no/tommygi/postgres/strace.txt This is captured with strace -ppid. Since this is about the extent of my knowledge of strace, please let me know if there's any other options I should specify. Relevant parts of the postgres log file is included below. [2009-01-29 13:55:11.721 CET] [pgtest02] [:] [] [26466] [0] LOG: server process (PID 12002) was terminated by signal 11: Segmentation fault [2009-01-29 13:55:11.721 CET] [pgtest02] [:] [] [26466] [0] LOG: terminating any other active server processes [2009-01-29 13:55:11.725 CET] [pgtest02] [:] [] [26466] [0] LOG: all server processes terminated; reinitializing [2009-01-29 13:55:11.931 CET] [pgtest02] [:] [] [12018] [0] LOG: database system was interrupted; last known up at 2009-01-29 13:42:05 CET [2009-01-29 13:55:11.931 CET] [pgtest02] [:] [] [12018] [0] LOG: database system was not properly shut down; automatic recovery in progress [2009-01-29 13:55:11.934 CET] [pgtest02] [:] [] [12018] [0] LOG: record with zero length at 0/4A2F9A0 [2009-01-29 13:55:11.934 CET] [pgtest02] [:] [] [12018] [0] LOG: redo is not required [2009-01-29 13:55:11.986 CET] [pgtest02] [fotoportal:postgres] [192.168.6.137(49650)] [12019] [0] FATAL: the database system is in recovery mode [2009-01-29 13:55:11.988 CET] [pgtest02] [fotoportal:postgres] [192.168.6.137(49655)] [12020] [0] FATAL: the database system is in recovery mode [2009-01-29 13:55:12.010 CET] [pgtest02] [:] [] [12023] [0] LOG: autovacuum launcher started [2009-01-29 13:55:12.011 CET] [pgtest02] [:] [] [26466] [0] LOG: database system is ready to accept connections -- Tommy Gildseth DBA, Gruppe for databasedrift Universitetet i Oslo, USIT m: +47 45 86 38 50 t: +47 22 85 29 39 -- 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 segmentation fault
It works on one of my servers:
SELECT version();
version
-
PostgreSQL 8.3.4 on i686-pc-linux-gnu, compiled by GCC cc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-59)
The one it fails on is running:
SELECT version();
version
PostgreSQL 8.3.5 on x86_64-unknown-linux-gnu, compiled by GCC cc (GCC)
3.2.3 20030502 (Red Hat Linux 3.2.3-59)
Oleg Bartunov wrote:
Tommy,
I tried your example and didn't find any problem.
My postgresql version is 8.3.3 and I didn't use stopwords, since I don't
have them.
arxiv=# select version();
version
PostgreSQL 8.3.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.2.3
(1 row)
arxiv=# select ts_lexize('no_ispell',
'overbuljongterningpakkmesterassistent');
ts_lexize
--
{over,buljong,terning,pakk,mester,assistent}
(1 row)
Time: 922.364 ms
Oleg
On Thu, 29 Jan 2009, Tommy Gildseth wrote:
While trying to create a new dictionary for use with PostgreSQL text
search, I get a segfault. My Postgres version is 8.3.5
The dictionary I use is the Norwegian Bokm?l Nynorsk (Norway) pack
for OOo 2.x downloaded from
http://wiki.services.openoffice.org/wiki/Dictionaries#Norwegian_.28Norway.29
I've also uploaded the dictionary and affix-file used to this location:
http://folk.uio.no/tommygi/postgres/nb_no.dict
http://folk.uio.no/tommygi/postgres/nb_no.affix
These are unpacked from the zip-file I got from the above location,
and converted to UTF-8 with the following commands:
- iconv -f latin1 -t utf-8 nb_NO.dic nb_no.dict
- iconv -f latin1 -t utf-8 nb_NO.aff nb_no.affix
The command I use is this:
CREATE TEXT SEARCH DICTIONARY no_ispell (
TEMPLATE = ispell,
DictFile = nb_no,
AffFile = nb_no,
StopWords = norwegian
);
I've uploaded a strace of the process at
http://folk.uio.no/tommygi/postgres/strace.txt
This is captured with strace -ppid. Since this is about the extent
of my knowledge of strace, please let me know if there's any other
options I should specify.
Relevant parts of the postgres log file is included below.
[2009-01-29 13:55:11.721 CET] [pgtest02] [:] [] [26466] [0] LOG:
server process (PID 12002) was terminated by signal 11: Segmentation
fault
[2009-01-29 13:55:11.721 CET] [pgtest02] [:] [] [26466] [0] LOG:
terminating any other active server processes
[2009-01-29 13:55:11.725 CET] [pgtest02] [:] [] [26466] [0] LOG: all
server processes terminated; reinitializing
[2009-01-29 13:55:11.931 CET] [pgtest02] [:] [] [12018] [0] LOG:
database system was interrupted; last known up at 2009-01-29 13:42:05 CET
[2009-01-29 13:55:11.931 CET] [pgtest02] [:] [] [12018] [0] LOG:
database system was not properly shut down; automatic recovery in
progress
[2009-01-29 13:55:11.934 CET] [pgtest02] [:] [] [12018] [0] LOG:
record with zero length at 0/4A2F9A0
[2009-01-29 13:55:11.934 CET] [pgtest02] [:] [] [12018] [0] LOG: redo
is not required
[2009-01-29 13:55:11.986 CET] [pgtest02] [fotoportal:postgres]
[192.168.6.137(49650)] [12019] [0] FATAL: the database system is in
recovery mode
[2009-01-29 13:55:11.988 CET] [pgtest02] [fotoportal:postgres]
[192.168.6.137(49655)] [12020] [0] FATAL: the database system is in
recovery mode
[2009-01-29 13:55:12.010 CET] [pgtest02] [:] [] [12023] [0] LOG:
autovacuum launcher started
[2009-01-29 13:55:12.011 CET] [pgtest02] [:] [] [26466] [0] LOG:
database system is ready to accept connections
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
--
Tommy Gildseth
DBA, Gruppe for databasedrift
Universitetet i Oslo, USIT
m: +47 45 86 38 50
t: +47 22 85 29 39
--
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 segmentation fault
Yes, originaly I used a customized norwegian.stop-file, but I changed that to the one that comes with PostgreSQL, and got the same error. How do I make a backtrace? Teodor Sigaev wrote: Could you provide a backtrace? Do you use unchanged norwegian.stop file? I'm not able to reproduce the bug - postgres just works. Tommy Gildseth wrote: While trying to create a new dictionary for use with PostgreSQL text search, I get a segfault. My Postgres version is 8.3.5 -- 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 segmentation fault
I tried without specifying a StopWords-list as well, but same thing happens. Teodor Sigaev wrote: Could you provide a backtrace? Do you use unchanged norwegian.stop file? I'm not able to reproduce the bug - postgres just works. Tommy Gildseth wrote: While trying to create a new dictionary for use with PostgreSQL text search, I get a segfault. My Postgres version is 8.3.5 -- 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 segmentation fault
Teodor Sigaev wrote: How do I make a backtrace? - if you have coredump, just execute gdb /PATH1/postgres gdb /PATH2/core and type bt. Linux doesn't make core by default, so you allow to do it by ulimit -c unlimited for postgresql user - connect to db, and attach gdb to backend process: gdb /PATH1/postgres BACKEND_PID and type run in gdb, next, execute CREATE DICTIONARY and type bt in gdb I am completely unfamiliar with gdb, but hopefully, this is what you are after? (gdb) continue Continuing. Program received signal SIGSEGV, Segmentation fault. [Switching to Thread 182898870848 (LWP 606)] 0x005a6f65 in makeCompoundFlags () (gdb) bt #0 0x005a6f65 in makeCompoundFlags () #1 0x005a712a in mkSPNode () #2 0x005a709b in mkSPNode () #3 0x005a709b in mkSPNode () #4 0x005a709b in mkSPNode () #5 0x005a71a2 in mkSPNode () #6 0x005a734d in NISortDictionary () #7 0x005a4dae in dispell_init () #8 0x00638823 in OidFunctionCall1 () #9 0x004f4800 in verify_dictoptions () #10 0x004f48da in DefineTSDictionary () #11 0x0059c4fe in PortalRunUtility () #12 0x0059c5dc in PortalRunMulti () #13 0x0059bf28 in PortalRun () #14 0x005977d2 in exec_simple_query () #15 0x0059a874 in PostgresMain () #16 0x00572821 in BackendRun () #17 0x00572112 in BackendStartup () #18 0x0057034c in ServerLoop () #19 0x0056f938 in PostmasterMain () #20 0x00529f5b in main () -- 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 segmentation fault
Teodor Sigaev wrote: I reproduced the bug with a help of Grzegorz's point for 64-bit box. So, patch is attached and I'm going to commit it Thanks a lot. Exceptional response time :D Less than 2.5 hours from problem reported, till a patch was made. Don't think there's many projects or commercial products that can compete with that ;-) -- 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] 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
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
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
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
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
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
[GENERAL] dbsamples from pgfoundry
Hi, I would like some pointers as to how to install the dbsamples so that I can use them in Postgres. These .tar.qz files seem to contain *.sql files. These seem to be text files but I do not understand how to import to postgres or if there is some command line command to run. These samples are on http://www.postgresql.org/ftp/projects/pgFoundry/dbsamples/ Tommy.
[GENERAL] Sample databases
Hi, I would like some pointers as to how to install the dbsamples so that I can use them in Postgres. These .tar.qz files seem to contain *.sql files. These seem to be text files but I do not understand how to import to postgres or if there is some command line command to run. These samples are on http://www.postgresql.org/ftp/projects/pgFoundry/dbsamples/ Tommy.
Re: [GENERAL] Minor bug/inconveniance with restore from backup, using PITR base backup and archived wal files
Tom Lane wrote: Tommy Gildseth [EMAIL PROTECTED] writes: After a bit of looking around, and with some help from the fine people in #postgresql on freenode, I think I figured out what was going on. The last wal archive file was 00010003009F, and after finishing recovery, postgresql created the file 00020003009F (ie. 0002 instead of 0001) in pg_xlog. It's customary for PG to create new XLOG segments by recycling old ones. The wal-files were archived read-only, and this file permission seemed to be carried over to the new file created by postgresql in pg_xlog, causing the cluster to fall over and die. I would say that the bug is in your restore script: it should have made sure that the files it copies into the xlog directory are given the right ownership/permissions. Well, the restore command(script) is simply copied from the suggestion in the manual (restore_command = 'cp /path/to/my/archived/wal/files/%f %p'). In my opinion, it's not very obvious that the last wal file needs read/write permissions set, and it's certainly not documented anywhere on http://www.postgresql.org/docs/current/static/continuous-archiving.html that I can see. There's also the matter of the inconsistency that postgresql knows to recycle *and* chmod the file if it's originally located in pg_xlog/ folder, but not if it's originally located in the wal files archive folder. I guess it's more of a gotcha than a bug per se. -- 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] Minor bug/inconveniance with restore from backup, using PITR base backup and archived wal files
I've recently been testing our backup/restore procedures, and discovered a minor inconvenience. I emptied out the data directory(on a test-box), and restored it from a backup. I made sure that pg_xlog and pg_xlog/archive_status was empty. I then set up the recovery.conf file in the root of the data directory, and pointed restore_command in the direction of the archived wal files. After starting postgresql, the cluster started restoring from the archive logs, and everything looks fine, and doing what it should. The problem came at the end of the recovery, after the log line: [2008-09-23 15:33:14.764 CEST] [pgtest01] [:] [] [18393] [] LOG: archive recovery complete followed immediately after by this line: [2008-09-24 13:04:52.168 CEST] pgtest01] [:] [] [13324] [] PANIC: could not open file pg_xlog/00020003009F (log file 3, segment 159): Permission denied and, the cluster shutting down. After a bit of looking around, and with some help from the fine people in #postgresql on freenode, I think I figured out what was going on. The last wal archive file was 00010003009F, and after finishing recovery, postgresql created the file 00020003009F (ie. 0002 instead of 0001) in pg_xlog. The wal-files were archived read-only, and this file permission seemed to be carried over to the new file created by postgresql in pg_xlog, causing the cluster to fall over and die. Changing permissions on the last wal-file in the archive directory to read/write made this problem go away. Moving the last wal file from the archive folder to pg_xlog/ or, if I had partial archive log files in pg_xlog, also made this problem go away, irrespective of whether the file was chmod read only, or read/write. Simply chmoding the wal-file in pg_xlog/, and trying to restart the cluster did not work, and I found no other way to recover from this, other than to start over again from the beginning. -- 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] Minor bug/inconveniance with restore from backup, using PITR base backup and archived wal files
Tommy Gildseth wrote: I've recently been testing our backup/restore procedures, and discovered a minor inconvenience. Running 8.2.9 btw -- 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] Stuck query
Erik Jones wrote: On Sep 10, 2008, at 5:57 AM, Tommy Gildseth wrote: Tom Lane wrote: Tommy Gildseth [EMAIL PROTECTED] writes: Richard Huxton wrote: For what it's worth, I've run into a situation similar to this with a client a couple time in the last week or two (I can't say identical as I don't know all of the details about the client end of your connection). Using the client port # you can use lsof in addition to netstat (lsof -i tcp:49004) to track down the client process. In our case, the client process was a connection made via an ssh tunnel and was sitting in FIN_WAIT2 status. Killing the client process individually made everything go away nicely without any kind of extra downtime necessary. Thanks, I'll keep that in mind if/when it happens again. -- 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] Stuck query
I have a query on a database that has been running for nearly 24 hours at the moment. The query itself doesn't seem like it should take very long to run, so it seems like there's something else going on here. The output from pg_stat_activity looks like this: SELECT procpid, waiting, current_query, query_start, backend_start FROM pg_stat_activity WHERE current_query 'IDLE' AND usename 'postgres'; procpid | waiting | current_query | query_start | backend_start -+-+-+---+--- 17504 | f | SELECT cam.netboxid,| 2008-09-09 13:44:01.035885+02 | 2008-09-09 13:43:58.613948+02 :ifindex, :arp.ip, :REPLACE(mac::text, ':', '') AS portname, :cam.start_time, :cam.end_time, :vlan : FROM cam : JOIN netbox USING (netboxid) : JOIN arp USING (mac) : JOIN prefix ON (arp.prefixid = prefix.prefixid) : JOIN vlan USING (vlanid) : WHERE cam.end_time='infinity' : AND arp.end_time='infinity' : AND vlan IS NOT NULL : SELECT pg_cancel_backend(17504) has no effect, neither does kill 17504 from the shell. I tried strace -p17504, and this gave me just the following output: sendto(7, \7\0\0\0\003771\0\0\0\00224\0\0\0\017127.120.213.18..., 968, 0, NULL, 0 unfinished ... Does anyone have any further troubleshooting suggestions that I can do, to figure out why this query have crashed? The pg version is 8.2.9 on RHEL4 -- 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] Stuck query
Richard Huxton wrote: Tommy Gildseth wrote: SELECT pg_cancel_backend(17504) has no effect, neither does kill 17504 from the shell. Strange. I tried strace -p17504, and this gave me just the following output: sendto(7, \7\0\0\0\003771\0\0\0\00224\0\0\0\017127.120.213.18..., 968, 0, NULL, 0 unfinished ... Looks like part of your query results being sent. Is it hung in that one system-call? Yes, I left it there for about ~1 hour, and that was all that ever came. Does anyone have any further troubleshooting suggestions that I can do, to figure out why this query have crashed? Is the client locked/crashed too? If it's connected over a network, is the connection still there? We stopped the client application to see if that would make any difference, but the connection and the query on the server side is still there. A strace of the client application before it was shut down simply showed: strace -p6721 Process 6721 attached - interrupt to quit accept(18, unfinished ... so, not very helpfull. The pg version is 8.2.9 on RHEL4 Fairly standard setup. I've seen various problems reported by selinux oddities, but nothing quite like this. We don't use selinux. -- 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] Stuck query
Tom Lane wrote: Tommy Gildseth [EMAIL PROTECTED] writes: Richard Huxton wrote: Looks like part of your query results being sent. Is it hung in that one system-call? Yes, I left it there for about ~1 hour, and that was all that ever came. Seems like you have got a network issue. What does netstat show for the status of that connection? I don't think that a query cancel will blow PG off the send; you'd probably have to resort to kill -9 on that process (with a consequent restart of other sessions). It's odd that the kernel hasn't given up on the connection yet ... Netstat showed: netstat -a --tcp -p | grep 49004 tcp 0 44660 dbserver:postgres clientserver:49004 ESTABLISHED 17504/postgres: nav I went back to the server the client was running on to double check, and it seems the client process hadn't been killed off when the application was restarted. We've got some scheduled downtime tomorrow, so I think I'll just leave it till then, since it's not causing any problems as far as I can tell. -- 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] big database with very small dump !?
Joao Ferreira gmail wrote:
On Mon, 2008-08-11 at 12:45 -0400, Greg Smith wrote:
On Mon, 11 Aug 2008, Joao Ferreira gmail wrote:
I'm finding it very strange that my pg takes 9Giga on disk but
pg_dumpall produces a 250Mega dump. 'VACUUM FULL' was executed
yesterday.
If you've been running VACUUM FULL, it's probably so-called index bloat.
Try running the query at http://wiki.postgresql.org/wiki/Disk_Usage to
figure out where all your space has gone inside the database.
egbert=# SELECT nspname || '.' || relname AS relation,
egbert-# pg_size_pretty(pg_relation_size(nspname || '.' || relname))
AS size
egbert-# FROM pg_class C
egbert-# LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
egbert-# WHERE nspname NOT IN ('pg_catalog', 'information_schema')
egbert-# AND nspname !~ '^pg_toast'
egbert-# AND pg_relation_size(nspname || '.' || relname)0
egbert-# ORDER BY pg_relation_size(nspname || '.' || relname) DESC
egbert-# LIMIT 20;
relation | size
--+-
public.timeslots_strs_var_ts_key | 5643 MB #this is a UNIQUE clause
public.timeslots | 2660 MB #this is the only table
public.timeslots_timestamp_index | 583 MB #this is an index
public.timeslots_var_index | 314 MB #this is an index
public.timeslots_timeslot_index | 275 MB this is an index
(5 rows)
so it seems that the UNIQUE clause is taking up more space than the data
itself...
stil I have 2660 MB of data but the dump is about 10x smaller !!!
any hints ?
I would try running a cluster on the table. This will usually clean up
things and free diskspace both in the table and the indexes.
It does require quite extensive locking though, so might not be an
option if you can't afford having the database unavailable for a few
(10-15) minutes.
--
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] big database with very small dump !?
Joao Ferreira gmail wrote: On Mon, 2008-08-11 at 10:58 -0600, Scott Marlowe wrote: If I try cluster, I'm guessing I'll choose the big index and forget about the smaller ones... is this right ? CLUSTER will sort out all the indexes, even though you're just clustering on on. -- Tommy Gildseth DBA, Gruppe for databasedrift Universitetet i Oslo, USIT m: +47 45 86 38 50 t: +47 22 85 29 39 -- 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] Method to detect certain characters in column?
Ian Meyer wrote: So I have a column that contains usernames that have characters such as Ã(c)(R), for example: fuchÃ(c)r.. is there any way to find names with non A-Za-z0-9? ...WHERE col ~ '[^a-zA-Z0-9]'; Someone with a bit more regex fu can probably condense down the regex. -- 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] ERROR: missing FROM-clause entry for table on postgresql 8.1 not on 7.4; deleting from a join
m laks wrote: Hi, I am migrating to postgresql 8.1 from 7.4 with debian. Now I notice that one of my perl DBI scripts is crashing with the error above, and worked fine before. LTA_IDB=# delete from instancetable where ( (imagelevel.serparent= '1.2.840.113704.1.111.4640.1185891989.4') and (instancetable.imageuid= imagelevel.sopinsuid));(at least in postgresql 7.4 using perl DBI). It worked on postgresql 7.4 and now on postgresql 8.1 I get error: ERROR: missing FROM-clause entry for table imagelevel How about something along these lines: delete from instancetable USING imagelevel WHERE ( (imagelevel.serparent= '1.2.840.113704.1.111.4640.1185891989.4') and (instancetable.imageuid= imagelevel.sopinsuid) ); -- Tommy Gildseth DBA, Gruppe for databasedrift Universitetet i Oslo, USIT m: +47 45 86 38 50 t: +47 22 85 29 39 -- 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] ERROR: missing FROM-clause entry for table on postgresql 8.1 not on 7.4; deleting from a join
m laks wrote: --- On *Tue, 6/17/08, Tommy Gildseth /[EMAIL PROTECTED]/* wrote: F How about something along these lines: delete from instancetable USING imagelevel WHERE ( (imagelevel.serparent= '1.2.840.113704.1.111.4640.1185891989.4') and (instancetable.imageuid= imagelevel.sopinsuid) ) Thank You so much!! It works perfectly! 1. Where is this documented, and 2. where can I read more about what changed between the 7 series and 8 series that caused this? Thank you so much! Mitchell As usual, the fine manual :-) http://www.postgresql.org/docs/8.1/interactive/sql-delete.html -- 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] Stored procs / functions - execution failure
Damian Georgiou wrote:
Hi All,
I am having an issue with a function where it used to run in a previous
installation of postgres under windows. The box has since been
ERROR: function sp_schedulefromdate(unknown) does not exist
LINE 1: select sp_scheduleFromDate('2008-01-01');
^
HINT: No function matches the given name and argument types. You may
need to add explicit type casts.
I suppose you need to add an explicit cast, as it says in the error
message. This means that instead of sp_scheduleFromDate('2008-01-01');
which is what you got now, you use
sp_scheduleFromDate('2008-01-01'::date); which seems to be what the
function expects.
--
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] Re: Accessing other databases with DBLink when leaving user/password empty
Hermann Muster wrote: Hi Adrian, I tried what you suggested, but still get the following Error: Error connecting to the server: fe_sendauth: no password supplied What is it I'm doing wrong? Isn't it possible to leave the password empty so that PostgreSQL can retrieve it from the current account? Your login password isn't kept anywhere in the session, so it's not possible for dblink to retrieve it. Furthermore, allowing passwordless authentication via dblink is considered a security risk, as it's potentially possible to escalate your access privileges to superuser. See http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2007-3278 and http://www.securityfocus.com/archive/1/archive/1/471541/100/0/threaded for more info on this issue. -- Tommy Gildseth DBA, Gruppe for databasedrift Universitetet i Oslo, USIT m: +47 45 86 38 50 t: +47 22 85 29 39 -- 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 of update
sam wrote: Hi Iam trying to update a database table with approx 45000 rows. Iam not updating all rows at a time. Iam updating 60 rows at a given time for example. and this is happening in a FOR LOOP. A function that has the update statements is called within the loop. The updates take too long.is postgres slow in doing updates on large tables or is it because of the function call within the loop??? 45000 rows isn't a large table, and there's no reason why it should be slow in updating 60 rows. Did you VACUUM ANALYZE; your table recently? Are the columns you use in your WHERE indexed? What does EXPLAIN ANALYZE say? -- 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] ascii to utf-8
Tom Hart wrote: Hello everybody. I hope your week's going well so far. I built our data mine in postgreSQL around 3 months ago and I've been working with it since. Postgres is great and I'm really enjoying it, but I've hit a bit of a hitch. Originally (and against pgAdmin's good advice, duh!) I set up the database to use ASCII encoding. However we have a large base of Spanish speaking members and services, and we need utf-8 ...snip snip pg_restore: [archiver (db)] COPY failed: ERROR: invalid byte sequence for encoding UTF8: 0xc52f HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by client_encoding. CONTEXT: COPY transaction, line 209487 WARNING: errors ignored on restore: 1 Try editing your dump-file and change the line which reads SET client_encoding = 'SQL_ASCII'; to SET client_encoding = 'LATIN1'; -- Tommy Gildseth ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] The .pgpass file
Hello, How do I specify in the command-line to access the .pgpass file when creating a database? I would like to use ~/.pgpass instead of –W on the command line when creating a database (createdb) and was wondering the correct syntax. For example, I don’t want to have to include (-W): C:\postgressql\bincreatedb -U postgres –p 5432 -W –E UTF8 myDatabase and substitute it for ~/.pgpass or PGPASSFILE: C:\postgressql\bincreatedb -U postgres –p 5432 ~/.pgpass –E UTF8 myDatabase How do I inform the complier to extract the password from the file and not the prompt the user? Any suggestions would be greatly appreciated. Tommy_ _ R U Ready for Windows Live Messenger Beta 8.5? Try it today! http://entertainment.sympatico.msn.ca/WindowsLiveMessenger
[GENERAL] ‘--pwfile’ command
Hello, Could someone tell me if the ‘--pwfile’ command can be applied to createdb.exe and psql.exe? I found a description of ‘--pwfile’ at http://www.it-manuals.info/postgressql/app-initdb.html, which states: --pwfile=filename makes initdb read the database superuser's password from a file. the first line of the file is taken as the password. I would like to disable password prompt (-W) and have the password read from a file. Any pointers would surely be appreciated. Thanks, Tommy_ _ Are you ready for Windows Live Messenger Beta 8.5 ? Get the latest for free today! http://entertainment.sympatico.msn.ca/WindowsLiveMessenger
Re: [GENERAL] (Never?) Kill Postmaster?
Pavel Stehule wrote: 2007/10/24, Stefan Schwarzer [EMAIL PROTECTED]: I read dozens of times the TIP 2: Don't 'kill -9' the postmaster... Now, what am I supposed to do if I launched a query which takes ages, and which I want to interrupt? you have to use more gently way select pg_cancel_backend() http://www.postgresql.org/docs/current/interactive/functions-admin.html The problem is that pg_cancel_backend() is somewhat unreliable at cancelling wayward queries. You can try other options for kill though, other than -9, which is kind of a last resort. -- Tommy Gildseth ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] ORDER BY - problem with NULL values
Stefan Schwarzer wrote: Hi there, if I order a given year in DESCending ORDER, so that the highest values (of a given variable) for the countries are displayed at the top of the list, then actually the NULL values appear as first. Only below, I find the values ordered correctly. Is there any way to a) make the countries with NULL values appear at the bottom of the list ... ORDER BY y_2000 IS NULL [DESC], y_2000; b) neglect the NULL values by still allowing the countries to be displayed Not quite sure what you mean by this. -- Tommy Gildseth ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] URGENT: Whole DB down (no space left on device)
Phoenix Kiula wrote: I am getting this message when I start the DB: psql: FATAL: could not access status of transaction 0 DETAIL: Could not write to file pg_subtrans/01F8 at offset 221184: No space left on device. What is this about and how do I solve this? A df -h on my system shows this: /dev/sda2 ext39.9G 9.5G 0 100% /var Well, the error message is pretty clear, and assuming you don't keep your database in any non-standard location, you /var partition is indeed full. -- Tommy Gildseth ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Internal Postgre SQL documentation
Cantor wrote: On Aug 7, 1:26 pm, Arthernan [EMAIL PROTECTED] wrote: I want to learn how a real database works. And I am about to start reading the Postgre source code. Are there any online documents that may document the code? Even if it was a general guideline. Any information will be greatly appreciated. Arturo Hernandez OK, I did find doc/FAQ_DEV inside the source tree. And it did include these two links for question 1.6) Where can I learn more about the code? http://www.postgresql.org/developer http://neilconway.org/talks/hacking/ http://doxygen.postgresql.org/ is probably usefull too. -- Tommy Gildseth ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Is this a bug? Sequences and rules
Chris Travers wrote:
Hi;
I noticed that rules were not behaving properly. I created a test
case, and it looks like the sequence is getting double-incrimented.
Is this the way this is supposed to work?
I know triggers would be better for something like this but I find
these results... surprising
postgres=# insert into test1 (test) values (1);
This insert is implicitly specifying that you should use the default
value as the value for the ID column. The default is
nextval('test1_id_seq'), so this is what gets inserted into test2 as
well, thus incrementing the sequence twice.
--
Tommy Gildseth
--
Tommy Gildseth
DBA, Gruppe for databasedrift
Universitetet i Oslo, USIT
m: +47 45 86 38 50
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] UPDATE on two large datasets is very slow
Martijn van Oosterhout wrote: On Mon, Apr 02, 2007 at 08:24:46PM -0700, Steve Gerhardt wrote: I've been working for the past few weeks on porting a closed source BitTorrent tracker to use PostgreSQL instead of MySQL for storing statistical data, but I've run in to a rather large snag. The tracker in question buffers its updates to the database, then makes them all at once, sending anywhere from 1-3 MiB of query data. With MySQL, this is accomplished using the INSERT INTO...ON DUPLICATE KEY UPDATE query, which seems to handle the insert/update very quickly; generally it only takes about a second for the entire set of new data to be merged. For the record, this is what the SQL MERGE command is for... I don't think anyone is working on implementing that though... This will possibly provide a solution to this question: http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE -- Tommy ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Best way to store and retrieve photo from PostGreSQL
Andreas Kretschmer wrote: Alain Roger [EMAIL PROTECTED] schrieb: Hi, I would like to store picture in my DB and after to display them on my PHP pages. What is the best solution for that ? Store the pictures in the filesystem and only the path, description and other metadata in the database. My suggestion ;-) One problem with this approach, is that you move the responsibility for maintaining data integrity from the database, to the application code using the database. You introduce 2 points of failure, where you risk adding the image to the filesystem, without it being added to the database, and the other way around. The same issue appears with deleting/updating. In addition, if there is a large amount of images, you probably do not want to keep all images in the same folder. So you introduce extra complexity in order to maintain a directory structure, and some sort of logic for sorting the images in this structure. -- Tommy ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] postgresql vs mysql
Ben wrote: I'm sorry maybe I missed something, but if you don't need NULLs and feel they just add extra work, why don't you just declare all your columns to be not null and have them default to zero or an empty string? which is what mySQL does by default :-) The statement CREATE TABLE foo (bar INTEGER NOT NULL, rab VARHCAR(123) NOT NULL, oof DATETIME NOT NULL,); will be rewritten automatically by mySQL to CREATE TABLE foo (bar INTEGER NOT NULL DEFAULT 0, rab VARHCAR(123) NOT NULL DEFAULT '', oof DATETIME NOT NULL DEFAULT '-00-00 00:00'); Maybe if you really want to enforce a NOT NULL constraint in mySQL, you have to declare a column as NOT NULL DEFAULT NULL, explicitly as was suggested somewhere else in this thread. Fascinating how they probably thought that was a good idea. -- Tommy ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] how to store whole XML file in database
deepak pal wrote: hi,, i have to insert whole xml file in database ,there is a text field for that.but it shows error parse error where there are attribute ..plz help me out. I'm guessing you have a quoting problem. Try escaping the XML-data before inserting it into the database, so that any single quotes doesn't mess up your insert statements. -- Tommy ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Problem with result ordering
Tom Lane wrote: Thorsten =?iso-8859-1?q?K=F6rner?= [EMAIL PROTECTED] writes: select m_id, m_u_id, m_title, m_rating from tablename where m_id in (26250, 11042, 16279, 42197, 672089); You could rewrite the query as select ... from tablename where m_id = 26250 union all select ... from tablename where m_id = 11042 union all select ... from tablename where m_id = 16279 union all select ... from tablename where m_id = 42197 union all select ... from tablename where m_id = 672089 This isn't guaranteed by the SQL spec to produce the results in any particular order either; but there's no good reason for PG to rearrange the order of the UNION arms, whereas there are plenty of good reasons to try to optimize fetching of individual rows. Or a variant of this, SELECT m_id, m_u_id, m_title, m_rating from tablename where m_id in (26250, 11042, 16279, 42197, 672089) ORDER BY m_id=26250, m_id=11042, m_id=16279, m_id=42197, m_id=672089; -- Tommy Gildseth http://www.gildseth.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Trouble creating database with proper encoding
Rob Tanner wrote: createdb -U xythos -E UNICODE XythosDocumentStoreDB createdb -U xythos -E UNICODE XythosGlobalDB When I look at what I've done with psql -l, I get List of databases Name | Owner | Encoding ---+--+-- XythosDocumentStoreDB | xythos | UTF8 XythosGlobalDB| xythos | UTF8 I dropped the databases and recreated them with unicode (lower case) but got the same results. Unicode in itself is just a standard. UTF-8 is one of the implementations of this standard. So, your database is indeed created with a unicode characterset -- Tommy ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Referencing created tables fails with message that
Thanks for all the replys, I got quite a lot of them, and haven't had time to read them all yet, but one common comment I get is You didn't supply enough information , be more specific, etc. You are of course right. From the answers I got however, it seems to be a very high propability that it is different case in table name between create and select, etc that causes the problem. It does make sense (now). I got the suggestion to quote the table names. I also switched tools after creating the first table and trying to create the next since the first tool was so simple it did not support foreign keys. That was probably a bad thing to do :-) I will drop the table in the tool that created it and then recreate them in the same tool and quote the names as suggested. I'm quite sure that will solve my problem. inMyDefense skip=OK - I've used Oracle, DB2, Mimer, and HSQLDB before, and my experience with these led me to beleive that SQL was case insensitive. In fact, I was so sure of it that a case problem just never occured to me. - Since I could create the table I assumed it was OK and there were no point in describing the table. /inMyDefense Regards, Tommy Svensson Tino Wildenhain wrote: Am Sonntag, den 27.02.2005, 18:50 -0500 schrieb Tommy Svensson: I have just installed Postgresql and tried it for the first time. One very serious problem I ran into was when actually trying to use created tables. Creating a simple table without any foreign keys works OK, but after creating the table it is not possible to do a select on it! I tried the following variants: SELECT * FROM table; SELECT * FROM public.table; SELECT * FROM schema.public.table; All result in the message The relation table does not exist! or The relation public.table does not exist!. Creating a new table with a foreign key referencing the first table is also impossible due to exactly the same error message! This behaviour is the same using DBVisualizer/jdbc or psql. So the question is how do you actually reference the tables you have created so that postgres will find them ? The tables do actually get created. I can se them in DBVisualizer. I'm using version 7.4.5 on Linux Mandrake 10.1. You should have provided an exact example of _how_ do you create your tables. I bet you created tables with mixed case (in a tool?) but dont use the quoting on these table names when you select. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Referencing created tables fails with message that
OK, I see. I first used the Postgres admin tool in webmin (Linux/unix web admin tool) to create the first table. I guess that it quoted my Project table. Anyhow, I dropped the table from the same tool, and then went to DBVisualizer and recreated all my tables there, using uppercase for all table and field names, and then it worked fine. But as you explained below, I guess it would not have mattered even if i called my first table PrOjEcT since it would be changed to project. I also realize that it is much safer to actually write the SQL yourself than let a tool produce it for you! /Tommy Michael Fuhr wrote: On Mon, Feb 28, 2005 at 12:50:25PM +0100, Tommy Svensson wrote: - I've used Oracle, DB2, Mimer, and HSQLDB before, and my experience with these led me to beleive that SQL was case insensitive. In fact, I was so sure of it that a case problem just never occured to me. Case isn't a problem if you don't quote identifiers because unquoted identifiers will be folded to lower case, both when you create them and then later when you reference them. For example, if you create a table with this command: CREATE TABLE XYZ (I INTEGER); then the system folds XYZ and I to lower case: \dt List of relations Schema | Name | Type | Owner +--+---+--- public | xyz | table | mfuhr \d xyz Table public.xyz Column | Type | Modifiers +-+--- i | integer | The following queries should all work (not an all-inclusive list): SELECT I FROM XYZ; SELECT i FROM xyz; SELECT I FROM Xyz; select i from xyz; sEleCt i fRoM xYz; But if you quote identifiers when you create them, then they'll be created with the exact case you specified and you'll need to quote them whenever you use them: CREATE TABLE XYZ (I INTEGER); \dt List of relations Schema | Name | Type | Owner +--+---+--- public | XYZ | table | mfuhr \d XYZ Table public.XYZ Column | Type | Modifiers +-+--- I | integer | SELECT I FROM XYZ; -- works SELECT I FROM XYZ; -- fails ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Referencing created tables fails with message that they do not exist!
I have just installed Postgresql and tried it for the first time. One very serious problem I ran into was when actually trying to use created tables. Creating a simple table without any foreign keys works OK, but after creating the table it is not possible to do a select on it! I tried the following variants: SELECT * FROM table; SELECT * FROM public.table; SELECT * FROM schema.public.table; All result in the message The relation table does not exist! or The relation public.table does not exist!. Creating a new table with a foreign key referencing the first table is also impossible due to exactly the same error message! This behaviour is the same using DBVisualizer/jdbc or psql. So the question is how do you actually reference the tables you have created so that postgres will find them ? The tables do actually get created. I can se them in DBVisualizer. I'm using version 7.4.5 on Linux Mandrake 10.1. Best Regards, Tommy Svensson ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] pg_proc_proname_args_nsp_index error
I've got a standard setup on Debian woody, with postgreSQL backports from http://people.debian.org/~elphick Last night, I started getting mails from the cron daemon that this command: /usr/bin/test -x /usr/lib/postgresql/bin/do.maintenance /usr/lib/postgresql/bin/do.maintenance -a -F was spitting out this errormessage: ERROR: could not read block 6 of relation pg_proc_proname_args_nsp_index: Input/output error I'm not really sure what's going on here, and how serious this is. Anyone got idea what this is, and how it can be fixed? Alternatively, links/urls to somewhere online where I can read up on this? Tommy ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
