[GENERAL] Query, usually running 300ms, sometimes hangs for hours
Hi! We're currently still on PostgreSQL 8.3.7 and are experiencing a strange problem since a couple of days. I have a suspicion on what is causing it (probably not PostgreSQL) and I'd like to hear your opinion before taking my findings to the Railo bugtracker. We're running queries like this every couple of minutes as scheduled task in Railo 3.3.0.022 rc running on Tomcat 6.0.18; connection is made via JDBC: Select distinct a.article_id, art.article_type_id , CASE when max(lc.count) is null Then 0 else max(lc.count) END as likes , CASE WHEN count_reply is null THEN 0 ELSE count_reply END as count_reply from babel_pcgames.article art inner join babel_pcgames.article_category a on a.article_id = art.article_id left join modules.likebutton_counter lc on (lc.uid = a.entity_id and lc.site_id = $1 and lc.type_id = 2) left join modules.article_comments ac on (ac.article_id = a.article_id and ac.board_id in (13)) where a.article_id in ($2,[...],$2715) Group by a.article_id, ac.count_reply, art.article_type_id Order by a.article_id Strange thing now that's happening occasionally since last Friday is, that one or more of these queries is hanging for several hours without completing. When I actually execute it in parallel to the already running query, it's coming back fine after about 300ms or less. When I try to kill -TERM [pid] on this backend, nothing happens. I actually have to kill -9 [pid] to make it go away which of course causes the Postmaster to go into recovery mode and thus leads to a short downtime of the server. I know, ~3,000 elements in the IN clause are quite a lot, but we haven't seen any problems with this before and I don't think that this is actually causing it - this same type of query has been running unchanged for more than six months now. I think that the kill [pid] not having any effect is quite suspicious. So I tried a backtrace with gdb; I haven't got full debugging support in my installation, but I get the functions nevertheless, which is better than nothing, I suppose: (gdb) bt #0 0x7f58e3394505 in send () from /lib/libc.so.6 #1 0x00546249 in internal_flush () #2 0x0054635d in internal_putbytes () #3 0x005463bc in pq_putmessage () #4 0x005479c4 in pq_endmessage () #5 0x00452eb0 in printtup () #6 0x00526408 in ExecutorRun () #7 0x005bfe50 in PortalRunSelect () #8 0x005c14b9 in PortalRun () #9 0x005be025 in PostgresMain () #10 0x00591ed2 in ServerLoop () #11 0x00592bb4 in PostmasterMain () #12 0x00548268 in main () I haven't got much experience reading stack traces, but I suspect that what this means is that PostgreSQL actually has done its job with the query and is now hanging in sending the result back to the client. This would indicate some sort of evil client behavior, i.e. connection still open but nobody answering on the other end. I expect that this might in fact explain why a kill -TERM on the backend doesn't have any effect as control over this process is currently being handled by the network stack kernel-side and there's some sort of network interrupt wait in effect here. I deployed the Railo patch from 3.3.0.018.rc to 3.3.0.022.rc on Friday, July 22nd, so the coincidence of this issue happening for the first time and the Railo patch in effect is another indication that the actual cause of the problem is client-side. Could anybody shed some more light on what I'm seeing here? I'm currently trying to run the very same job on another CFML-type server and haven't had a single issue for a couple of hours now, but as this has been hitting us only every couple of hours at best I'm still not completely sure that my assumptions have been correct. Kind regards Markus BAM! Der COMPUTEC Games Award 2011 - Jetzt abstimmen fur die besten Games: www.bamaward.de Computec Media AG Sitz der Gesellschaft und Registergericht: Furth (HRB 8818) Vorstandsmitglieder: Albrecht Hengstenberg (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jurg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- 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] Query, usually running 300ms, sometimes hangs for hours
Hi! Tom Lane wrote: Yes, that reading is correct: this stack trace shows it's blocked trying to send query results back to the client. So you need to figure out why the client is failing to accept data. Thanks; we saw one of those zombie queries again today, a simple restart of the Tomcat instance was enough to get rid of it. It's definitely an issue with the client. I shall try to get someone on the Railo mailinglist to have a more thorough look at the issue, it's definitely caused by a recent update - of the bleeding edge version though, so harm done only to those who actively invited it ;) Thanks for confirmation! Kinds regards Markus BAM! Der COMPUTEC Games Award 2011 - Jetzt abstimmen fur die besten Games: www.bamaward.de Computec Media AG Sitz der Gesellschaft und Registergericht: Furth (HRB 8818) Vorstandsmitglieder: Albrecht Hengstenberg (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jurg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- 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] Problem with to_tsquery() after restore on PostgreSQL 9.0.1
Hi! Tom Lane t...@sss.pgh.pa.us writes: So far as I can see offhand, the only way you'd get that error message is if to_tsquery were declared to take OID not regconfig as its first argument. I suspect it has to do with the Tsearch2-compatibility modules from contrib - these were compiled and installed to a couple of databases on the old cluster; I haven't yet compiled and installed them to the new databases as I'd like to get rid of some dead weight in the migration process. contrib/tsearch2 does provide a to_tsquery(oid, text) function ... I'm not sure why offhand, nor how come that's managing to show up in front of the standard definition in your search path. Try dropping that. Now I simply used the postgresql-9.0.1/contrib/tsearch2/uninstall_tsearch2.sql to get rid of the contrib DDL - that seems to have fixed the problem alright; maybe we'll have to fix some of our code that may use some old function signatures, but since we've switched to Sphinx for the more demanding FTS tasks, we're not making as much use of TSearch2 as we used to, anyway. (Another question is why it wasn't failing already in your 8.3 DB. The behavior shouldn't be any different AFAICS. Are you sure you are using the same search_path as before?) Yes, as I simply copied my old postgresql.conf to the test server. Strange, but as the primary problem seems to be solved alright, I'm happy anyway :) Kind regards Markus COMPUTEC MEDIA zieht in neue Verlagsraume! Adresse ab dem 27. September 2010: COMPUTEC MEDIA AG, Dr.-Mack-Stra?e 83, 90762 Furth. Alle sonstigen Kontaktdaten bleiben unverandert. Computec Media AG Sitz der Gesellschaft und Registergericht: Furth (HRB 8818) Vorstandsmitglieder: Albrecht Hengstenberg (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jurg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problem with to_tsquery() after restore on PostgreSQL 9.0.1
Hi!
I currently testing the upgrade of our 8.3 databases to 9.0.
In some of those restored databases to_tsquery is throwing an error:
magazine=# SELECT to_tsquery('simple','plants');
ERROR: invalid input syntax for type oid: simple
LINE 1: SELECT to_tsquery('simple','plants');
In other databases, it's working fine; the same applies to newly created
databases on this cluster.
Now my Google-fu seems to be somewhat lacking because I couldn't find
anything on this precise error condition.
I dumped the old databases using
su postgres -c time /opt/pgsql/bin/pg_dump -s mydb | gzip
~/mydb-schema.sql.`date -I`.gz
su postgres -c time /opt/pgsql/bin/pg_dump -a mydb -Fc
~/mydb-data.`date -I`.pg
Source version() is PostgreSQL 8.3.7 on x86_64-unknown-linux-gnu,
compiled by GCC gcc (Debian 4.3.2-1.1) 4.3.2
Target version() is PostgreSQL 9.0.1 on x86_64-unknown-linux-gnu,
compiled by GCC gcc (Debian 4.4.5-4) 4.4.5, 64-bit
I suspect it has to do with the Tsearch2-compatibility modules from
contrib - these were compiled and installed to a couple of databases on
the old cluster; I haven't yet compiled and installed them to the new
databases as I'd like to get rid of some dead weight in the migration
process.
http://www.postgresql.org/docs/9.0/static/tsearch2.html doesn't
elaborate further on how to get rid of the replacement tsearch2 module
stuff.
Any ideas on how to resolve this issue?
Kind regards
Markus
COMPUTEC MEDIA zieht in neue Verlagsraume! Adresse ab dem 27. September 2010:
COMPUTEC MEDIA AG, Dr.-Mack-Stra?e 83, 90762 Furth. Alle sonstigen Kontaktdaten
bleiben unverandert.
Computec Media AG
Sitz der Gesellschaft und Registergericht: Furth (HRB 8818)
Vorstandsmitglieder: Albrecht Hengstenberg (Vorsitzender) und Rainer Rosenbusch
Vorsitzender des Aufsichtsrates: Jurg Marquard
Umsatzsteuer-Identifikationsnummer: DE 812 575 276
--
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] Transaction wraparound problem with database postgres
Hi! From: Scott Marlowe [mailto:scott.marl...@gmail.com] Do your logs show any kind of error when vacuuming about only owner can vacuum a table or anything? I grepped through the logs from the last four days and, no, there were none such errors whatsoever. Last vacuum analyze run returned the following: INFO: free space map contains 1974573 pages in 9980 relations DETAIL: A total of 2043408 page slots are in use (including overhead). 2043408 page slots are required to track all free space. Current limits are: 210 page slots, 1 relations, using 13376 kB. I have since increased these limits by 50% as we've come quite close to what was configured. But as they hadn't been reached yet anyway, so I don't think we did have any sort of apparent problem with the running of vaccuum as such. Kind regards Markus Computec Media AG Sitz der Gesellschaft und Registergericht: Furth (HRB 8818) Vorstandsmitglieder: Albrecht Hengstenberg (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jurg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- 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] Transaction wraparound problem with database postgres
Hi! After going several months without such incidents, we now got bit by the same problem again. We have since upgraded the hardware we ran the database cluster on and currently use version 8.3.7. The general outline of the problem hasn't changed much though - we still don't use the database 'postgres' except for one lone pgagent-job which has only been configured a couple of weeks back and we do a nightly vacuum over all databases in addition the the running of the autovacuum daemon. As I expect that this might hit again in a couple of months: Any suggestions on what sort of forensic data might be required to actually find out the root of what's causing it? As I needed to get the cluster back up and running again, I used the same remedy as last time and simply dropped the database and recreated it from template1, so there's not much left to be looked into right now, but if I knew what kind of data to retain I mit be able to come up with some more useful info next time... Kind regards Markus -Ursprüngliche Nachricht- Von: Markus Wollny Gesendet: Freitag, 21. März 2008 23:34 An: 'Tom Lane' Cc: pgsql-general@postgresql.org Betreff: AW: [GENERAL] Transaction wraparound problem with database postgres Tom Lane wrote: Markus Wollny markus.wol...@computec.de writes: I'd still like to find out what exactly happened here so I can prevent the same from happening again in the future. Me too. It would seem that something did a vacuum of postgres with a strange choice of xid cutoff, but I can't think of what would cause that. Do you ever do VACUUM FREEZE on your databases? No, I actually never heard of VACUUM FREEZE, I have to admit. Computec Media AG Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818) Vorstandsmitglieder: Albrecht Hengstenberg (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jürg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Massive table bloat
Hi! I've set up some system to track slow page executions in one of our (as yet not live) web apps. The tracking itself is handled completely within the database using a function. Within a very short time (approx. 1 week) and although we haven't got that much traffic on our testpages, the table in question as grown beyond a size of 23 GB, even though a SELECT count(*) on it will tell me that it only contains 235 rows. I'm sure I must be missing something obvious here... Here's the DDL for the table: CREATE TABLE stats.slowpages ( url text NOT NULL, lastexecduration integer NOT NULL, avgslowexecduration integer, execcount integer, lastexectime timestamp without time zone, site_id integer NOT NULL, slowestexecduration integer, totaltimespent bigint, CONSTRAINT slowpages_pkey PRIMARY KEY (url) )WITHOUT OIDS; -- Indexes CREATE INDEX idx_slowpages_duration ON stats.slowpages USING btree (lastexecduration); CREATE INDEX idx_slowpages_avgduration ON stats.slowpages USING btree (avgslowexecduration); CREATE INDEX idx_slowpages_execcount ON stats.slowpages USING btree (execcount); CREATE INDEX idx_slowpages_lastexec ON stats.slowpages USING btree (lastexectime); CREATE INDEX idx_slowpages_site ON stats.slowpages USING btree (site_id); CREATE UNIQUE INDEX uidx_slowpages_url_site ON stats.slowpages USING btree (url, site_id); CREATE INDEX idx_slowpages_totaltimespent ON stats.slowpages USING btree (totaltimespent); And this here is the function we use to insert or update entries in this table: CREATE or REPLACE FUNCTION stats.iou_slowpages( IN _site_id integer, IN _url text, IN _duration integer) RETURNS void AS $BODY$ BEGIN LOOP UPDATE stats.slowpages SET avgslowexecduration = ((avgslowexecduration*execcount)+_duration)/(execcount+1) ,execcount = execcount+1 ,lastexectime = now() ,lastexecduration = _duration ,totaltimespent = totaltimespent + _duration ,slowestexecduration = CASE WHEN _duration slowestexecduration THEN _duration ELSE slowestexecduration END WHERE url = _url AND site_id = _site_id; IF found THEN RETURN; END IF; BEGIN INSERT INTO stats.slowpages(url,lastexecduration,avgslowexecduration,slowestexecdura tion,totaltimespent,execcount,lastexectime,site_id) VALUES (_url, _duration, _duration,_duration,_duration, 1, now(), _site_id); RETURN; EXCEPTION WHEN unique_violation THEN END; END LOOP; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; _site_id is a small integer value, _url is a full URL string to a page and _duration is a value in milliseconds. We're on PostgreSQL 8.3.7. Any idea about what I may be missing here? Kind regards Markus Computec Media AG Sitz der Gesellschaft und Registergericht: Furth (HRB 8818) Vorstandsmitglieder: Albrecht Hengstenberg (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jurg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- 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] Massive table bloat
-Ursprüngliche Nachricht- Von: Thom Brown [mailto:thombr...@gmail.com] Gesendet: Mittwoch, 3. März 2010 16:56 An: Markus Wollny Cc: pgsql-general@postgresql.org Betreff: Re: [GENERAL] Massive table bloat If you update rows, it actually creates a new version of it. The old one doesn't get removed until the VACUUM process cleans it up, so maybe you need to run that against the database? I already do on a nightly basis (which is probably not often enough in this case) and have got autovacuum running. I'll check into FSM settings as suggested by Grzegorz Jaśkiewicz, there's probably half a solution to the problem there, the other half being probably the autovacuum daemon not visiting this table nearly often enough. Kind regards Markus Computec Media AG Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818) Vorstandsmitglieder: Albrecht Hengstenberg (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jürg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- 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] Postgres won't start. Nothing in the log.
Hi! pgsql-general-ow...@postgresql.org wrote: Unfortunately there is nothing anywhere telling me what the problem is. The log file is empty, there is nothing in the /var/log/messages or /var/log/syslog either. The only output I get is this. * Starting PostgreSQL 8.3 database server * The PostgreSQL server failed to start. Please check the log output. What can I do to figure out why it won't start? Did you check if the directory PostgreSQL wants to write the logfile to has appropriate rights to allow the user PostgreSQL runs under to write to it? Another fairly common issue would be a postgresql.conf that is not readable by the PostgreSQL-user. In case of such an outright failure to start, it's usually something fairly simple to fix such as file or directory permissions. Kind regards Markus Computec Media AG Sitz der Gesellschaft und Registergericht: Furth (HRB 8818) Vorstandsmitglieder: Albrecht Hengstenberg (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jurg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- 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] GIN and GiST index - more in depth info
For sure I had a look to Oleg Bartunov' s and Teodor Sigaev's website at http://www.sai.msu.su/~megera/wiki/ but for me it's still not clear how to describe the differences between the indexes and the usage scenarios when to use GIN or GiST. As far as I understand it's a matter of usage scenario. GIN is extremely slow on updates, I seem to remember somewhere that it's actually often better to simply recreate the complete index than to update it; GiST's write performance is not half as bad. On the other hand, GIN is much faster on reads than GiST. If you've got some data that is read-only in nature, you'll probably fare better with GIN. If you need frequent updates, GiST ist the better choice. In certain scenarios you would use partial indexes to have a GiST index on current, still heavily updated data, and a GIN index on older, archived rows which are not updated any longer. Kind regards Markus Jede Stimme zahlt, jetzt voten fur die besten Games: www.bamaward.de Computec Media AG Sitz der Gesellschaft und Registergericht: Furth (HRB 8818) Vorstandsmitglieder: Albrecht Hengstenberg (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jurg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Could not open file pg_clog/....
Hello! Recently one of my PostgreSQL servers has started throwing error messages like these: ERROR: could not access status of transaction 3489956864 DETAIL: Could not open file pg_clog/0D00: Datei oder Verzeichnis nicht gefunden. (file not found) The machine in question doesn't show any signs of a hardware defect, we're running a RAID-10 over 10 disks for this partition on a 3Ware hardware RAID controller with battery backup unit, the controller doesn't show any defects at all. We're running PostgreSQL 8.3.5 on that box, kernel is 2.6.18-6-amd64 of Debian Etch, the PostgreSQL binaries were compiled from source on that machine. I searched the lists and though I couldn't find an exact hint as to what's causing this, I found a suggestion for a more or less hotfix solution: Create a file of the required size filled with zeroes and then put that into the clog-directory, i.e. dd bs=262144 count=1 if=/dev/zero of=/tmp/pg_clog_replacements/0002 chown postgres.daemon /tmp/pg_clog_replacements/0002 chmod 600 /tmp/pg_clog_replacements/0002 mv /tmp/pg_clog_replacements/0002 /var/lib/pgsql/data/pg_clog I know that I'd be loosing some transactions, but in our use case this is not critical. Anyway, this made the problem go away for a while but now I'm getting those messages again - and indeed the clog-files in question appear to be missing altogether. And what's worse, the workaround no longer works properly but makes PostgreSQL crash: magazine=# vacuum analyze pcaction.article; PANIC: corrupted item pointer: 5 server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. And from the logfile: 2009-05-12 11:38:09 CEST - 6606: [loc...@magazinePANIC: corrupted item pointer: 5 2009-05-12 11:38:09 CEST - 6606: [loc...@magazineSTATEMENT: vacuum analyze pcaction.article; 2009-05-12 11:38:09 CEST - 29178: @LOG: server process (PID 6606) was terminated by signal 6: Aborted 2009-05-12 11:38:09 CEST - 29178: @LOG: terminating any other active server processes 2009-05-12 11:38:09 CEST - 6607: 192.168.222.134(57292)@magazineWARNING: terminating connection because of crash of another server process 2009-05-12 11:38:09 CEST - 6607: 192.168.222.134(57292)@magazineDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2009-05-12 11:38:09 CEST - 6569: 192.168.222.134(57214)@blueboxHINT: In a moment you should be able to reconnect to the database and repeat your command. [...] 2009-05-12 11:38:09 CEST - 29178: @LOG: all server processes terminated; reinitializing 2009-05-12 11:38:09 CEST - 6619: @LOG: database system was interrupted; last known up at 2009-05-12 11:37:51 CEST 2009-05-12 11:38:09 CEST - 6619: @LOG: database system was not properly shut down; automatic recovery in progress 2009-05-12 11:38:09 CEST - 6619: @LOG: redo starts at 172/8B4EE118 2009-05-12 11:38:09 CEST - 6619: @LOG: record with zero length at 172/8B6AD510 2009-05-12 11:38:09 CEST - 6619: @LOG: redo done at 172/8B6AD4E0 2009-05-12 11:38:09 CEST - 6619: @LOG: last completed transaction was at log time 2009-05-12 11:38:09.550175+02 2009-05-12 11:38:09 CEST - 6619: @LOG: checkpoint starting: shutdown immediate 2009-05-12 11:38:09 CEST - 6619: @LOG: checkpoint complete: wrote 351 buffers (1.1%); 0 transaction log file(s) added, 0 removed, 2 recycled; write=0.008s, sync=0.000 s, total=0.009 s 2009-05-12 11:38:09 CEST - 6622: @LOG: autovacuum launcher started 2009-05-12 11:38:09 CEST - 29178: @LOG: database system is ready to accept connections Now what exactly is causing those missing clog files, what can I do to prevent this and what can I do to recover my database cluster, as this issue seems to prevent proper dumps at the moment? Kind regards Markus Jede Stimme zahlt, jetzt voten fur die besten Games: www.bamaward.de Computec Media AG Sitz der Gesellschaft und Registergericht: Furth (HRB 8818) Vorstandsmitglieder: Albrecht Hengstenberg (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jurg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- 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] Could not open file pg_clog/....
Hi! -Ursprüngliche Nachricht- Von: Glyn Astill [mailto:glynast...@yahoo.co.uk] Gesendet: Dienstag, 12. Mai 2009 12:33 An: pgsql-general@postgresql.org; Markus Wollny The first thing I would have done if I've been forced to do that (if there was no other option?) would be a dump / restore directly afterwards, then pick through for any inconsistencies. That's a lot of data - somewhere around 43GB at the moment. And pg_dump seems to fail altogether on the affected databases, so the pg_clog issue actually means that I cannot make any current backups. Probably wait for the big-wigs to reply but perhaps a reindex may get you going. Tried that, but it also makes PostgreSQL crash, so no luck there either. I also dropped template0, recreated it from template1, did a VACUUM FREEZE on it, marked it as template again and disallowed connections. I'd definately be starting with a fresh database once I got out of the whole though... Yes, but that'll be a nightshift and I need some way to actually get at a working dump now... Kind regards Markus Jede Stimme zählt, jetzt voten für die besten Games: www.bamaward.de Computec Media AG Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818) Vorstandsmitglieder: Albrecht Hengstenberg (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jürg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- 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] Could not open file pg_clog/....
Hi! It appears to be failing on the pcaction.article table. Could you get away without that? Perhaps, and it'd be a longshot, you'd be able to dump the rest of the data with it gone? I'm going to duck out of this now though, and I think you should probably wait until someone a little more knowlegable replies. Yes, I could get away with it without any problems, as this table isn't really needed any more (just some remains of a previous site version, which I didn't yet drop so we could still take a look at it if the need should arise). It is somewhat funny that this should happen on some table that is hardy accessed at all, whereas the more popular database objects seem to be in order. But I am not yet shure, that this one is the only affected object, but I'll try to dump that db without the pcaction.schema, see what happens. Kind regards Markus Jede Stimme zahlt, jetzt voten fur die besten Games: www.bamaward.de Computec Media AG Sitz der Gesellschaft und Registergericht: Furth (HRB 8818) Vorstandsmitglieder: Albrecht Hengstenberg (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jurg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Question about function returning record
Hi!
I've got a generalized function
getshadowrecord(schema (varchar),table (varchar), id (int4),
version_id (int))
which returns RECORD. As this function is supposed to work on any table
structure, I need to declare a column alias list when I do a SELECT on
it, like
SELECT *
FROM getshadowrecord('foo','article',683503,0) AS shadowrecord (
id integer,
type_id integer ,
headline text,
strapline text,
[...]
);
Now I'd like to make things easier for my developers by supplying sort
of alias functions for each table like
CREATE or REPLACE FUNCTION foo.getshadow_article(
IN _id int4,
IN _versid int4)
RETURNS foo.article_shadow AS
$BODY$
SELECT *
FROM getshadowrecord('foo','article',$1,$2) AS
shadowrecord (
id integer,
type_id integer ,
headline text,
strapline text,
[...]
);
$BODY$
LANGUAGE SQL VOLATILE;
Using these alias functions, they can simply do a SELECT * FROM
foo.getshadow_article(id,version_id) without having to write the column
list.
As each of those alias functions would correspond exactly to one table,
I wonder if there is a more elegant alternative to explicitly declaring
the column list, something like this:
CREATE or REPLACE FUNCTION foo.getshadow_article(
IN _id int4,
IN _versid int4)
RETURNS foo.article_shadow AS
$BODY$
SELECT *
FROM getshadowrecord('foo','article',$1,$2) AS
shadowrecord (foo.article_shadow%rowtype);
$BODY$
LANGUAGE SQL VOLATILE;
Unfortunately my example doesn't work, but I think you'll know what I'd
like to do. The only way I see to solve this so far, would be to use
pl/pgsql or pl/perl, issue a query to the information_schema.columns
table, then assemble the query string with the column list and execute
that. I'd like to know if there's some better way to implement this,
something that would somehow use the %rowtype construct.
Kind regards
Markus
Jede Stimme zahlt, jetzt voten fur die besten Games: www.bamaward.de
Computec Media AG
Sitz der Gesellschaft und Registergericht: Furth (HRB 8818)
Vorstandsmitglieder: Albrecht Hengstenberg (Vorsitzender) und Rainer Rosenbusch
Vorsitzender des Aufsichtsrates: Jurg Marquard
Umsatzsteuer-Identifikationsnummer: DE 812 575 276
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PL/Perl: Is there a way to use spi_prepare/spi_exec_prepared with a list as second argument?
Hello!
I'd like to dynamically prepare and execute a certain statement in
PL/perl, i.e. I don't know at the time of calling my trigger function
how many arguments I need to pass for this queries and which types they
have. The command string is assembled dynamically, as is the list of
type-identifiers and arguments.
So what i'd like to do is something like this:
my $_sQueryString = 'INSERT INTO foo VALUES ($1,$2,$3)';
my @_lColumnTypes = =('int4','int4','text');
my $_pColumnInsert = spi_prepare($_sQueryString, @_lColumnTypes);
... and a similar thing for the actual spi_exec_prepared().
So far my experiments were not successful, but that may be because I am
everything but a Perl monk :) The docs don't say anything about the
actual type of the second arguments, so I hoped that passing lists would
be somehow possible, thereby allowing for more dynamic declarations.
If this doesn't work, which would be the best way to proceed? I cannot
think of anything other than eval as a last resort.
Kind regards
Markus
Computec Media AG
Sitz der Gesellschaft und Registergericht: Furth (HRB 8818)
Vorstandsmitglieder: Albrecht Hengstenberg (Vorsitzender) und Rainer Rosenbusch
Vorsitzender des Aufsichtsrates: Jurg Marquard
Umsatzsteuer-Identifikationsnummer: DE 812 575 276
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] diff-/patch-functionality for text-type data inside PostgreSQL
Hi!
I want to implement a versioning system for text columns of a table
inside a PostgreSQL 8.3 database. As some of the changes to a text field
might be very small compared to the total text size, I'd prefer storing
diffs instead of full previous versions of the text and use a patch-like
function whenever I want to actually roll back to a certain version. I
know that I could probably handle this quite easily in the application
code, but I'd prefer some solution running on the database itself, so
that the application wouldn't have to know anything about storing the
diffs, instead that process would be handled by a ON UPDATE trigger.
So far I have been playing around with PL/PerlU for diff/path
functionality, using CPAN modules Text::Diff and Text::Patch, but
haven't been too successful, as there seems to be some issue with this
mechanism if the text data doesn't contain newlines. Just as an
off-topic info, because it's some issue with the CPAN modules, not with
PostgreSQL:
#!/usr/bin/perl
use Text::Patch;
use Text::Diff;
$src = foo sdffasd;
$dst = 34asd sdf;
$diff = diff( \$src, \$dst, { STYLE = 'Unified' } );
print $diff . \n;
$out = patch( $src, $diff, { STYLE = 'Unified' } );
print Patch successful\n if $out eq $dst;
Running this results in the following output:
@@ -1 +1 @@
-foo sdffasd+34asd sdf
Hunk #1 failed at line 1.
Anyway, has anybody already done something in this direction,
preferrably in some way that is purely pl/* and wouldn't require any
custom-made C-library? So far I have only found this interesting
description of the implementation of the very same functionality here:
http://www.ciselant.de/projects/pg_ci_diff/doc.html - but there's no
source code supplied for the libpg_ci_diff.so library.
Kind regards
Markus
Computec Media AG
Sitz der Gesellschaft und Registergericht: Furth (HRB 8818)
Vorstandsmitglieder: Albrecht Hengstenberg (Vorsitzender) und Rainer Rosenbusch
Vorsitzender des Aufsichtsrates: Jurg Marquard
Umsatzsteuer-Identifikationsnummer: DE 812 575 276
--
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] diff-/patch-functionality for text-type data insidePostgreSQL
Hi! -Ursprüngliche Nachricht- Von: Martijn van Oosterhout [mailto:klep...@svana.org] Gesendet: Montag, 4. Mai 2009 15:30 I've used the Algorithm::Diff module in the past with success. It works on sequences of objects rather than just text but it works well. That means you can diff on word or character level at your choice, and even control what sequences you consider equal. That said, it doesn't have a patch function but that should be fairly easy to make. You'll need to define your own storage format for the diff though. http://search.cpan.org/~nedkonz/Algorithm-Diff-1.15/lib/Algori thm/Diff.pm Thank you - I have considered using Algorithm::Diff before, as Text::Diff seems to be based on that and one could, as you describe, create even more granular deltas than just line by line comparisons. The latter would however be fully sufficient for my needs, but I think I'll give Algorithm::Diff a closer look now :) Kind regards Markus Computec Media AG Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818) Vorstandsmitglieder: Albrecht Hengstenberg (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jürg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- 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] diff-/patch-functionality for text-type data inside PostgreSQL
Hi! -Ursprüngliche Nachricht- Von: Tom Lane [mailto:t...@sss.pgh.pa.us] Gesendet: Montag, 4. Mai 2009 15:04 Markus Wollny markus.wol...@computec.de writes: So far I have been playing around with PL/PerlU for diff/path functionality, using CPAN modules Text::Diff and Text::Patch, but haven't been too successful, as there seems to be some issue with this mechanism if the text data doesn't contain newlines. Almost all diff/patch functions operate line-by-line, so that hardly seems surprising. Not so much surprising, no, but I hadn't expected it to fail altogether on entries that just end after one line of text just because they lack a newline character - they are a one line text after all, so I assumed that the diff would produce a replace this old line with the new one type of instruction instead of producing something that patch doesn't seem to be able to process at all. Kind regards Markus Computec Media AG Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818) Vorstandsmitglieder: Albrecht Hengstenberg (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jürg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] invalid byte sequence on restore
Hi! I am currently struggling with a couple oif tainted bytes in one of our PostgreSQL 8.2 databases which I plan to move to 8.3 soon - so I need to dump restore. I think this problem bit me almost every single time during a major upgrade in the last couple of years, so I must say that I have become somewhat used to the procedure. Before now I have always used the plain text format dump, which I fed through iconv in order to correct encoding errors. This time I also had to convert from the 8.2 contrib-tsearch2 to 8.3 core-tsearch2, using the helpful instructions found at http://sql-info.de/postgresql/notes/converting-tsearch2-to-8.3.html Unfortunately this involves using the custom dump format and pg_restore. Using iconv on a custom dump is most probably not such a good idea :) On restoring, I received errors like the following: pg_restore: [archiver (db)] COPY failed: ERROR: invalid byte sequence for encoding UTF8: 0x80 pg_restore: [archiver (db)] COPY failed: ERROR: invalid byte sequence for encoding UTF8: 0xcd09 Fortunately I'm restoring to a test server, so the original DB is still available to me - and so I decided to correct the encoding errors before dumping. I just had to find out what to correct - and that's the tricky bit. pg_restore will error out on the first occurrence of an invalid byte sequence for a table and so the table remains empty on restore and what's more: Even when you would find this one spot per chance from the little information you've got now and you'd correct it before dumping again, you'd never know if a similar issue wouldn't bite you a few lines further down in the dump on the next attempt. So it's better to sieve through the complete contents of the affected tables before attempting another restore. Here's a possible path of actions to resolve the issue: 1. Take a look in the PostgreSQL logfile of the server you restore to in order to determine the tables where the error occurs. For each affected table, you'll find a couple of lines like the following: somedbERROR: invalid byte sequence for encoding UTF8: 0x80 somedbHINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by client_encoding. somedbCONTEXT: COPY topsearchterms, line 8998 somedbSTATEMENT: COPY topsearchterms (searchterm, usercount) FROM stdin; In this case, a table named topsearchterms is affected. Unfortunately you don't get to know which schema, but that doesn't matter right now. 2. Now get a list of all the objects in your custom dump: # pg_restore --disable-triggers -U postgres -Fc somedb-data.pg -lsomedb_objects.txt 3. In somedb_objects.txt, comment out everything but the lines for the tables where the errors occur; be sure to keep all tables with matching names and table column definitions in there, no matter which schema. 4. Restore those tables' contents into a file: # pg_restore --disable-triggers -U postgres -Fc somedb-data.pg -L somedb_objects.txtbroken_tables.txt 5. Now filter the tables' contents through iconv # cat broken_tables.txt | ./iconv-chunks - -c -f utf8 -t utf8 | fixed_tables.txt As my databases are quite big, I always use this helpful script here: http://maurice.aubrey.googlepages.com/iconv-chunks.txt - this is feeding the input in chunks to iconv, thus avoiding memory exhaustion. 6. Now you can simply use diff to find the affected tuples: # diff broken_tables.txt fixed_tables.txt 7. Even when diff output is not enough in itself, it will give you the line numbers, where the error occurs. So fire up your favorite editor and examine these lines in broken_tables.txt. 8. Update your affected tables in your original database. 9. Dump reload again - this time it'll hoepfully run smoothly :) I hope that this may help somebody facing the same problem. I'd also welcome any suggestions on how to improve on this procedure. Kind regards Markus Computec Media AG Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818) Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jürg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- 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] Suboptimal execution plan for simple query
Hi! Sam Mason wrote: You may have some luck with increasing the statistics target on the entry_id and last_updated columns and re-ANALYZING the table. Then again, the fact that it thinks it's only going to get a single row back when it searches for the entity_id suggests that it's all a bit confused! Thank you for that suggestion. Increasing the statistics target on entity_id from the default 10 to 30 and re-analyzing did the trick: Limit (cost=340.75..340.75 rows=1 width=12) (actual time=0.084..0.085 rows=1 loops=1) - Sort (cost=340.75..341.00 rows=103 width=12) (actual time=0.081..0.081 rows=1 loops=1) Sort Key: last_updated - Index Scan using idx_image_relation_entity_id on image_relation (cost=0.00..337.30 rows=103 width=12) (actual time=0.059..0.065 rows=3 loops=1) Index Cond: (entity_id = 69560) Total runtime: 0.121 ms Kind regards Markus Computec Media AG Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818) Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jürg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- 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] Suboptimal execution plan for simple query
Hi! In preparation for my upcoming upgrade to PostgreSQL 8.3.5, I have taken the opportunity to try this scenario on a test machine with the latest PostgreSQL version. Unfortunately the result remains the same, though this database has been just reloaded from a dump and vacuum analyzed. select version() outputs PostgreSQL 8.3.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21). Kind regards Markus Just for reference: Now I've got this simple query SELECT image_id FROM image_relation WHERE entity_id = 69560::integer ORDER BY last_updated DESC LIMIT1; which currently runs for something around 600ms. Here's the explain analyze output: Limit (cost=0.00..144.78 rows=1 width=12) (actual time=599.745..599.747 rows=1 loops=1) - Index Scan Backward using idx_image_relation_last_updated on image_relation (cost=0.00..39525.70 rows=273 width=12) (actual time=599.741..599.741 rows=1 loops=1) Filter: (entity_id = 69560) Total runtime: 599.825 ms SELECT image_id FROM image_relation WHERE entity_id = 69560 AND entity_id = entity_id ORDER BY last_updated DESC LIMIT1 Limit (cost=881.82..881.82 rows=1 width=12) (actual time=0.097..0.099 rows=1 loops=1) - Sort (cost=881.82..881.82 rows=1 width=12) (actual time=0.094..0.094 rows=1 loops=1) Sort Key: last_updated - Index Scan using idx_image_relation_entity_id on image_relation (cost=0.00..881.81 rows=1 width=12) (actual time=0.063..0.075 rows=3 loops=1) Index Cond: (entity_id = 69560) Filter: (entity_id = entity_id) Total runtime: 0.128 ms Computec Media AG Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818) Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jürg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Suboptimal execution plan for simple query
Hi! We've got a table with the following definition: CREATE TABLE image_relation ( id integer, article_id integer NOT NULL, entity_id integer NOT NULL, image_id integer NOT NULL, subline text, position integer, article_headline text, entity_name text, entity_type_id integer, entity_source text, default_pic character varying(3) NOT NULL, last_updated timestamp without time zone NOT NULL DEFAULT now(), CONSTRAINT pkey_image_relation PRIMARY KEY (article_id, entity_id, image_id) ) WITH (OIDS=FALSE); There are simple btree indexes on article_id, default_pic, entity_id, id, image_id, last_updated and position. The table has about 723,000 rows, stats say table size is 135MB, toast tables are 184MB and index size was at a whopping 727MB - so I thought I might do some additional maintenance. After reindexing, I got index size down to 131MB. This however did not affect the planner choices in any way, as they and the resulting execution times stayed the same before and after table maintenance (reindex and subsequent vacuum analyze). Our PostgreSQL version is 8.2.4 (I am going to move on to the latest and greatest 8.3 in about two weeks). Now I've got this simple query SELECT image_id FROM image_relation WHERE entity_id = 69560::integer ORDER BY last_updated DESC LIMIT1; which currently runs for something around 600ms. Here's the explain analyze output: Limit (cost=0.00..144.78 rows=1 width=12) (actual time=599.745..599.747 rows=1 loops=1) - Index Scan Backward using idx_image_relation_last_updated on image_relation (cost=0.00..39525.70 rows=273 width=12) (actual time=599.741..599.741 rows=1 loops=1) Filter: (entity_id = 69560) Total runtime: 599.825 ms SELECT image_id FROM image_relation WHERE entity_id = 69560::integer; only returns three rows. So I wonder why the planner chooses to use the index on last_updated instead of the index on entity_id; I found out that I can get it to reconsider and make a wiser choice by adding some seemingly superfluous statement to the WHERE clause (notice the AND... bit): SELECT image_id FROM image_relation WHERE entity_id = 69560 AND entity_id = entity_id ORDER BY last_updated DESC LIMIT1 Limit (cost=881.82..881.82 rows=1 width=12) (actual time=0.097..0.099 rows=1 loops=1) - Sort (cost=881.82..881.82 rows=1 width=12) (actual time=0.094..0.094 rows=1 loops=1) Sort Key: last_updated - Index Scan using idx_image_relation_entity_id on image_relation (cost=0.00..881.81 rows=1 width=12) (actual time=0.063..0.075 rows=3 loops=1) Index Cond: (entity_id = 69560) Filter: (entity_id = entity_id) Total runtime: 0.128 ms That's much more like it. The table is being vacuumed on a regular basis by both a nightly cron and the autovacuum daemon. Any ideas on what's going wrong here? Kind regards Markus Computec Media AG Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818) Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jürg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Efficient data structures and UI for product matrix
Hi! We wish to provide our users with a simple-to-use web-based processor-selection tool, where a user could select a couple of attribute values and be presented with a list of matching processors. The basis of the required data would be provided by our editors as Excel documents of the following structure: attribute_1 attribute_2 ... processor_a some_value some_value ... processor_b some_value some_value ... This data would be normalized to the following structure on import: CREATE TABLE processors ( id serial NOT NULL, processor_name text NOT NULL, CONSTRAINT processors_pkey PRIMARY KEY (id) )WITHOUT OIDS; CREATE TABLE attributes ( id serial NOT NULL, attribute_name text NOT NULL, CONSTRAINT attributes_pkey PRIMARY KEY (id) )WITHOUT OIDS; CREATE TABLE processor_attributes ( processor_id integer NOT NULL, attribute_id integer NOT NULL, value_id integer NOT NULL, CONSTRAINT pk_processor_attributes PRIMARY KEY (processor_id, attribute_id, value_id), CONSTRAINT fk_processor_id FOREIGN KEY (processor_id) REFERENCES processors(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_attribute_id FOREIGN KEY (attribute_id) REFERENCES attributes(id) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT fk_value_id FOREIGN KEY (value_id) REFERENCES attribute_values(id) )WITHOUT OIDS; CREATE TABLE attribute_values ( id serial NOT NULL, value text, attribute_id integer NOT NULL, CONSTRAINT attribute_values_pkey PRIMARY KEY (id), CONSTRAINT fk_attribute_id FOREIGN KEY (attribute_id) REFERENCES attributes(id) ON UPDATE CASCADE ON DELETE CASCADE )WITHOUT OIDS; The (web-based) UI should provide a dropdown field for each attribute (none selected per default) and a pageable table with the matching results underneath. The user should be kept from having to find out that there's no match for a selected combination of attribute-values, so after each selected dropdown, the as yet unselected dropdown-lists must be filtered to show only the still available attribute values - we intend to use some AJAX functions here. It'd be nice if the UI could be made fully dynamic, that's to say that it should reflect any changes to the number and names of attributes or their available values without any change to the application's code; the latter is in fact a must have, whereas the number and names of attributes would not change quite as frequently, so moderate changes to the code would be alright. Now, has anyone done anything similar recently and could provide some insight? I'd be particularly interested in any solutions involving some sort of de-normalization, views, procedures and suchlike to speed up performance of the drop-down-update process, especially as the number of attributes and the number of legal values for each attribute increases. Does anybody know of some sort of example application for this type of problem where we could find to inspiration? Kind regards Markus Computec Media AG Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818) Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jürg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- 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] Need to update all entries of one table based on an earlier backup
Hi! You're missing a table declaration for the table foo_old. You might try this: update foo set foo.foo_name2= (SELECT foo_old.foo_name2 FROM foo_old where foo.foo_id = foo_old.foo_id); Kind regards Markus Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Im Auftrag von Gregory Williamson Gesendet: Freitag, 20. Juni 2008 12:30 An: pgsql-general@postgresql.org Betreff: [GENERAL] Need to update all entries of one table based on an earlier backup For reasons best left unmentioned, I need to update entries in a table from a backup; I need to do all entries. For reasons eluding my sleep deprived eyes this fails in every variation I can think of: update foo set foo.foo_name2=foo_old.foo_name2 where foo.foo_id = foo_old.foo_id; ERROR: missing FROM-clause entry for table foo_old Could someone please hit me with a clue-stick ? This is crucial and yet I am stumbling over something. Since I am not updating foo_old I am baffled as to what this messsage really means. I've tried where foo_old.foo_id = foo.foo_id ... same message. TIA, Greg Williamson Senior DBA DigitalGlobe Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. (My corporate masters made me say this.) Computec Media AG Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818) Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jürg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276
[GENERAL] Slony-I configuration problem, probably due to custom casts in 8.3
Hi
I'm trying to get Slony-I up and running; master-db is PostgreSQL 8.3.1, slave
is PostgreSQL 8.2.4, Slony-I is 1.2.14 on both machines.
This is the DDL for the table I wish to replicate:
CREATE TABLE stats.article_impressions_day
(
site_id integer NOT NULL,
article_id integer NOT NULL,
date_day date NOT NULL,
impressions_p1 integer,
impressions_total integer NOT NULL,
impressions_pages integer,
CONSTRAINT pk_article_impressions_day PRIMARY KEY (site_id, article_id,
date_day)
)WITHOUT OIDS;
-- Indexes
CREATE INDEX idx_article_impressions_day_total ON stats.article_impressions_day
USING btree (impressions_total);
CREATE INDEX idx_article_impressions_day_site ON stats.article_impressions_day
USING btree (site_id);
CREATE INDEX idx_article_impressions_day_p1 ON stats.article_impressions_day
USING btree (impressions_p1);
CREATE INDEX idx_article_impressions_day_date_day ON
stats.article_impressions_day USING btree (date_day);
CREATE INDEX idx_article_impressions_day_aid ON stats.article_impressions_day
USING btree (article_id);
This is my slonik-script:
#!/bin/sh
CLUSTER=stats
DBNAME1=community
DBNAME2=cbox
HOST1=ciadb2
HOST2=ciadb1
SLONY_USER=postgres
PGBENCH_USER=postgres
/opt/pgsql/bin/slonik _EOF_
cluster name = $CLUSTER;
node 1 admin conninfo = 'dbname=$DBNAME1 host=$HOST1 user=$SLONY_USER';
node 2 admin conninfo = 'dbname=$DBNAME2 host=$HOST2 user=$SLONY_USER';
init cluster ( id = 1, comment = 'Node 1' );
create set ( id = 1, origin = 1, comment = 'All stats tables' );
set add table ( set id = 1, origin = 1,
id = 1, full qualified name = 'stats.article_impressions_day',
comment = 'daily article stats' );
set add table ( set id = 1, origin = 1,
id = 2, full qualified name = 'stats.entity_impressions_day',
comment = 'daily entity stats' );
store node ( id = 2, comment = 'Node 2' );
store path ( server = 1, client = 2,
conninfo = 'dbname=$DBNAME1 host=$HOST1 user=$SLONY_USER');
store path ( server = 2, client = 1,
conninfo = 'dbname=$DBNAME2 host=$HOST2 user=$SLONY_USER');
store listen ( origin = 1, provider = 1, receiver = 2 );
store listen ( origin = 2, provider = 2, receiver = 1 );
When I run this script, I get some error messages:
stdin:41: PGRES_FATAL_ERROR select _stats.setAddTable(1, 1,
'stats.article_impressions_day', 'pk_article_impressions_day', 'daily article
stats'); - ERROR: operator is not unique: unknown || integer
LINE 1: SELECT 'create trigger _stats_logtrigger_' || $1 || ' a...
^
HINT: Could not choose a best candidate operator. You might need to add
explicit type casts.
QUERY: SELECT 'create trigger _stats_logtrigger_' || $1 || ' after insert
or update or delete on ' || $2 || ' for each row execute procedure
_stats.logTrigger (''_stats'', ''' || $1 ||
''', ''' || $3 || ''');'
CONTEXT: PL/pgSQL function altertableforreplication line 62 at EXECUTE
statement
SQL statement SELECT _stats.alterTableForReplication( $1 )
PL/pgSQL function setaddtable_int line 109 at PERFORM
SQL statement SELECT _stats.setAddTable_int( $1 , $2 , $3 , $4 , $5 )
PL/pgSQL function setaddtable line 37 at PERFORM
I strongly suspect that there is some conflict with the implicit casts I added
in the master-db - I used
http://people.planetpostgresql.org/peter/uploads/pg83-implicit-casts.sqlx in
order to restore pre-8.3 cast behaviour. As our application still depends on
this behaviour I cannot simply drop the casts. Now what could I do to get
replication with these casts in place on the master-db?
Kind regards
Markus
Computec Media AG
Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818)
Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch
Vorsitzender des Aufsichtsrates: Jürg Marquard
Umsatzsteuer-Identifikationsnummer: DE 812 575 276
--
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] Slony-I configuration problem, probably due to custom casts in 8.3
Hi, Sorry for the bother - found this: http://archives.postgresql.org/pgsql-general/2008-03/msg01159.php That seems to solve the problem. Thank you! Kind regards Markus Computec Media AG Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818) Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jürg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- 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] tsvector_update_trigger throws error column is not of tsvector type
Hi! Tom Lane wrote: I wrote: Would you confirm that select atttypid from pg_attribute where attrelid = 'public.ct_com_board_message'::regclass and attname = 'idxfti'; gives 3614 (the preassigned OID for pg_catalog.tsvector)? Actually, I'll bet a nickel that you'll find it doesn't, but rather returns the OID of the domain over tsvector that the new contrib/tsearch2 module creates. It's clearly a bug that the built-in trigger doesn't allow the domain alias to be used --- will fix. regards, tom lane That nickel would be yours to keep :) community=# select atttypid from pg_attribute where attrelid = 'public.ct_com_board_message'::regclass and attname = 'idxfti'; atttypid -- 33991259 (1 row) Concerning the table definition - sorry, I edited out a couple of fields too many, which I assumed were not relevant to this case. Here is the full table definition: CREATE TABLE public.ct_com_board_message ( board_id integer DEFAULT 0, thread_id integer DEFAULT 0, father_id integer DEFAULT 0, message_id integer NOT NULL DEFAULT 0, user_id integer DEFAULT 0, title text, signature text, follow_up text, count_reply integer DEFAULT 0, last_reply timestamptz, created timestamptz DEFAULT now(), article_id integer DEFAULT 0, logged_ip text, state_id smallint DEFAULT 0, user_login text, user_status smallint DEFAULT 5, user_rights text, text text, deleted_user_id integer DEFAULT -1, user_rank text, user_rank_description text, user_rank_picture text, deleted_date timestamptz, deleted_login text, user_created timestamptz, poll_id integer DEFAULT 0, last_updated timestamptz DEFAULT now(), idxfti tsvector, CONSTRAINT pk_ct_com_board_message PRIMARY KEY (message_id) ); The trigger definition: CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON public.ct_com_board_message FOR EACH ROW EXECUTE PROCEDURE pg_catalog.tsvector_update_trigger(idxfti,'pg_catalog.german',title,text,user_login); And the error message from the log: 2008-04-09 13:42:48 CEST - 8820: 192.168.222.132(52319)@communityERROR: column idxfti is not of tsvector type 2008-04-09 13:42:48 CEST - 8820: 192.168.222.132(52319)@communitySTATEMENT: insert into PUBLIC.CT_COM_BOARD_MESSAGE ( BOARD_ID , THREAD_ID , FATHER_ID , MESSAGE_ID , USER_ID , TITLE , TEXT , SIGNATURE , LOGGED_IP , USER_LOGIN , USER_STATUS , USER_RIGHTS , USER_CREATED , LAST_REPLY ) values ( 1 , 6579073 , 0 , 6579073 , 39 , 'Test TSearch2 tsvector_update_trigger' , 'tsvector_update_trigger test test test' , '' , '123.123.123.123' , 'Markus_Wollny' , 100 , 'yp' , '2001-03-22 16:54:53.0' , CURRENT_TIMESTAMP ) Now I have a custom trigger function: CREATE or REPLACE FUNCTION public.board_message_trigger() RETURNS pg_catalog.trigger AS $BODY$ begin new.idxfti := to_tsvector(coalesce(new.title,'')) || to_tsvector(coalesce(new.text,'')) || to_tsvector(coalesce(new.user_login,'')); return new; end $BODY$ LANGUAGE 'plpgsql' VOLATILE; And this trigger: CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON ct_com_board_message FOR EACH ROW EXECUTE PROCEDURE board_message_trigger(); Everything works fine. It's sort of less elegant though than having just the one generic trigger function and configuring the needed fields in the trigger itself. Kind regards Markus Computec Media AG Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818) Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jürg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- 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] tsvector_update_trigger throws error column is not of tsvector type
Tom Lane wrote: It should work if you explicitly change the column's type to pg_catalog.tsvector. (There's a fix in place for 8.3.2, also.) Which would probably be not such a good idea to try on a 7GB table in production, I think. Or is there some way hacking the system catalog to correct the type instead of an ALTER TABLE ... ALTER COLUMN TYPE? Kind regards Markus Computec Media AG Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818) Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jürg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- 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] tsvector_update_trigger throws error column is not of tsvector type
Hi! Tom Lane wrote: Well, you could probably get away with an update pg_attribute set atttypid ... but it might be safer to just wait for 8.3.2. I like it safe :) Thanks for the advice! Kind regards Markus Computec Media AG Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818) Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jürg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] tsvector_update_trigger throws error column is not of tsvector type
Hi! I am in the process of migrating a PostgreSQL 8.2.4 database to 8.3. So far, everything has worked fine, even tsearch2-searching an indexed table. There's something severely wrong with the trigger-function I use to keep the tsvector-column updated. Here's my table definition: CREATE TABLE public.ct_com_board_message ( board_id integer DEFAULT 0, thread_id integer DEFAULT 0, father_id integer DEFAULT 0, message_id integer NOT NULL DEFAULT 0, user_id integer DEFAULT 0, title text, signature text, follow_up text, count_reply integer DEFAULT 0, last_reply timestamptz, created timestamptz DEFAULT now(), article_id integer DEFAULT 0, logged_ip text, state_id smallint DEFAULT 0, text text, deleted_date timestamptz, deleted_login text, poll_id integer DEFAULT 0, last_updated timestamptz DEFAULT now(), idxfti tsvector, CONSTRAINT pk_ct_com_board_message PRIMARY KEY (message_id) ); And there's this trigger definition: CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON public.ct_com_board_message FOR EACH ROW EXECUTE PROCEDURE pg_catalog.tsvector_update_trigger(idxfti,pg_catalog.german,title,text,user_login); Now when I do anything that fires the trigger like UPDATE ct_com_board_message set count_reply = 1 where message_id = 12345; I get an error ERROR: column idxfti is not of tsvector type I didn't touch the tsvector_update_trigger-function at all, it still reads CREATE or REPLACE FUNCTION pg_catalog.tsvector_update_trigger() RETURNS pg_catalog.trigger AS $BODY$ tsvector_update_trigger_byid $BODY$ LANGUAGE 'internal' VOLATILE; So what's happening here? Kind regards Markus Computec Media AG Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818) Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jürg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- 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] tsvector_update_trigger throws error column is not of tsvector type
Hi!
Tom Lane wrote:
This is leaping to conclusions, but what I suspect is that you've got
two types tsvector in your database and the column is the wrong
one.
This situation is not too hard to get into if you try to restore a
dump from an old database that used contrib/tsearch2 --- the dump may
create a partially broken type public.tsvector while the built-in
pg_catalog.tsvector still remains.
It's a fair suspicion, but I have been in the fortunate situation to have
tsearch2 installed to it's own schema in 8.2.4; so I dumped the old db without
the tsearch2-schema like this, using 8.3.1's pg_dump on the new machine:
pg_dump -h123.123.123.123 -U postgres -N tsearch2 -s community
community.schema.sql
pg_dump -h123.123.123.123 -U postgres -N tsearch2 -a community -Fc
community.data.pg
Then I edited community.schema.sql, doing these two sed's:
sed -e 's/tsearch2\.tsvector/tsvector/g' community.schema.sq | \
sed -e 's/idxfti tsearch2\.gist_tsvector_ops/idxfti/g' -
community.schema.sql.83.tmp
Afterwards I replaced all the old trigger-declarations for the update-trigger
with the new style, using tsvector_update_trigger.
Then I created a new 8.3-DB, imported the tsearch2-compatibility-script like
this:
psql -U postgres community /opt/pgsql/share/contrib/tsearch2.sql
And only then did I import the edited schema.
Afterwards I restored the dump like this:
pg_restore --disable-triggers -U postgres -v -Fc -d community community.data.pg
There haven't been any errors during the import, everything went fine. The
restored database doesn't have a tsearch2-schema any more. I scanned through
the edited schema-definiton which I imported and theres's no CREATE TYPE in
there at all. I checked the public schema and there's no tsvector there either.
So it must be the builtin-tsvector type alright - it seems to be there and work
perfectly:
community=# select 'foo'::tsvector;
tsvector
--
'foo'
(1 row)
community=# select to_tsvector('foo');
to_tsvector
-
'foo':1
(1 row)
community=# SELECT message_id FROM ct_com_board_message WHERE idxfti @@
to_tsquery('Starcraft') LIMIT 3;
message_id
5669043
5671762
5670197
(3 rows)
I can even update that idxfti-column manually like so:
community=# UPDATE ct_com_board_message
SET idxfti = to_tsvector(coalesce(title,'')) ||
to_tsvector(coalesce(text,'')) ||
to_tsvector(coalesce(user_login,'')) WHERE
message_id = 6575830;
UPDATE 1
And when I use a custom-trigger-function, there's no problem either:
CREATE FUNCTION board_message_trigger() RETURNS trigger AS $$
begin
new.idxfti :=
to_tsvector(coalesce(new.title,'')) ||
to_tsvector(coalesce(new.text,'')) ||
to_tsvector(coalesce(new.user_login,''));
return new;
end
$$ LANGUAGE plpgsql;
CREATE TRIGGER tsvectorupdate
BEFORE
INSERT OR UPDATE
ON public.ct_com_board_message
FOR EACH ROW
EXECUTE PROCEDURE board_message_trigger();
community=# UPDATE ct_com_board_message set count_reply = count_reply where
message_id = 6575830;
UPDATE 1
community=# SELECT message_id FROM ct_com_board_message WHERE idxfti @@
to_tsquery('markus') AND message_id = 6575830 LIMIT 3;
message_id
6575830
(1 row)
So everything's working as expected apart from that built-in trigger function.
There's some hints in the manual about safe migration from tsearch2
to built-in tsearch:
http://www.postgresql.org/docs/8.3/static/textsearch-migration.html
I read that carefully before I went on that journey (that's why I did load that
new contrib/tsearch2 module), but I didn't find anything helpful regarding this
situation.
This is very puzzling. I'll resort to writing custom trigger-functions for the
time being.
Kind regards
Markus
Computec Media AG
Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818)
Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch
Vorsitzender des Aufsichtsrates: Jürg Marquard
Umsatzsteuer-Identifikationsnummer: DE 812 575 276
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Duplicate Key violation on dumpreload using pg_restore
Hello! I'm currently trying to migrate one of our databases from PostgreSQL 8.2.4 to PostgreSQL 8.3.1. I have worked around the Tsearch2 migration (we used the contrib module) without too much hassle, but find myself stuck at an unexpected point - I get a duplicate key violation for the primary key on one of my tables: pg_restore -U postgres -d community -a --disable-triggers -t ct_com_user -v ct_com_user.backup pg_restore: connecting to database for restore pg_restore: disabling triggers for ct_com_user pg_restore: restoring data for table ct_com_user pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 4256; 0 106035908 TABLE DATA ct_com_user postgres pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key value violates unique constraint pk_ct_com_user CONTEXT: COPY ct_com_user, line 357214: 2463013 X 5 \N \N 0 \N 0 \N 0 \N 0 \N 1 \N 1 \N 1 \N 0 \N 0 \N 0 \N 0 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] 0 ... pg_restore: *** aborted because of error This is the table definition (I left out the non-relevant bits): CREATE TABLE ct_com_user ( user_id integer NOT NULL, login text, password text, [...] CONSTRAINT pk_ct_com_user PRIMARY KEY (user_id) ) WITH (OIDS=TRUE); I did not change the table definition after the dump. I used pgdump of 8.3.1 to create a dump of schema and data separately like this: /opt/pgsql/bin/pg_dump -h-U postgres -N tsearch2 -s community community.schema.sql /opt/pgsql/bin/pg_dump -h -U postgres -N tsearch2 -a community -Fc community.data.pg Then I created a new database (same encoding UTF-8, no issues there) on my 8.3.1 machine and installed the 8.3.1-contrib-tsearch2-module for backwards compatibility. After that I fed the schema.sql into that new DB - no errors so far. Then I tried to restore the data using /opt/pgsql/bin/pg_restore --disable-triggers -v -U postgres -v -Fc -d community community.data.pg During restore of that complete data dump, I get a warning like the one above: pg_restore: restoring data for table ct_com_user pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 9116; 0 106035908 TABLE DATA ct_com_user postgres pg_restore: [archiver (db)] COPY failed: ERROR: duplicate key value violates unique constraint pk_ct_com_user CONTEXT: COPY ct_com_user, line 356811: 2463013 X 5 \N \N 0 \N 0 \N 0 \N 0 \N 1 \N 1 \N 1 \N 0 \N 0 \N 0 \N 0 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] 0 ... pg_restore: enabling triggers for ct_com_user [...] WARNING: errors ignored on restore: 1 Checking the restored database, everything is where it should be (i.e. even the TSearch2-enabled tabled), with the exception of that ct_com_user-table, which remains empty. I therefore tried and dumped that table alone again and tried to restore - with the exact same result (see above). Before restoring again, I made sure that the target table doesn't contain any entries (count(*) still is 0). I'll try and delete that single line in the 8.2.1 production system now (this user has not logged in for nearly three months now, so not much loss there - but even if that happens to work out (not so sure if it will), I'd still like to know what's going on here. Any ideas? Kind regards Markus Computec Media AG Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818) Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jürg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276
Re: [GENERAL] Duplicate Key violation on dumpreload using pg_restore
Quick update: Seems like removing that tuple has solved the issue, dump and import of that table went fine, everything is where is should be - but there shouldn't have been an issue there in the first place however, with the primary key constraint present in the source database. I'm still curious, even though I've now got less to worry about the upcoming migration :) Computec Media AG Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818) Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jürg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276
Re: [GENERAL] Duplicate Key violation on dumpreload using pg_restore
Tom Lane wrote: Maybe there actually is a duplicate key in the source DB --- have you checked? There were some bugs in early 8.2.x releases that could possibly allow that to happen. Thanks, I was hoping there would be an easy explanation like that. I guess I'll have to do a little reading up on the change logs of post-8.2.4-releases :) Computec Media AG Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818) Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jürg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276
[GENERAL] Logging of autovacuum activity
Hi, I am a bit stuck finding out how to log autovacuum activity in PostgreSQL 8.2. In the olden times I used to pipe the pg_autovacuum daemon's output to a file. Now pg_autovacuum has become part of the core, I wonder if there's some other possibility of monitoring its activity? I've got the following logging-settings set: client_min_messages = error log_min_messages = notice log_error_verbosity = default log_min_error_statement = notice log_min_duration_statement = -1 log_line_prefix = '%t - %p: [EMAIL PROTECTED]' Everything else is commented out, i.e. set to defaults. There is however nothing to be found in the logs concerning autovacuum or any standard vacuum at all. I couldn't find anything in the docs regarding the specific logging of vacuum runs. For my nightly vacuum maintenance job, I simply pipe sterr of vacuum verbose analyze to a separate logfile. Concerning autovacuum, I'd like to have some info on when it runs and which tables it has processed. It is running for sure, I can often see the autovacuum process pop up in top. I only see no means of finding out what it actually does when it's running. Kind regards Markus Computec Media AG Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818) Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Rainer Rosenbusch Vorsitzender des Aufsichtsrates: Jürg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- 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] Transaction wraparound problem with database postgres
Hi! Just some more info, hoping that it helps with a diagnosis: 1: datname (typeid = 19, len = 64, typmod = -1, byval = f) 2: age (typeid = 23, len = 4, typmod = -1, byval = t) 3: datfrozenxid(typeid = 28, len = 4, typmod = -1, byval = t) 1: datname = postgres(typeid = 19, len = 64, typmod = -1, byval = f) 2: age = -2147321465 (typeid = 23, len = 4, typmod = -1, byval = t) 3: datfrozenxid = 1835116837 (typeid = 28, len = 4, typmod = -1, byval = t) Then I issue a vacuum: 1: datname (typeid = 19, len = 64, typmod = -1, byval = f) 2: age (typeid = 23, len = 4, typmod = -1, byval = t) 3: datfrozenxid(typeid = 28, len = 4, typmod = -1, byval = t) 1: datname = postgres(typeid = 19, len = 64, typmod = -1, byval = f) 2: age = -2147321383 (typeid = 23, len = 4, typmod = -1, byval = t) 3: datfrozenxid = 1835116837 (typeid = 28, len = 4, typmod = -1, byval = t) It worries me, that 'age' is negative. Kind regards Markus Von: Markus Wollny Gesendet: Fr 21.03.2008 21:50 An: pgsql-general@postgresql.org Betreff: Transaction wraparound problem with database postgres Hi! My database cluster has just stopped working. I get the following message: psql: FATAL: Datenbank nimmt keine Befehle an, um Datenverlust in Datenbank »postgres« wegen Transaktionsnummernüberlauf zu vermeiden TIP: Halten Sie den Postmaster an und verwenden Sie ein Standalone-Backend, um VACUUM in der Datenbank »postgres« auszuführen. I did as suggested, stopped tzhe postmaster and started a single backend on database 'postgres'; I issued a VACCUM there. su postgres -c /opt/pgsql/bin/postgres --single -D /var/lib/pgsql/data postgres backend VACUUM However, this doesn't seem to help - I receive lots and lots of messages like this: 2008-03-21 21:43:27 CET - 11845: @WARNUNG: Datenbank »postgres« muss innerhalb von 4294805194 Transaktionen gevacuumt werden 2008-03-21 21:43:27 CET - 11845: @TIPP: Um ein Abschalten der Datenbank zu vermeiden, führen Sie in »postgres« ein VACUUM über die komplette Datenbank aus. i.e. database 'postgres' must be vacuumed within 4294805194 transactions. That's what I just did, but the problem persists. Whenever I issue a 'vacuum', the number of transactions simply decreases. This is PostgreSQL 8.2.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21) I am absolutely lost about what to do now - and it's a puzzle how this could have happened in the first place. I have configured autovaccum AND I run a vacuum verbose analyze over all databases every single night. What do I do now? Is there some alternative to reinit and going back to the last dump? Urgent help would be very much appreciated. Kind regards Markus Computec Media AG Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818) Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Niels Herrmann Vorsitzender des Aufsichtsrates: Jürg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276
[GENERAL] Transaction wraparound problem with database postgres
Hi! My database cluster has just stopped working. I get the following message: psql: FATAL: Datenbank nimmt keine Befehle an, um Datenverlust in Datenbank »postgres« wegen Transaktionsnummernüberlauf zu vermeiden TIP: Halten Sie den Postmaster an und verwenden Sie ein Standalone-Backend, um VACUUM in der Datenbank »postgres« auszuführen. I did as suggested, stopped tzhe postmaster and started a single backend on database 'postgres'; I issued a VACCUM there. su postgres -c /opt/pgsql/bin/postgres --single -D /var/lib/pgsql/data postgres backend VACUUM However, this doesn't seem to help - I receive lots and lots of messages like this: 2008-03-21 21:43:27 CET - 11845: @WARNUNG: Datenbank »postgres« muss innerhalb von 4294805194 Transaktionen gevacuumt werden 2008-03-21 21:43:27 CET - 11845: @TIPP: Um ein Abschalten der Datenbank zu vermeiden, führen Sie in »postgres« ein VACUUM über die komplette Datenbank aus. i.e. database 'postgres' must be vacuumed within 4294805194 transactions. That's what I just did, but the problem persists. Whenever I issue a 'vacuum', the number of transactions simply decreases. This is PostgreSQL 8.2.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21) I am absolutely lost about what to do now - and it's a puzzle how this could have happened in the first place. I have configured autovaccum AND I run a vacuum verbose analyze over all databases every single night. What do I do now? Is there some alternative to reinit and going back to the last dump? Urgent help would be very much appreciated. Kind regards Markus Computec Media AG Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818) Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Niels Herrmann Vorsitzender des Aufsichtsrates: Jürg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276
Re: [GENERAL] Transaction wraparound problem with database postgres
Hi! Sorry for the quick updates to my own messages, but I didn't want to lean back and wait - so I took to more aggressive measures. All my other databases in this cluster are fine - and the 'postgres' database doesn't seem to do anything really useful except being the default database. I dropped it and recreated it with template1 as template, afterwards I could start up my cluster with no problems whatsoever. I'd still like to find out what exactly happened here so I can prevent the same from happening again in the future. The age(datfrozenxid) is positive again: # SELECT datname, age(datfrozenxid), datfrozenxid FROM pg_database where datname='postgres'; datname |age| datfrozenxid --+---+-- postgres | 100291695 | 3882762765 (1 Zeile) As I mentioned earlier, I'm running autovaccuum and use a nightly cron to run vacuum verbose analyze over all my databases. So lack of vacuum cannot be the issue, I think. But what else could have happened here? I regularly scan my logs, and there was no early warning for this issue. The first event of this type in the server log was from today: 2008-03-21 17:08:48 CET - 32161: xxx.xxx.xxx.xxx(52833)@magazineWARNUNG: Datenbank »postgres« muss innerhalb von 1100 Transaktionen gevacuumt werden 2008-03-21 17:08:48 CET - 32161: xxx.xxx.xxx.xxx(52833)@magazineTIPP: Um ein Abschalten der Datenbank zu vermeiden, führen Sie in »postgres« ein VACUUM über die komplette Datenbank aus. (i.e. database 'postgres' need to be vacuumed within 1100 transactions...) A mere three hours later, the server already refused any further requests: 2008-03-21 20:05:21 CET - 25184: xxx.xxx.xxx.xxx(60837)@magazineFEHLER: Datenbank nimmt keine Befehle an, um Datenverlust in Datenbank »postgres« wegen Transaktionsnummernüberlauf zu vermeiden (ie. database no longer accepts any commands in order to prevent data loss in database 'postgres' because of transaction id wraparound) Now that the adrenaline level has dropped to normal, I'd still like to know what exactly has happened here; The cluster has been initdb'ed on 2007-04-27. Kind regards Markus Computec Media AG Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818) Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Niels Herrmann Vorsitzender des Aufsichtsrates: Jürg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276
Re: [GENERAL] Transaction wraparound problem with database postgres
Hi! Thanks for all the quick replies :) Tom Lane wrote: Markus Wollny [EMAIL PROTECTED] writes: Just some more info, hoping that it helps with a diagnosis: 1: datname (typeid = 19, len = 64, typmod = -1, byval = f) 2: age (typeid = 23, len = 4, typmod = -1, byval = t) 3: datfrozenxid(typeid = 28, len = 4, typmod = -1, byval = t) 1: datname = postgres (typeid = 19, len = 64, typmod = -1, byval = f) 2: age = -2147321465 (typeid = 23, len = 4, typmod = -1, byval = t) 3: datfrozenxid = 1835116837 (typeid = 28, len = 4, typmod = -1, byval = t) What are the datfrozenxid's of the other rows in pg_database? Do the other fields of postgres' row look sane? Yes, there were no issues on any of the databases that are actually in use: # select datname, age(datfrozenxid), datfrozenxid from pg_database; datname |age| datfrozenxid +---+-- rpfcms | 104213725 | 3881601233 rpfflash | 147289015 | 3838525943 postgres | 103052193 | 3882762765 template1 | 104213787 | 3881601171 template0 | 3052193 | 3982762765 ezpublish | 147419044 | 3838395914 community | 147566532 | 3838248426 abo| 147689637 | 3838125321 bluebox| 147679271 | 3838135687 cbox | 147582662 | 3838232296 mpo| 147309716 | 3838505242 newsletter | 147309110 | 3838505848 pcaction | 147297707 | 3838517251 pcgames| 147291588 | 3838523370 magazine | 147419044 | 3838395914 Only the 'postgres' db was affected - which is puzzling because we don't actually use this database actively for anything. Kind regards Markus Computec Media AG Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818) Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Niels Herrmann Vorsitzender des Aufsichtsrates: Jürg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- 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] Transaction wraparound problem with database postgres
Andreas 'ads' Scherbaum wrote: Hello, First of all, it would help you and most of the readers on this list, if you have the error messages in english. There is a german mailinglist too, if you want to ask in german. Sorry, I tried to describe the issue as best as I could and included the actual log entries only for completeness, but was in too much of a hurry to find the correct translations. vacuum all databases, add the VERBOSE option to see, what actually happens. Alas, too late, I got rid of the offending 'postgres' database already by dropping and recreating. Are you using the database 'postgres' at all? No, not at all. Didn't touch it ever after initdb. And are you sure, that you include all databases? Yes. I run the following every night: su postgres -c '/opt/pgsql/bin/psql -t -c select datname from pg_database order by datname; template1 | xargs -n 1 /opt/pgsql/bin/psql -q -c vacuum verbose analyze;' Any error messages in the vacuum output? None. Oh, and by the way: why do you have autovacuum and a manual vacuum run every night plus the vacuum run with verbose? Paranoia, mostly, I think. I'm using PostgreSQL since long before autovacuum was introduced and always thought that it couldn't do any harm to keep my original vacuum job running once every night, even though autovacuum does a remarkable job, especially for a couple of busy tables where the nightly vacuum was not quite enough. Plus, having the verbose output from the log, I get useful info for setting the 'max_fsm_pages'/'max_fsm_relations'-options to sensible values. Is it a problem to have cron'ed VACUUM-runs in parallel with autovacuum? Urgent help would be very much appreciated. That's a bit late here ;-) Ah, well obviously it wasn't - it's always an extremely pleasant surprise when one is actually in dire need of help and gets an almost immediate and helpful response. I wish you all happy Easter! Kind regards Markus Computec Media AG Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818) Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Niels Herrmann Vorsitzender des Aufsichtsrates: Jürg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- 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] Transaction wraparound problem with database postgres
Tom Lane wrote: Markus Wollny [EMAIL PROTECTED] writes: I'd still like to find out what exactly happened here so I can prevent the same from happening again in the future. Me too. It would seem that something did a vacuum of postgres with a strange choice of xid cutoff, but I can't think of what would cause that. Do you ever do VACUUM FREEZE on your databases? No, I actually never heard of VACUUM FREEZE, I have to admit. Computec Media AG Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818) Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Niels Herrmann Vorsitzender des Aufsichtsrates: Jürg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Questions about TSearch2 and PG 8.2
Hello!
I'm in the process of upgrading our PostgreSQL 8.1 databases to PostgreSQL
8.2.4. I have stumbled over a minor issue with the upgrade and some helpful
suggestions here:
http://people.planetpostgresql.org/xzilla/index.php?/archives/291-The-pain-that-is-tsearch2-8.1-8.2-upgrading.html
I shall try tonight with an plain SQL dump, but as some of my DBs are quite
large, I usually use the custom dump format. As I would like to move the
tsearch2-stuff in ist own schema as suggested, I tried using a restore list.
I'd like to report that everything works as expected, but I've got a slight
problem with the custom schema part. I created the target-db, created a schema
tsearch2 and installed the tsearch2-functions, operators, configuration and
whatnot into this new schema. Then I edited the restore list so that the
tsearch2-bits would not be created from the dump file again. However, the
binary-dump tries to create the textindex-columns with a tsvector-type which
explicitly references the public schema.
Instead of
CREATE TABLE someschema.article
(
id integer,
mytext text,
idxfti tsvector
);
it tries to create the table like this
CREATE TABLE someschema.article
(
id integer,
mytext text,
idxfti public.tsvector
);
As the tsvector-type is defined in the tsearch2-schema, this is bound to fail,
even with the search_path set to include the tsearch2-schema. I assume that
this happens because the table article is not in the same schema as the
original tsvector-type and the default search_path is being ignored on the dump
in order to be on the safe side. This double-checking breaks the migration in
my case, however, so is there some way that would allow me to change the table
definition on restore from using just tsvector instead of the explicit
public.tsvector? I already tried editing the binary dump, but that just
resulted in a corrupted dump-file. I there's no other way, I'll go the plain
dump route, of course, but I'd just like to check this issue.
My second question concerns the new Gin (Generalized Inverted Index) index
type. Is it stable enough for production yet and would it yield a high enough
performance gain in comparison the GiST? Does it make much sense using a
Gin-index alongside the GiST-one? Would we need to change anything in the
application code in order to make use of Gin - like using
where idxfti @ to_tsquery('default_german', 'Fundstück')
instead of
where idxfti @@ to_tsquery('default_german', 'Fundstück')
? The docs here http://www.sai.msu.su/~megera/wiki/Gin are still a bit thin, so
any hint to some further examples would be greatly appreciated.
Kind regards
Markus
Computec Media AG
Sitz der Gesellschaft und Registergericht: Fürth (HRB 8818)
Vorstandsmitglieder: Johannes S. Gözalan (Vorsitzender) und Niels Herrmann
Vorsitzender des Aufsichtsrates: Jürg Marquard
Umsatzsteuer-Identifikationsnummer: DE 812 575 276
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
[GENERAL] 8.2.3 initdb fails - invalid value for parameter timezone_abbreviations: Default
Hello! I've just tried to initdb a fresh install of PostgreSQL 8.3.2 on Debian Etch (testing). My configure settings were ./configure --prefix=/opt/pgsql --datadir=/var/lib/pgsql/data/base --enable-locale --with-perl --enable-odbc --with-java This is what happens: [EMAIL PROTECTED]:~$ /opt/pgsql/bin/initdb -D -locale=de_DE.UTF-8 /var/lib/pgsql/data/ initdb: file /var/lib/pgsql/data/base/postgres.bki does not exist This means you have a corrupted installation or identified the wrong directory with the invocation option -L. So I use the -L switch and try again: [EMAIL PROTECTED]:~$ /opt/pgsql/bin/initdb -D -locale=de_DE.UTF-8 /var/lib/pgsql/data/base -L /var/lib/pgsql/data/input/ The files belonging to this database system will be owned by user postgres. This user must also own the server process. The database cluster will be initialized with locale de_DE.UTF-8. The default database encoding has accordingly been set to UTF8. fixing permissions on existing directory /var/lib/pgsql/data/base ... ok creating subdirectories ... ok selecting default max_connections ... 10 selecting default shared_buffers/max_fsm_pages ... 400kB/2 creating configuration files ... ok creating template1 database in /var/lib/pgsql/data/base/base/1 ... FATAL: invalid value for parameter timezone_abbreviations: Default child process exited with exit code 1 initdb: removing contents of data directory /var/lib/pgsql/data/base [EMAIL PROTECTED]:~$ ls -l /var/lib/pgsql/data/input/ insgesamt 624 drwxr-xr-x 2 postgres daemon 4096 2007-02-11 03:22 contrib -rw-r--r-- 1 postgres daemon 41682 2007-02-11 03:19 conversion_create.sql -rw-r--r-- 1 postgres daemon 82686 2007-02-11 03:19 information_schema.sql -rw-r--r-- 1 postgres daemon 3257 2007-02-11 03:19 pg_hba.conf.sample -rw-r--r-- 1 postgres daemon 1460 2007-02-11 03:19 pg_ident.conf.sample -rw-r--r-- 1 postgres daemon542 2007-02-11 03:19 pg_service.conf.sample -rw-r--r-- 1 postgres daemon 335770 2007-02-11 03:19 postgres.bki -rw-r--r-- 1 postgres daemon 61773 2007-02-11 03:19 postgres.description -rw-r--r-- 1 postgres daemon 15322 2007-02-11 03:19 postgresql.conf.sample -rw-r--r-- 1 postgres daemon 40 2007-02-11 03:19 postgres.shdescription -rw-r--r-- 1 postgres daemon211 2007-02-11 03:19 psqlrc.sample -rw-r--r-- 1 postgres daemon 2689 2007-02-11 03:19 recovery.conf.sample -rw-r--r-- 1 postgres daemon 22311 2007-02-11 03:19 sql_features.txt -rw-r--r-- 1 postgres daemon 13385 2007-02-11 03:19 system_views.sql drwxr-xr-x 19 postgres daemon 4096 2007-02-11 03:19 timezone drwxr-xr-x 2 postgres daemon 4096 2007-02-11 03:19 timezonesets [EMAIL PROTECTED]:~$ ls -l /var/lib/pgsql/data/input/timezonesets/Default -rw-r--r-- 1 postgres daemon 28709 2007-02-11 03:19 /var/lib/pgsql/data/input/timezonesets/Default LC_time is set to de_DE, LC_ALL is set to de_DE.UTF-8. Does anybody have an idea what's going wrong here? Kind regards Markus Computec Media AG Sitz der Gesellschaft und Registergericht: Furth (HRB 8818) Vorstandsmitglieder: Johannes S. Gozalan (Vorsitzender) und Niels Herrmann Vorsitzender des Aufsichtsrates: Jurg Marquard Umsatzsteuer-Identifikationsnummer: DE 812 575 276 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Asynchronous replication of a PostgreSQL DB to a MySQL target
Hi! I'd like to export schema and data from a PostgreSQL database to a remote MySQL database; any changes to the PG-master should be reflected on the MySQL target in a matter of a few minutes to one hour max. Has anybody done something like this before? Here's some more background: We've got an Oracle database as our backend and a couple of PostgreSQL-DBs as our frontend databases; the schema of the backend DB is stable. There are so called publishing jobs running every few minutes; these jobs not only update the frontend databases with any changes in the backend, they also make changes to the frontend dbs schemas whenever the backend says so - the frontend schemas differ from the backend's, the DDL of the frontend dbs is partly defined by data in the backend. The logical thing to do would be to create another set of publishing jobs for the MySQL databases; however our current network layout makes this quite difficult, so I'd rather try and keep the MySQL db and one of the PostgreSQL dbs in near sync. My first problem is that the PostgreSQLs schema is not stable, so if I simply write a couple of jobs to transport the data, I need to alter these jobs and the MySQL schema whenever there are changes to the PG schema. The second problem lies in PostgreSQL-specifics such as tsearch2 - I actually do not need nor want to replicate such metadata. Custom datatypes and functions should also be exempt from this kind of replication. My hopes aren't all too high that there's an easy way to accomplish what I wish to do, so any advice would be very much welcome - even a can't be done that way by somebody who has tried to travel that path before :) Kind regards Markus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] invalid byte sequence for encoding error
Nis Jorgensen wrote: Oliver A. Rojo wrote: how do you fix your original db? Since I had only 3 occurrences of the error, I used hand-crafted update statements. The fact that the replacement for the invalid characters was constant and plain ascii made this very easy. If you have many occurrences of the error, or if you need to do replacement based on the invalid bytes, things become trickier. You might even be better of working on the dump file directly using perl/favourite scripting language I had the exact same problem with my upgrade - and a lot more than just a couple of occurences. The solution is quite easy however, so if you're prepared to simply eliminate the offending bytes, you'll find that iconv will be a very fast solution. However at least on my systems (Debian Sarge) iconv didn't like my 5GB sized dump files. So in order to successfully reimport the dumps, I had to split --line-bytese0m the SQL-file, pass the parts through iconv -c -f UTF8 -t UTF8 and concatenate them back into one file again. There were no more errors on feeding the dump back into psql and I didn't come across any missing data during my tests, so this has definitely done the trick for me. You should be aware that this will simply omit the illegal byte sequences from the dump. So if you've got some string foo[non-UTF-8-bytes]bar, it will be converted to a simple foobar in the result. So if you really need to keep things 100% accurate, you'll have to actually identify each of these byte-sequences, then find the corresponding UTF-8 representation and use some searchreplace-scripting on the dump before reloading it. Kind regards Markus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] MediaWiki and Postgresql?
Hi [EMAIL PROTECTED] wrote: Has anyone put MediaWiki up using the current version of Postgresql? I have, although our Mediawiki installation is currently not openly accessible. Can't say that it went like a breeze, but the obstacles where more or less minor and writing a little custom auth-plugin wasn't really too hard either. If your question was just along the lines of does it run, I can confirm that, yes, it does. I cannot say though how well it would stand up to high loads and if every query and index has been fully tweaked. Kind regards Markus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] configure: error: file 'tclConfig.sh' is required for Tcl
Hi! Being a Debian-user I haven't really got a clue about Fedora Core, but have you tried installing the tcl-devel-package? Kind regards Markus Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Im Auftrag von Teresa Noviello Gesendet: Freitag, 31. März 2006 10:16 An: pgsql-general@postgresql.org Betreff: [GENERAL] configure: error: file 'tclConfig.sh' is required for Tcl [...] I've veryfied tcl'installation with [EMAIL PROTECTED] pgsql]# rpm -qa | grep tcl tcl-8.4.9-3 but in directories /usr/lib/tcl8.4, /usr/share/tcl8.4, there ISN'T the file tclConfig.sh. [...] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Unicode Corruption and upgrading to 8.0.4. to 8.1
Hello! -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Im Auftrag von Howard Cole Gesendet: Dienstag, 6. Dezember 2005 13:41 An: 'PgSql General' Betreff: Re: [GENERAL] Unicode Corruption and upgrading to 8.0.4. to 8.1 Hi everyone, I have a problem with corrupt UTF-8 sequences in my 8.0.4 dump which is preventing me from upgrading to 8.1 - which spots the errors and refuses to import the data. Is there some SQL command that I can use to fix or cauterise the sequences in the 8.0.4 database before dumping to 8.1? I think the problem arose using invalid client encodings - which were not rejected prior to 8.1. We experienced the exact same problems. You may solve the problem by feeding the dump through iconv. See my earlier message on this issue http://archives.postgresql.org/pgsql-general/2005-11/msg00799.php On top of that you'd be well advised to try dumping using pg_dump of postgresql 8.1. Kind regards Markus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Unicode Corruption and upgrading to 8.0.4. to 8.1
Hi! -Ursprüngliche Nachricht- Von: Howard Cole [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 6. Dezember 2005 15:38 An: Markus Wollny Cc: PgSql General Betreff: Re: [GENERAL] Unicode Corruption and upgrading to 8.0.4. to 8.1 I am avoiding this solution at the moment since the database contains binary (ByteA) fields aswell as text fields and I am unsure what iconv would do to this data. Bytea-data in a plain text dump should be quite safe from iconv, as all the problematic characters (decimal value 32 or 126) in the binary string are represented as SQL escaped octets like \###. Kind regards Markus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] error in creating database
Hi! -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Im Auftrag von Karthik.S Gesendet: Dienstag, 6. Dezember 2005 13:26 An: pgsql-general@postgresql.org Betreff: [GENERAL] error in creating database Dear all, I am using postgres version: 7.1.3 in Red hat linux : 7.2. You should really consider upgrading. There has been a lot of development, both feature-wise and concerning bug-squashing, since the 7.1-days. Sometimes (nearly 50% of the times) the database creation fails by saying ERROR: CREATE DATABASE: source database template1 is being accessed by other users 8.1 has introduced the concept of a maintenance-database, but you can easily do something similar by either creating your db's with CREATE DATABASE foo WITH TEMPLATE = template0; (but anything you have changed in template1 after PostgreSQL-installation won't be in your new db then) or simply create another template-database, say template2 for this purpose. Kind regards Markus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] 'AS' column-alias beign ignored on outer select
Hi! Is this the expected behaviour? select ID , USK_RATING AS USK from ( select ID , USK_RATING from MAIN_SONY_PS2_GAME where ID = 101400 limit 1 ) as PRODUCT_LIST limit 1; id | usk_rating + 101400 | (1 row) Note the column-header being labeled 'usk_rating', not 'usk'. Obviously the 'AS' column alias of the outer select is being ignored in the resultset. select ID , USK from ( select ID , USK_RATING AS USK from MAIN_SONY_PS2_GAME where ID = 101400 limit 1 ) as PRODUCT_LIST limit 1; id | usk +- 101400 | (1 row) If the column alias is being declared in the subselect, the column alias is working. select version(); version -- PostgreSQL 8.1.0 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 Is this working as expected or is this a bug? Kind regards Markus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Errors upgrading from 7.3 to 8.1
Title: AW: [GENERAL] Errors upgrading from 7.3 to 8.1 Hello! We experienced the exact same problems. You may solve the problem by feeding the dump through iconv. See my earlier message on this issue http://archives.postgresql.org/pgsql-general/2005-11/msg00799.php On top of that you'd be well advised to try dumping using pg_dump of postgresql 8.1. Kind regards Markus -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] im Auftrag von Benjamin Smith Gesendet: Di 11/29/2005 01:22 An: Postgres General Betreff: [GENERAL] Errors upgrading from 7.3 to 8.1 Currently running Postgres 7.3.9 32bit on Fedora Core 1, and upgrading to PG 8.1 64bit on Centos 4. When I load the file, psql -U dbname dbname.sql I get this error: ERROR: invalid UTF-8 byte sequence detected near byte 0x96 when inserting fields that seem to contain HTML. What could be causing this? My understanding is that pg_dump should properly escape things so that I'm not trying to dump/load things improperly. The dumps are made (on the PG 7.3 server) pg_dump -d -f $OUTPUT.pgsql $db Are being restore with (on the new 8.1 server) psql -U $db -e $OUTPUT.pgsql -Ben -- The best way to predict the future is to invent it. - XEROX PARC slogan, circa 1978 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Incomplete Startup Packet
Title: [GENERAL] Incomplete Startup Packet ? Hi! We're getting "incomplete startup packet" messages in our logfiles due to some sort of system probe run by our service provider which checks if PG is still running. In our case they're harmless of course. Are you sure that you're not running something along those lines, too? Kind regards Markus
Re: [GENERAL] invalid UTF-8 byte sequence detected
Hi! -Ursprüngliche Nachricht- Von: Bruce Momjian [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 15. November 2005 19:46 An: Markus Wollny Cc: pgsql-general@postgresql.org Betreff: Re: [GENERAL] invalid UTF-8 byte sequence detected I am also confused how invalid UTF8 sequences got into your database. It shouldn't have been possible. Our databases were originally encoded in ASCII, though they should have been LATIN9 - or UNICODE; this has been remedied a long time ago using iconv on the dumps; our PostgreSQL-version then was 7.4 and we converted databases and dumps to UTF-8. Maybe the invalid byte sequences have been entered sometimes during our migration from ODBC to JDBC while our encoding was still a mess - though I would have thought that this should have been resolved by dumpiconvrestore then. However, I do suspect that the cause of the issue was really more or less a bug in PostgreSQL 8.1, which accepted certain illegal byte sequences. I our case, I found that the re-import of the dump errored out on ISO-8859-1 encoded backticks (´) - certain mournfully misled individuals do use this degu-character instead of the apostrophe even tough it's more difficult to type on a german keyboard layout. And quite wrong, too. Anyway, I found some reference in the hackers-list that encoding-consistency for Unicode has been tightened down (see http://archives.postgresql.org/pgsql-hackers/2005-10/msg00972.php ). Both a solution and a suggestion have been posted in this thread; Christopher Kings-Lynne has suggested to include a reference to this issue in the 'upgrading/back compatibiliy' section for these release notes - I do strongly second his suggestion :) The suggested solution was to feed the plain dump again through iconv; however at least on my systems (Debian Sarge) iconv didn't like my 5GB sized dump files. So in order to successfully reimport the dumps, I had to split --line-bytes=650m the SQL-file, pass the parts through iconv -c -f UTF8 -t UTF8 and concatenate them back into one file again. There were no more errors on feeding the dump back into psql and I didn't come across any missing data during my tests, so this has definitely done the trick for me. As 8.1 has tightened down encoding-consistency for Unicode, I believe that the databases should be safe from any illegal byte-sequences in text-fields from now on. Kind regards Markus ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] invalid UTF-8 byte sequence detected
Hello! I am currently testdriving migration of our PostgreSQL 8.0 databases to 8.1; in this process I have stumbled a couple of times over certain errors in text-fields that lead to error-messages during import of the dump like these: 2005-11-09 14:57:34 CET - 9354: [EMAIL PROTECTED]ERROR: invalid UTF-8 byte sequence detected near byte 0xb4 2005-11-09 14:57:34 CET - 9354: [EMAIL PROTECTED]CONTEXT: COPY board_message, line 1125662, column text: HI Besteht ein gewisser Nachteil, wenn ich nur eins von den beiden kaufe, da in beiden Addon▒s viel... 2005-11-09 14:57:34 CET - 9354: [EMAIL PROTECTED]STATEMENT: COPY board_message (board_id, thread_id, father_id, message_id, user_id, title, signat ure, follow_up, count_reply, last_reply, created, article_id, logged_ip, state_id, user_login, user_status, user_rank, user_rank_description, user_rank_picture, user_rights, text, deleted_user_id, deleted_date, deleted_login, user_created, poll_id, idxfti) FROM stdin; 2005-11-09 14:57:49 CET - 9354: [EMAIL PROTECTED]ERROR: invalid UTF-8 byte sequence detected near byte 0x98 2005-11-09 14:57:49 CET - 9354: [EMAIL PROTECTED]CONTEXT: COPY kidszone_tournament2005_user, line 427, column phone: 02302▒74 2005-11-09 14:57:49 CET - 9354: [EMAIL PROTECTED]STATEMENT: COPY kidszone_tournament2005_user (id, first_name, last_name, adress, birthday, phone, email, place, permission, ude, ude_box, invited) FROM stdin; There are not too many occurrences of the same type - five altogether in a 1.8GB compressed dumpfile, but still it has me worried and leaves me with some questions: 1.) How could I have prevented insertion of these invalid byte-sequences in the first place? We're using UTF-8 encoded databases, data is mostly inserted by users via browser applications, our websites are UTF-8 encoded, too, but still we cannot really make 100% sure that all clients behave as expected; on the other hand, it would be extremely inconvenient if we had to check each and every text input for character set conformance in the application, so is there a way to ascertain sane data via some database-setting? pg_restore does throw this error and indeed terminates after that (I used custom dump format for pg_dump), psql on the other hand just continues with the import (using a pgdumpall-output that generates a standard SQL-script), although it too throws the error. 2.) How does this really affect the value of the database-dumps? psql continues with import after the error, but the table where this error occurred remains empty, as the affected COPY-statement has failed altogether due to this error. So a plain no-worries import in my case would present me a result with five tables empty - one of them quite large... Is there some kind of magic, maybe involving some perl or whatever, that could help to clean up the dump before the import, so I can accomplish a full restore? Kind regards, Markus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Finding (and deleting) dupes in relation table
Hello! CSN [EMAIL PROTECTED] wrote: I have a table that relates id's of two other tables: table1id, table2id Dupes have found their way into it (create unique index across both fields fails). Is there a quick and easy way to find and delete the dupes (there are tens of thousands of records)? Thanks, CSN If your table was created WITH OIDS you could identify the duplicates thus: select a.table1id , a.table12d , max(a.oid) as maxoid , count(a.oid) as coid from schema.mytable a, schema.mytable b where a.table1id = b.table1id and a.table2id=b.table2id and a.oid b.oid group by a.table1id, a.table2id order by a.table1id; If you wish to delete surplus rows, you might do the following: delete from schema.mytable where oid in ( select maxoid from ( select a.table1id, a.table12d, max(a.oid) as maxoid, count(a.oid) as coid from schema.mytable a, schema.mytable b where a.table1id = b.table1id and a.table2id=b.table2id and a.oid b.oid group by a.table1id, a.table2id order by a.table1id ) as foo where coid 1 ); This will delete the oldest tuple of a duplicate set of rows; if there are more than two tuples in a set, you'll want to execute this a couple of times until there's no duplicate left, as the delete will only reduce a set by one tuple at a time. I'd also recommend to apply a PRIMARY KEY constraint afterwards instead of just a unique index - this will prevent NULL-entries as well as creating the desired unique index - and I think it's good practice to have a primary key on about every table there is, except when it's just a junk data table like a logging table where content is regularly evaluated and discarded. Kind regards Markus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Upgrade instructions -7.4.8/8.0.3 incomplete regarding tsearch2
Hello! I am currently trying to import a 8.0.1 database dump into a 8.0.3 installation. I, too, have at first stumbled over the tsearch2-issue which is explained here: http://www.postgresql.org/docs/8.0/static/release-7-4-8.html (should make a rule to thoroughly read the upgrade notes first :) ) So I followed the instructions and executed the recommended procedure in every database of my 8.0.1 cluster: BEGIN; UPDATE pg_proc SET proargtypes[0] = 'internal'::regtype WHERE oid IN ( 'dex_init(text)'::regprocedure, 'snb_en_init(text)'::regprocedure, 'snb_ru_init(text)'::regprocedure, 'spell_init(text)'::regprocedure, 'syn_init(text)'::regprocedure ); -- The command should report having updated 5 rows; -- if not, rollback and investigate instead of committing! COMMIT; Unfortunately, this is indeed not sufficient, if one has configured support for one or more additional languages, which are not configured per default (i.e. anything but English and Russian, e.g. German). In my case, I have got a function dinit_de which is declared thus: -- Name: dinit_de(text); Type: FUNCTION; Schema: public; Owner: postgres -- CREATE FUNCTION dinit_de(text) RETURNS internal AS '$libdir/dict_de', 'dinit_de' LANGUAGE c; ALTER FUNCTION public.dinit_de(text) OWNER TO postgres; So when restoring the dump from 8.0.1 to 8.0.3, I receive this error: ERROR: unsafe use of INTERNAL pseudo-type DETAIL: A function returning internal must have at least one internal argument. In order to be able to restore the dump, the function declaration must be altered according to the new declaration of the other tsearch2-functions thus: CREATE FUNCTION dinit_de(internal) RETURNS internal AS '$libdir/tsearch2', 'dinit_de' LANGUAGE c; So the recommended procedure for upgrading the databases in my particular case should be BEGIN; UPDATE pg_proc SET proargtypes[0] = 'internal'::regtype WHERE oid IN ( 'dex_init(text)'::regprocedure, 'snb_en_init(text)'::regprocedure, 'snb_ru_init(text)'::regprocedure, 'dinit_de(text)'::regprocedure, 'spell_init(text)'::regprocedure, 'syn_init(text)'::regprocedure ); -- The command should report having updated 6 rows; -- if not, rollback and investigate instead of committing! COMMIT; I recommend that anyone who wishes to upgrade their tsearch2-databases examine their function declarations for any declaration which is of the same type (returning internal without having at least one internal argument); this certainly applies to the function-declarations for the initialization of any tsearch2-dictionaries, which originally expect text as input and are supposed to return internal. These declarations must be included in the pre-upgrade-procedure, or else there will be errors on restoring the dump. I don't know how this will affect the restored database or if the issue can be somehow resolved after the dump has already been restored without previous correction of the matter, maybe someone else can shed some light on that. I think that it's probably more on the safe side to assume, that it's better to have a restore process that doesn't throw any such errors. Kind regards Markus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Using gdb to obtain statement
Hello! As pg_stat_activity.current_query truncates statements to about 255 characters, I've tinkered a bit with gdb, as suggested by Tom Lane a little while ago. But when I attach to the backend in question, the only output I get from p debug_query_string is some number, for example: mysrv:/var/log # gdb /opt/pgsql/bin/postgres 1485 GNU gdb 5.2.1 Copyright 2002 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. Type show copying to see the conditions. There is absolutely no warranty for GDB. Type show warranty for details. This GDB was configured as i586-suse-linux... Attaching to program: /opt/pgsql/bin/postgres, process 1485 Reading symbols from /lib/libz.so.1...done. Loaded symbols for /lib/libz.so.1 Reading symbols from /lib/libreadline.so.4...done. Loaded symbols for /lib/libreadline.so.4 Reading symbols from /lib/libcrypt.so.1...done. Loaded symbols for /lib/libcrypt.so.1 Reading symbols from /lib/libresolv.so.2...done. Loaded symbols for /lib/libresolv.so.2 Reading symbols from /lib/libnsl.so.1...done. Loaded symbols for /lib/libnsl.so.1 Reading symbols from /lib/libdl.so.2...done. Loaded symbols for /lib/libdl.so.2 Reading symbols from /lib/i686/libm.so.6...done. Loaded symbols for /lib/i686/libm.so.6 Reading symbols from /lib/i686/libc.so.6...done. Loaded symbols for /lib/i686/libc.so.6 Reading symbols from /lib/libncurses.so.5...done. Loaded symbols for /lib/libncurses.so.5 Reading symbols from /lib/ld-linux.so.2...done. Loaded symbols for /lib/ld-linux.so.2 Reading symbols from /usr/lib/gconv/ISO8859-1.so...done. Loaded symbols for /usr/lib/gconv/ISO8859-1.so Reading symbols from /lib/libnss_files.so.2...done. Loaded symbols for /lib/libnss_files.so.2 0x40198cd4 in read () from /lib/i686/libc.so.6 (gdb) p debug_query_string $1 = 137763608 What am I doing wrong? Kind regards Markus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Problems with pgcrypto and special characters
Hello!
To get straight to the point, here's my problem:
mypgdb=# select bytea2text(decrypt(encrypt('Tübingen'::bytea,
'mypassphrase'::bytea,'bf'::text),'mypassphrase'::bytea,'bf'::text)) as foo;
foo
-
T\303\274bingen
(1 row)
I have compiled and installed pg_crypto and I'am using the following function
as workaround for a bytea-to-text-cast:
create or replace function bytea2text(bytea) returns text as '
begin
return $1;
end;
' language plpgsql;
The cluster was initialized with locale de_DE.UTF-8, pg_controldata confirms:
LC_COLLATE: de_DE.UTF-8
LC_CTYPE: de_DE.UTF-8
Database version is PostgreSQL 8.0.1 on i686-pc-linux-gnu, compiled by GCC gcc
(GCC) 3.2
I think I'm missing something very obvious here, so please give me a hint: How
can I use pgcrypto to encrypt and decrypt text which contains UTF-8 special
characters like german umlauts? I think that this simple bytea2text-function
probably needs a replacement, but I haven't got the faintest clue about how to
actually retrieve the original input after encryption. Any help would be
tremendously appreciated :)
Thanks in advance!
Kind regards
Markus
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Problems with pgcrypto and special characters
Hi!
-Original Message-
From: Ragnar Hafsta [mailto:[EMAIL PROTECTED]
are you sure your problem is with pg_crypto ?
what does this produce:
select bytea2text('Tbingen'::bytea) as foo;
?
Well I'm sure it's not WITH pgcrypto but with actually using pgcrypto in
conjunction with UTF-8 encoded text. This function doesn't do anything but
replace a bytea::text-cast.
have you tried to use encode()/decode() instead ?
untested:
select
decode(
decrypt(
encrypt(
encode('Tbingen','escape') ,
'mypassphrase'::bytea,
'bf'::text
),
'mypassphrase'::bytea,
'bf'::text
)
) as foo;
Yes, and that doesn't work either:
mypgdb=# select decode(encode('Tbingen'::text::bytea,'escape'),'escape');
decode
-
T\303\274bingen
(1 row)
But I just found the bugger - we both confused encode and decode :)
mypgdb=# select encode(decode('Tbingen','escape'),'escape');
encode
--
Tbingen
(1 row)
Now using pgcrypto works, too:
mypgdb=# select
encode(decrypt(encrypt(decode('Tbingen'::text,'escape'),'mypassphrase','bf'),'mypassphrase','bf'),'escape');
encode
--
Tbingen
(1 row)
Thanks nevertheless, this was exactly the push in the right direction that I
needed!
Kind regards
Markus
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Problems with pgcrypto and special characters
Hi!
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Possibly a binary cast (WITHOUT FUNCTION) would solve your problem,
though I doubt it will work well on bytea values containing \0.
Thanks, I've been a bit thick here, but I just found the solution to my problem
- and that doesn't need this awkward function nor any type of extra WITHOUT
FUNCTION casts - just decode and encode, alas in exactly the opposite order
than I originally expected.
mypgdb=# select decode('Tbingen'::text,'escape');
decode
-
T\303\274bingen
(1 row)
mypgdbe=# select encode('T\303\274bingen','escape');
encode
--
Tbingen
(1 row)
I think this should be safe for any kind of bytea value.
Kind regards
Markus
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] High volume inserts - more disks or more CPUs?
Hi! -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Im Auftrag von Guy Rouillier Gesendet: Montag, 13. Dezember 2004 07:17 An: PostgreSQL General Betreff: [GENERAL] High volume inserts - more disks or more CPUs? (1) Would we be better off with more CPUs and fewer disks or fewer CPUs and more disks? From my experience, it's generally a good idea to have as many disks as possible - CPU is secondary. Having enough RAM so that at least the frequently accessed parts of your db data including the indexes fit completely into memory is also a good idea. (3) If we go with more disks, should we attempt to split tables and indexes onto different drives (i.e., tablespaces), or just put all the disks in hardware RAID5 and use a single tablespace? RAID5 is not an optimum choice for a database; switch to RAID0+1 if you can afford the disk space lost - this yields much better insert performance than RAID5, as there's no parity calculation involved. There's another performance gain to be achieved by moving the WAL-files to another RAID-set than the database files; splitting tablespaces across RAID-sets usually won't do much for you in terms of performance, but might be convenient when you think about scaling in size. Kind regards Markus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] How can I recovery old Data from files and folders on windows?
Hi! You can't. You'll have to restore your erroneous version first, then dump your data, the reinstall and use restore to restore the data you want. Kind regards Markus -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Im Auftrag von Premsun Choltanwanich Gesendet: Dienstag, 7. Dezember 2004 10:49 An: [EMAIL PROTECTED] Betreff: [GENERAL] How can I recovery old Data from files and folders on windows? I got some problem on PostgreSQL 8 for windows so I uninstall and reinstall it again. Before I uninstall PostgreSQL 8 I already backup all files and folders (copy all to other place). The problem is how can I restore by use files and folders that I already backup. If I try to restore by put all of it back it will be make a same error. I just want to restore only DATA (databases, functions, views, users, group etc). Please suggest me that How can I recovery old Data from files and folders on windows? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] why use SCHEMA? any real-world examples?
Hi! It's really just a convenience-thing to organize your data in a more intuitive way. We're running several online magazines, each of those with a sort of entity-database, but each with their own articles. So we've just put the entity-data in the public schema, whereas the magazine-specific data is going in their own schemata. That way we can simply use the very same queries for all of our magazines' applications, just by implementing the magazine-schema as a variable which is set at query-runtime. Kind regards Markus -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Im Auftrag von Miles Keaton Gesendet: Donnerstag, 25. November 2004 06:13 An: [EMAIL PROTECTED] Betreff: [GENERAL] why use SCHEMA? any real-world examples? I just noticed PostgreSQL's schemas for my first time. (http://www.postgresql.org/docs/current/static/ddl-schemas.html) I Googled around, but couldn't find any articles describing WHY or WHEN to use schemas in database design. Since the manual says HOW, could anyone here who has used schemas take a minute to describe to a newbie like me why you did? What benefits did they offer you? Any drawbacks? Thanks for your time. - Miles ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Tsearch2 and Unicode?
Hi!
I dug through my list-archives - I actually used to have the very same problem
that you described: special chars being swallowed by tsearch2-functions. The
source of the problem was that I had INITDB'ed my cluster with [EMAIL
PROTECTED] as locale, whereas my databases used Unicode encoding. This does not
work correctly. I had to dump, initdb to the correct UTF-8-locale (de_DE.UTF-8
in my case) and reload to get tsearch2 to work correctly. You may find the
original discussion here:
http://archives.postgresql.org/pgsql-general/2004-07/msg00620.php
If you wish to find out which locale was used during INITDB for your cluster,
you may use the pg_controldata program that's supplied with PostgreSQL.
Kind regards
Markus
-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Im Auftrag von
Dawid Kuroczko
Gesendet: Mittwoch, 17. November 2004 17:17
An: Pgsql General
Betreff: [GENERAL] Tsearch2 and Unicode?
I'm trying to use tsearch2 with database which is in
'UNICODE' encoding.
It works fine for English text, but as I intend to search
Polish texts I did:
insert into pg_ts_cfg('default_polish', 'default',
'pl_PL.UTF-8'); (and I updated other pg_ts_* tables as
written in manual).
However, Polish-specific chars are being eaten alive, it seems.
I.e. doing select to_tsvector('default_polish', body) from
messages; results in list of words but with national chars stripped...
I wonder, am I doing something wrong, or just tsearch2
doesn't grok Unicode, despite the locales setting? This also
is a good question regarding ispell_dict and its feelings
regarding Unicode, but that's another story.
Assuming Unicode unsupported means I should perhaps... oh,
convert the data to iso8859 prior feeding it to_tsvector()...
interesting idea, but so far I have failed to actually do
it. Maybe store the data as 'bytea' and add a column with
encoding information (assuming I don't want to recreate whole
database with new encoding, and that I want to use unicode
for some columns (so I don't have to keep encoding with every
text everywhere...).
And while we are at it, how do you feel -- an extra column
with tsvector and its index -- would it be OK to keep it away
from my data (so I can safely get rid of them if need be)?
[ I intend to keep index of around 2 000 000 records, few KBs
of text each ]...
Regards,
Dawid Kuroczko
---(end of
broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---(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] Tsearch2 and Unicode?
Hi!
Hi!
Oleg, what exactly do you mean by tsearch2 doesn't support unicode yet?
It does seem to work fine in my database, it seems:
./pg_controldata [mycluster] gives me
pg_control version number:72
[...]
LC_COLLATE: de_DE.UTF-8
LC_CTYPE: de_DE.UTF-8
community_unicode=# SELECT pg_encoding_to_char(encoding) AS encoding FROM
pg_database WHERE datname='community_unicode';
encoding
--
UNICODE
(1 row)
community_unicode=# select to_tsvector('default_german', 'Ich fände, daß das
Fehlen von Umlauten ein Ärgernis wäre.');
to_tsvector
--
'daß':3 'wäre':10 'fehlen':5 'fände':2 'umlauten':7 'Ärgernis':9
(1 row)
community_unicode=# SELECT message_id
community_unicode-# , rank(idxfti, to_tsquery('default_german',
'Könige|Söldner'),0) as rank
community_unicode-# FROM ct_com_board_message
community_unicode-# WHERE idxfti @@ to_tsquery('default_german',
'Könige|Söldner')
community_unicode-# order by rank desc
community_unicode-# limit 10;
message_id | rank
+--
3191632 | 0.686189
2803233 | 0.686189
2935325 | 0.686189
2882337 | 0.686189
2842006 | 0.686189
2854329 | 0.686189
2841962 | 0.686189
2999851 | 0.651322
2869839 | 0.651322
2999799 | 0.61258
(10 rows)
These results look alright to me, so I cannot reproduce this phenomenon of
disappearing special characters in a unicode-database. Dawid, are you sure, you
INITDB'd your cluster to the correct locale-settings?
Kind regards
Markus
-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Im Auftrag von
Oleg Bartunov
Gesendet: Mittwoch, 17. November 2004 17:32
An: Dawid Kuroczko
Cc: Pgsql General
Betreff: Re: [GENERAL] Tsearch2 and Unicode?
Dawid,
unfortunately, tsearch2 doesn't support unicode yet.
If you keep tsvector separately from data than you'll need
one more join.
Oleg
---(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] Can this be indexed?
PostgreSQL doesn't provide pre-configured support for materialized views as such, but using some PL/pgSQL and triggers, one can easily implement any kind of materialized view as seen fit for the specific intended purpose (Snapshot, Eager, Lazy, Very Lazy). You may find an excellent tutorial on materialized views with PostgreSQL here: http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Im Auftrag von Net Virtual Mailing Lists Gesendet: Samstag, 6. November 2004 16:49 An: Matteo Beccati Betreff: Re: [GENERAL] Can this be indexed? I am not clear how to use a trigger for this, I will need to look into that It is my understanding that Postgres does not have materialized views though (which I believe would solve this problem nicely) - am I mistaken?... - Greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [OpenFTS-general] AW: [GENERAL] tsearch2, ispell, utf-8 and german special characters
Hi! -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Im Auftrag von Markus Wollny Gesendet: Mittwoch, 21. Juli 2004 17:04 An: Oleg Bartunov Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Betreff: [OpenFTS-general] AW: [GENERAL] tsearch2, ispell, utf-8 and german special characters The issue with the unrecognized stop-word 'ein' which is converted by to_tsvector to 'eint' remains however. Now here's as much detail as I can provide: Ispell is Version 3.1.20 10/10/95, patch 1. I've just upgraded Ispell to the latest version (International Ispell Version 3.2.06 08/01/01), but that didn't help; by now I think it might be something to do with a german language peculiarity or with something in the german dictionary. In german.med, there is an entry eint/EGPVWX So the ts_vector output is just a bit like a wrong guess. Doesn't it evaluate the stopword-list first before doing the lookup in the Ispell-dictionary? Kind regards Markus Wollny ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [OpenFTS-general] AW: [GENERAL] tsearch2, ispell, utf-8 and german special characters
Hi!
ts2test=# select * from ts_debug('Jeden Tag wird man ein bisschen weiser');
ts_name | tok_type | description | token | dict_name | tsvector
+--+-+--+-+
default_german | lword| Latin word | Jeden| {de_ispell} |
default_german | lword| Latin word | Tag | {de_ispell} | 'tag'
default_german | lword| Latin word | wird | {de_ispell} |
default_german | lword| Latin word | man | {de_ispell} |
default_german | lword| Latin word | ein | {de_ispell} | 'eint'
default_german | lword| Latin word | bisschen | {de_ispell} | 'bisschen'
default_german | lword| Latin word | weiser | {de_ispell} | 'weise'
(7 rows)
cat german.stop|grep ^ein$
ein
'jeden', 'man', 'wird' and 'ein' are all in german.stop; the first three words are
correctly recognozed as stopwords, whereas the last one is converted to 'eint',
although 'ein' is a stopword, too. I still don't understand what exactly is happening
and if I should be concerned by that sort of wrong guess - so 'ein' is just
converted to 'eint' every time, no matter if it's in the stopwords-file or not, but on
the other hand, as this applies to to_tsvector(), to_tsquery() and lexize(), this
behaviour would be consitant throughout tsearch2 - thus making any search containing
'ein' a little bit fuzzier, but nonetheless still usable. It's still some sort of
cosmetic bug, though, but I guess that's probably due to German being somewhat less
IT-friendly than english.
Kind regards
Markus
-Original Message-
From: Oleg Bartunov [mailto:[EMAIL PROTECTED]
Sent: Wed 7/21/2004 22:24
To: Markus Wollny
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject:Re: AW: [OpenFTS-general] AW: [GENERAL] tsearch2, ispell, utf-8 and
german special characters
On Wed, 21 Jul 2004, Markus Wollny wrote:
Hi!
-Urspr?ngliche Nachricht-
Von: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Im
Auftrag von Markus Wollny
Gesendet: Mittwoch, 21. Juli 2004 17:04
An: Oleg Bartunov
Cc: [EMAIL PROTECTED];
[EMAIL PROTECTED]
Betreff: [OpenFTS-general] AW: [GENERAL] tsearch2, ispell,
utf-8 and german special characters
The issue with the unrecognized stop-word 'ein' which is
converted by to_tsvector to 'eint' remains however. Now
here's as much detail as I can provide:
Ispell is Version 3.1.20 10/10/95, patch 1.
I've just upgraded Ispell to the latest version (International Ispell Version 3.2.06
08/01/01), but that didn't help; by now I think it might be something to do with a
german language peculiarity or with something in the german dictionary. In
german.med, there is an entry
ispell itself don't used in tsearch2, only dict,aff files !
eint/EGPVWX
So the ts_vector output is just a bit like a wrong guess. Doesn't it evaluate the
stopword-list first before doing the lookup in the Ispell-dictionary?
yes. There is very usefull function for debugging I always recommend to use -
ts_debug. See my notes
(http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_Notes)
for examples.
Kind regards
Markus Wollny
---
This SF.Net email is sponsored by BEA Weblogic Workshop
FREE Java Enterprise J2EE developer tools!
Get your free copy of BEA WebLogic Workshop 8.1 today.
http://ads.osdn.com/?ad_idG21alloc_id040op?k
___
OpenFTS-general mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/openfts-general
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] ./configure --with-java fails
Hi!
I am trying to build
PostgreSQL 7.4.3 with Java enabled; I've got Apache Ant version 1.5 and j2sdk1.4.1_05
installed:
Verifiying
ant:
# which javac/usr/java/j2sdk1.4.1_05/bin/javac# ant
-versionApache Ant version 1.5 compiled on October 15
2002
Verifying
java-compiler:
# which
javac/usr/java/j2sdk1.4.1_05/bin/javac
Verifying
environment variables:
# export|grep javadeclare -x
JAVA_BINDIR="/usr/java/j2sdk1.4.1_05/bin"declare -x
JAVA_HOME="/usr/java/j2sdk1.4.1_05"declare -x
JAVA_ROOT="/usr/java/j2sdk1.4.1_05"declare -x
JRE_HOME="/usr/java/j2sdk1.4.1_05/jre"declare -x
PATH="/sbin:/usr/sbin:/usr/local/sbin:/root/bin:/usr/local/bin:/usr/bin:/usr/X11R6/bin:/bin:/usr/games:/opt/kde3/bin:/usr/java/j2sdk1.4.1_05/bin:/usr/java/j2sdk1.4.1_05/jre/bin"
I ran the
./configure-script as follows:
./configure --prefix=/opt/pgsql
--datadir=/var/lib/pgsql/data/base --enable-locale --with-perl --with-java
--enable-odbc --enable-syslog
The last lines of
configure-output are
checking
whether to build Java/JDBC tools... yes
checking for
jakarta-ant... nochecking for ant... /usr/bin/antchecking whether
/usr/bin/ant works... noconfigure: error: ant does not
work
The configure.log
contains the following relevant lines:
configure:3139: checking whether to build
Java/JDBC toolsconfigure:3150: result: yesconfigure:3157: checking for
jakarta-antconfigure:3190: result: noconfigure:3157: checking for
antconfigure:3175: found /usr/bin/antconfigure:3187: result:
/usr/bin/antconfigure:3198: checking whether /usr/bin/ant
worksconfigure:3222: /usr/bin/ant -buildfile conftest.xml
12Buildfile: conftest.xml
conftest: [javac]
Compiling 1 source file [javac] Modern compiler not found
- looking for classic compiler
BUILD
FAILEDfile:/usr/src/postgresql/postgresql-7.4.3/conftest.xml:3: Cannot use
classic compiler, as it is not available. A common solution is to set the
environment variable JAVA_HOME to your jdk directory.
Total time: 2
secondsconfigure:3225: $? = 1configure: failed java program
was:public class conftest { int testmethod(int a, int
b) { return a +
b; }}configure: failed build file
was:project name="conftest" default="conftest"target
name="conftest" javac srcdir="."
includes="conftest.java"
/javac/target/projectconfigure:3241:
result: noconfigure:3245: error: ant does not work
Help would be very
much appreciated; on second thoughts: Is it at all necessary to build the
JDBC-driver myself or can I safely use the precompiled .jar-file from http://jdbc.postgresql.org/download.htmlon
my clients? And if I compile without the --with-java,will the resulting
PostgreSQL-server-installation have any shortcomings in integrating with
JDBC-applications? What exactly are the "Java/JDBC
tools" aside from the JDBC-driver
jar-file?
Kind
regards
Markus
Re: [GENERAL] ./configure --with-java fails
-Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Im Auftrag von Kris Jurka Gesendet: Dienstag, 6. Juli 2004 13:11 An: Markus Wollny Cc: [EMAIL PROTECTED] Betreff: Re: [GENERAL] ./configure --with-java fails Your setup looks good, perhaps changing the configure script to run ant with -debug would help? Thanks, now I got loads and loads of debug output, so I'm still confused, albeit on a higher level... :) !--- config.log excerpt starts here --- configure:3222: /usr/bin/ant -debug -buildfile conftest.xml 12 Apache Ant version 1.5 compiled on October 15 2002 Buildfile: conftest.xml Detected Java version: 1.3 in: /usr/lib/SunJava2-1.3.1/jre Detected OS: Linux +User task: propertyfile org.apache.tools.ant.taskdefs.optional.PropertyFile +User task: vsscheckin org.apache.tools.ant.taskdefs.optional.vss.MSVSSCHECKIN +User task: sql org.apache.tools.ant.taskdefs.SQLExec +User task: cvspass org.apache.tools.ant.taskdefs.CVSPass Could not load class (org.apache.tools.ant.taskdefs.optional.perforce.P4Reopen) for task p4reopen +User task: csc org.apache.tools.ant.taskdefs.optional.dotnet.CSharp +User task: dirname org.apache.tools.ant.taskdefs.Dirname Could not load class (org.apache.tools.ant.taskdefs.optional.ejb.WLRun) for task wlrun Could not load class (org.apache.tools.ant.taskdefs.optional.perforce.P4Label) for task p4label Could not load class (org.apache.tools.ant.taskdefs.optional.perforce.P4Revert) for task p4revert +User task: replaceregexp org.apache.tools.ant.taskdefs.optional.ReplaceRegExp +User task: get org.apache.tools.ant.taskdefs.Get +User task: jjtree org.apache.tools.ant.taskdefs.optional.javacc.JJTree +User task: sleep org.apache.tools.ant.taskdefs.Sleep +User task: jarlib-display org.apache.tools.ant.taskdefs.optional.extension.JarLibDisplayTask +User task: dependset org.apache.tools.ant.taskdefs.DependSet +User task: zip org.apache.tools.ant.taskdefs.Zip +User task: patch org.apache.tools.ant.taskdefs.Patch +User task: jspc org.apache.tools.ant.taskdefs.optional.jsp.JspC +User task: style org.apache.tools.ant.taskdefs.XSLTProcess +User task: test org.apache.tools.ant.taskdefs.optional.Test +User task: tstamp org.apache.tools.ant.taskdefs.Tstamp +User task: unwar org.apache.tools.ant.taskdefs.Expand +User task: vsshistory org.apache.tools.ant.taskdefs.optional.vss.MSVSSHISTORY Could not load class (org.apache.tools.ant.taskdefs.optional.IContract) for task icontract +User task: cvschangelog org.apache.tools.ant.taskdefs.cvslib.ChangeLogTask Could not load class (org.apache.tools.ant.taskdefs.optional.perforce.P4Submit) for task p4submit +User task: ccmcheckin org.apache.tools.ant.taskdefs.optional.ccm.CCMCheckin Could not load class (org.apache.tools.ant.taskdefs.optional.perforce.P4Change) for task p4change +User task: bzip2 org.apache.tools.ant.taskdefs.BZip2 Could not load class (org.apache.tools.ant.taskdefs.optional.perforce.P4Delete) for task p4delete +User task: vssadd org.apache.tools.ant.taskdefs.optional.vss.MSVSSADD +User task: javadoc org.apache.tools.ant.taskdefs.Javadoc +User task: translate org.apache.tools.ant.taskdefs.optional.i18n.Translate +User task: signjar org.apache.tools.ant.taskdefs.SignJar Could not load class (org.apache.tools.ant.taskdefs.optional.ide.VAJLoadProjects) for task vajload +User task: jarlib-available org.apache.tools.ant.taskdefs.optional.extension.JarLibAvailableTask +User task: WsdlToDotnet org.apache.tools.ant.taskdefs.optional.dotnet.WsdlToDotnet +User task: buildnumber org.apache.tools.ant.taskdefs.BuildNumber +User task: jpcovmerge org.apache.tools.ant.taskdefs.optional.sitraka.CovMerge +User task: ejbjar org.apache.tools.ant.taskdefs.optional.ejb.EjbJar +User task: war org.apache.tools.ant.taskdefs.War Could not load class (org.apache.tools.ant.taskdefs.optional.starteam.StarTeamList) for task stlist +User task: rename org.apache.tools.ant.taskdefs.Rename +User task: sequential org.apache.tools.ant.taskdefs.Sequential +User task: serverdeploy org.apache.tools.ant.taskdefs.optional.j2ee.ServerDeploy +User task: property org.apache.tools.ant.taskdefs.Property +User task: move org.apache.tools.ant.taskdefs.Move +User task: copydir org.apache.tools.ant.taskdefs.Copydir +User task: cccheckin org.apache.tools.ant.taskdefs.optional.clearcase.CCCheckin +User task: wljspc org.apache.tools.ant.taskdefs.optional.jsp.WLJspc +User task: fixcrlf org.apache.tools.ant.taskdefs.FixCRLF Could not load class (org.apache.tools.ant.taskdefs.optional.net.TelnetTask) for task telnet +User task: sosget org.apache.tools.ant.taskdefs.optional.sos.SOSGet +User task: pathconvert org.apache.tools.ant.taskdefs.PathConvert +User task: record org.apache.tools.ant.taskdefs.Recorder Could not load
[GENERAL] Character Encoding Confusion
Hi! We've been using PostgreSQL (currently 7.4) via ODBC with ColdFusion until now and didn't experience any problems. But now we want to move from ColdFusion 4.5 to ColdFusion MX, thus abandoning ODBC and migrating to JDBC. As ODBC seems to be blissfully unaware of any character encodings whatsoever, so were we - our databases are encoded in SQL_ASCII, although we have stored german special chars (ÄÖÜäöü and ß), and from what I have read so far, these are stored as multibyte and thus exceed the SQL-ASCII specification. With ODBC we never noticed the mistake we'd made. Now with JDBC/ColdFusion MX 6.1, we see all sorts of weird characters on our web-application, but not the ones which are stored in the database. I tried setting different character sets for the JDBC-driver, using the URL-syntax jdbc:postgresql://123.456.789.012:5432/database?charSet=characterSet with charSet=iso-8859-1 or charSet=UTF-8 for example, but that just change anything. Now is there some way to elegantly resolve the issue without dropping and recreating the databases in order to change the encoding? Can we somehow get the JDBC-driver to act just as the ODBC-driver did - silently passing on the bad characters without changing anything? And if there is just no way to avoid that, what's the correct procedure for changing the encoding anyway? How would I be able to migrate the current data without any data-loss and with the least possible downtime? Kind regards Markus ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] tsearch2 column update produces word too longerror
Hi!
Now I really couldn't code C to save my life, but I managed to elicit
some more debugging info. It's still dumb-user-interaction as suspected,
but this is an issue I have to take into account as a basis; here's the
patch for ts_cfg.c:
if (lenlemm = MAXSTRLEN)
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
!errmsg(word is too long(%d):
%s,lenlemm,lemm)));
Now when I try
UPDATE ct_com_board_message
SET ftindex=to_tsvector('default',coalesce(user_login,'') ||'
'|| coalesce(title,'') ||' '|| coalesce(text,''));
I eventually get:
ERROR: word is too long(2724):
jajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaja
jajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaja
jajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaja
jajajajajajjajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaj
ajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaj
ajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaj
ajajajajajajajajajajajjajajajajajajajajajajajajajajajajajajajajajajajaja
jajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaja
jajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaja
jajajajajajajajajajajajajajajajajjajajajajajajajajajajajajajajajajajajaj
ajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaj
ajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaj
ajajajajajajajajajajajajajajajajajajajajajajjajajajajajajajajajajajajaja
jajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaja
jajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaja
jajajajajajajajajajajajajajajajajajajajajajajajajajajajjajajajajajajajaj
ajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaj
ajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaj
ajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajjajaja
jajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaja
jajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaja
jajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaja
jajajjajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaj
ajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaj
ajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaj
ajajajajajajajajjajajajajajajajajajajajajajajajajajajajajajajajajajajaja
jajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaja
jajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaja
jajajajajajajajajajajajajajjajajajajajajajajajajajajajajajajajajajajajaj
ajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaj
ajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaj
ajajajajajajajajajajajajajajajajajajajjajajajajajajajajajajajajajajajaja
jajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaja
jajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaja
jajajajajajajajajajajajajajajajajajajajajajajajajjajajajajajajajajajajaj
ajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaj
ajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaj
ajajajajajajajajajajajajajajajajajajajajajajajajajajajajajaj
This is a brightly shining example of utterly wanton user-stupidity, I
think: A 2k+ string of |:ja:|. Input like that cannot be helped, though
- if he'd been a bit more imaginative, he could have used a few dozen
Llanfairpwllgwyngyllgogerychwyrndrobwantysiliogogogoch in a row or
anything else; unfortunately there's no app that could automatically
whack a user if he's doing something stupid.
But on the other hand I cannot think of any reason why crap like that
should be indexed in the first place. Therefore I would like to see some
sort of option allowing me to still use tsearch2 but actually
automatically excluding anything exceeding MAXSTRLEN - so the UPDATE
might throw a NOTICE (if anything at all) but still get on with the
rest.
An alteration like that does however exceed my limited abilities with C
by far and I don't want to mess with something I do not fully understand
and then use that mess in a production environment. Is there a way to
get around this problem with oversized words?
Kind regards
Markus
-Ursprüngliche Nachricht-
Von: Oleg Bartunov [mailto:[EMAIL PROTECTED]
Gesendet: Freitag, 21. November 2003 15:13
An: Markus Wollny
Cc: [EMAIL PROTECTED]
Betreff: Re: AW: [GENERAL] tsearch2 column update produces word too
longerror
On Fri, 21 Nov 2003, Markus Wollny wrote:
Hello!
Von: Oleg Bartunov [mailto:[EMAIL PROTECTED]
Gesendet: Freitag, 21. November 2003 13:06
An: Markus Wollny
Cc: [EMAIL PROTECTED]
Word length is limited by 2K. What's exactly the word
[GENERAL] configure --with-java fails
Hello!
When I try to run ./configure --with-java, it complains that ant doesn't
work. However ant is installed, as is the latest Java SDK 1.4.2 from
sun, PATH and JAVA_HOME are set correctly;
helles:/ # /usr/java/apache-ant-1.5.4/bin/ant -version
Apache Ant version 1.5.4 compiled on August 12 2003
It complains about some unsupported class-version; does it require an
older Java-SDK?
Here's the bit from config.log:
configure:3157: checking for jakarta-ant
configure:3190: result: no
configure:3157: checking for ant
configure:3175: found /usr/java/apache-ant-1.5.4/bin/ant
configure:3187: result: /usr/java/apache-ant-1.5.4/bin/ant
configure:3198: checking whether /usr/java/apache-ant-1.5.4/bin/ant
works
configure:3222: /usr/java/apache-ant-1.5.4/bin/ant -buildfile
conftest.xml 12
Buildfile: conftest.xml
conftest:
[javac] Compiling 1 source file
BUILD FAILED
java.lang.UnsupportedClassVersionError: com/sun/tools/javac/Main
(Unsupported major.minor version 48.0)
at java.lang.ClassLoader.defineClass0(Native Method)
at java.lang.ClassLoader.defineClass(ClassLoader.java:488)
at
java.security.SecureClassLoader.defineClass(SecureClassLoader.java:106)
at java.net.URLClassLoader.defineClass(URLClassLoader.java:243)
at java.net.URLClassLoader.access$100(URLClassLoader.java:51)
at java.net.URLClassLoader$1.run(URLClassLoader.java:190)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:183)
at java.lang.ClassLoader.loadClass(ClassLoader.java:294)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:281)
at java.lang.ClassLoader.loadClass(ClassLoader.java:250)
at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:310)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:115)
at
org.apache.tools.ant.taskdefs.compilers.CompilerAdapterFactory.doesModer
nCompilerExist(CompilerAdapterFactory.java:173)
at
org.apache.tools.ant.taskdefs.compilers.CompilerAdapterFactory.getCompil
er(CompilerAdapterFactory.java:131)
at org.apache.tools.ant.taskdefs.Javac.compile(Javac.java:835)
at org.apache.tools.ant.taskdefs.Javac.execute(Javac.java:682)
at org.apache.tools.ant.Task.perform(Task.java:341)
at org.apache.tools.ant.Target.execute(Target.java:309)
at org.apache.tools.ant.Target.performTasks(Target.java:336)
at org.apache.tools.ant.Project.executeTarget(Project.java:1339)
at
org.apache.tools.ant.Project.executeTargets(Project.java:1255)
at org.apache.tools.ant.Main.runBuild(Main.java:609)
at org.apache.tools.ant.Main.start(Main.java:196)
at org.apache.tools.ant.Main.main(Main.java:235)
com/sun/tools/javac/Main (Unsupported major.minor version 48.0)
configure:3225: $? = 1
configure: failed java program was:
public class conftest {
int testmethod(int a, int b) {
return a + b;
}
}
configure: failed build file was:
project name=conftest default=conftest
target name=conftest
javac srcdir=. includes=conftest.java
/javac
/target
/project
configure:3241: result: no
configure:3245: error: ant does not work
## ##
## Cache variables. ##
## ##
ac_cv_path_ANT=/usr/java/apache-ant-1.5.4/bin/ant
#define PG_VERSION_STR PostgreSQL 7.4 on i686-pc-linux-gnu, compiled by
GCC gcc (GCC) 3.2
configure: exit 1
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
[GENERAL] Cron-job for checking up on pg_autovacuum
Hi!
I haven't found anything in terms of startup- and check-scripts for
pg_autovacuum yet; usually I like to have some sort of mechanism to
check if some daemon is running and restart it if it isn't.
Of course this sort of script shouldn't be too much of a bother for more
experienced users; however you might actually find this small
checkup-script more or less useful - just save it in /opt/pgsql/bin/ as
autovachk, chmod +x and follow the included instructions for adding it
to your crontab.
Regards
Markus
#!/bin/sh
#
# This is a script suitable for use in a crontab. It checks to make
sure
# your pg_autovacuum daemon is running.
#
# To check for the daemon every 5 minutes, put the following line in
your
# crontab:
#2,7,12,17,22,27,32,37,42,47,52,57 * * * *
# /opt/pgsql/bin/autovachk /dev/null 21
# change this to the directory you run the daemon from:
dir=/opt/pgsql/bin
# change this to the complete commandline you usually start the daemon
with
daemoninvoc=pg_autovacuum -D -U postgres -L /var/log/pgautovac.log
# I wouldn't touch this if I were you.
daemonpid=`eval ps ax | sed -n '/[p]g_autovacuum/p' | awk '{ print $1
}'`
## you probably don't need to change anything below here
##
cd $dir
if `kill -CHLD $daemonpid /dev/null 21`; then
# it's still going, so back out quietly
exit 0
fi
echo
echo Couldn't find the pg_autovacuum daemon running. Reloading it...
echo
./$daemoninvoc
sleep 3
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[GENERAL] tsearch2 column update produces word too long error
Hello!
I'm currently testing deployment of tsearch2 on our forum table. The
table is huge in itself - some 2GB of data without the indexes. I have
got PostgreSQL 7.4RC2 running on a test machine, installed tsearch2 to
my database, added the new column to the table and tried to update it in
the recommended fashion:
UPDATE ct_com_board_message
SET ftindex=to_tsvector('default',coalesce(user_login,'')
||' '|| coalesce(title,'') ||' '|| coalesce(text,''));
It does run for a while but at some point I get ERROR: word is too
long; I guess that this is caused by some idiot user(s) writing some
Joycean nonsense (but most probably without the literary value) or
drivelling about their holidays in
Llanfairpwllgwyngyllgogerychwyrndrobwantysiliogogogoch (Wales). Now
what could I do in order to intercept this error?
Kind regards
Markus
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] tsearch2 column update produces word too long error
Hello!
Von: Oleg Bartunov [mailto:[EMAIL PROTECTED]
Gesendet: Freitag, 21. November 2003 13:06
An: Markus Wollny
Cc: [EMAIL PROTECTED]
Word length is limited by 2K. What's exactly the word
tsearch2 complained on ?
'Llanfairpwllgwyngyllgogerychwyrndrobwantysiliogogogoch'
is fine :)
This was a silly example, I know - it is a long word, but not too long
to worry a machine. The offending word will surely be much longer, but
as a matter of fact, I cannot think of any user actually typing a 2k+
string without any spaces in between. I'm not sure on which word
tsearch2 complained, it doesn't tell and even logging did not provide me
with any more detail:
2003-11-21 14:06:44 [26497] ERROR: 42601: word is too long
LOCATION: parsetext_v2, ts_cfg.c:294
STATEMENT: UPDATE ct_com_board_message
SET
ftindex=to_tsvector('default',coalesce(user_login,'') ||' '||
coalesce(title,'') ||' '|| coalesce(text,''));
Is there some way to find the exact position?
btw, don't forget to configure properly dictionaries, so you
don't have a lot of unique words.
I won't forget that; I justed wanted to run a quick-off first test
before diving deeper into Ispell and other issues which are as yet a bit
of a mystery to me.
Kind Regards
Markus
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] More Praise for 7.4RC2
Hello!
You can use the oid2name program in the contrib directory to kinda
research which files are big under those trees and see if
it's a table or
index growth problem.
I found it a tedious operation, if you want to keep a check on growth of
your databases regularly. So I wrote a litte script which outputs a
sorted comma separated list of all objects within a database - so I can
do
./showdbsize foodb foodb.csv
and import this thing in Excel for further processing or do whatever I
like with it. There may surely be more elegant ways of getting the task
done using another language or just plain bash-scripting and you have to
have oid2name made and installed, but in terms of language php is what I
am most comfortable with - if deemed necessary, anyone might write their
own little tool in their preferred ways and languages; but it's short
enough, so I'll just post it here if anyone wants to make use of it as
it is.
Kind Regards,
Markus
Here goes:
#!/usr/bin/php -q
?php
# showdbsize for PostgreSQL #
#MWollny - 2003 #
##
/* Config Begin */
$pg_user = 'postgres';
$pg_bindir = '/opt/pgsql/bin/';
$pg_data ='/var/lib/pgsql/data/base/';
/* Config End */
##
/* DO NOT EDIT BELOW THIS LINE */
$argv=$_SERVER['argv'];
$argc=$_SERVER['argc'];
if ($argc != 2 || in_array($argv[1], array('--help', '-help',
'-h', '-?'))) {
?
This is a commandline PHP script to generate
a list of object-ids, names and filesizes of
all tables/indexes within a specified
POSTGRESQL-database.
Usage:
?php echo $argv[0]; ? database
database is the name of the database you
wish to generate the list of.
With the --list, -list, -l or --show,
-show or -s options, you can get a list of
all available databases on this server.
With the --help, -help, -h,
or -? options, you can get this help.
?php } else {
/* Function to make bytesize numbers human-readable */
function fsize($file) {
$a = array(B, KB, MB, GB, TB, PB);
$pos = 0;
$size = filesize($file);
while ($size = 1024) {
$size /= 1024;
$pos++;
}
return round($size,2). .$a[$pos];
}
/* One Ring To Find Them All */
$pg_data=$pg_data.'base/';
$db_exec=$pg_bindir.'oid2name -U '.$pg_user;
$alldb=`$db_exec`;
$i=1;
$lines = explode (\n, $alldb);
foreach($lines as $value) {
if (!strpos($value, =)===false) {
$dboid[$i] = trim(substr($value,0,strpos($value, =)-1));
$dbname[$i] = trim(substr(strstr($value,'='),2));
$i++;
}}
if (in_array($argv[1], array('--show', '-show', '-s', '-l',
'--list', '-list'))) {
echo Databases available on this server:\n;
foreach($dbname as $value) {echo $value\n;}
die();
}
/* Is argument the name of an existing database on this server?
*/
if (!in_array ($argv[1], $dbname)) {
die (Database $argv[1] not found on this server.\n);
}
/* Still alive? Okay, give me the OID of that DB! */
$i=array_search($argv[1], $dbname);
$use_oid=$dboid[$i];
$use_name=$dbname[$i];
$dbdir=$pg_data.$use_oid.'/';
chdir ($dbdir);
/* Let's see the list of files of the DB */
$handle=opendir($dbdir);
$i=0;
while ($file = readdir ($handle)) {
if ($file != . $file != ..) {
$i++;
$oid[$i]=$file;
}
}
closedir($handle);
/* Now gather data about actual names and filesizes of these
objects */
for ($j = 1; $j = $i; $j++) {
if (is_numeric($oid[$j])) {
$oid_size[$j]=filesize($oid[$j]);
$oid_hsize[$j]=fsize($oid[$j]);
$db_exec=$pg_bindir.'oid2name -U '.$pg_user.' -d
'.$use_name.' -o '.$oid[$j];
$raw_name=`$db_exec`;
$full_name[$j]=trim(substr(substr(strstr($raw_name,'='), 1),
0, -1));
# echo $oid[$j]; $full_name[$j]; $oid_size[$j];
$oid_hsize[$j] \n;
}}
/* Sort and output the list so that it can be piped to a
CSV-file */
asort ($oid_size);
reset ($oid_size);
echo OID; Name; Size (Bytes); Size (readable)\n;
foreach($oid_size as $key = $tablesize) {
echo $oid[$key];
Re: [GENERAL] pg7.3.4: pg_atoi: zero-length string
-Ursprüngliche Nachricht-
Von: Andrew Sullivan [mailto:[EMAIL PROTECTED]
Gesendet: Dienstag, 4. November 2003 12:32
An: [EMAIL PROTECTED]
Betreff: Re: [GENERAL] pg7.3.4: pg_atoi: zero-length string
On Tue, Nov 04, 2003 at 11:21:35AM +, Rob Fielding wrote:
We're currently experiencing a problem where SQL statements
are failing
when entring a '' for not not-null integer columns:
Yes. This behaviour was made more compliant in 7.3. It's in the
release notes.
[snip]
You could probably put in a rewrite rule to convert '' to NULL and
allow nulls on the column. It's the only suggestion I can think of,
short of going back to 7.2.
No, there's actually another option. Bruce posted a patch for reverting
to 7.2-behaviour (well, sort of...); I currently cannot find the
original message, but here's the relevant bit:
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania
19073
--ELM1040320327-20624-0_
Content-Transfer-Encoding: 7bit
Content-Type: text/plain
Content-Disposition: inline; filename=/bjm/diff
Index: src/backend/utils/adt/numutils.c
===
RCS file: /cvsroot/pgsql-server/src/backend/utils/adt/numutils.c,v
retrieving revision 1.54
diff -c -c -r1.54 numutils.c
*** src/backend/utils/adt/numutils.c 4 Sep 2002 20:31:28 - 1.54
--- src/backend/utils/adt/numutils.c 19 Dec 2002 17:10:56 -
***
*** 70,76
if (s == (char *) NULL)
elog(ERROR, pg_atoi: NULL pointer);
else if (*s == 0)
! elog(ERROR, pg_atoi: zero-length string);
else
l = strtol(s, badp, 10);
--- 70,80
if (s == (char *) NULL)
elog(ERROR, pg_atoi: NULL pointer);
else if (*s == 0)
! {
! /* 7.3.X workaround for broken apps, bjm 2002-12-19 */
! elog(WARNING, pg_atoi: zero-length string);
! l = (long) 0;
! }
else
l = strtol(s, badp, 10);
--ELM1040320327-20624-0_
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0
Note however, that regression tests for 7.3.x will fail, as there are
explicit tests for zero-length strings to cause an error. That need not
worry you, though. We're currently running 7.3.4 with this patch and it
works like a charm.
Regards,
Markus
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Recomended FS
Hi! -Ursprüngliche Nachricht- Von: Shridhar Daithankar [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 21. Oktober 2003 08:08 An: [EMAIL PROTECTED] Betreff: Re: [GENERAL] Recomended FS Can you compare ogbench results for the RAID and single IDE disks? It would be great if you could turn off write caching of individual drives in RAID and test it as well. One thing I can say from previous experiences is that the type of RAID does matter quite a lot. RAID5, even with a quite expensive Adaptec SCSI-hardware-controller, is not always the best solution for a database, particularly if there's a lot of INSERTs and UPDATEs going on. If you're not too dependant on raw storage size, your best bet is to use the space-consuming RAID0+1 instead; the reasoning behind this is probably that on RAID5 the controller has to calculate the parity-data for every write-access, on RAID0+1 it just mirrors and distributes the data, reducing overall load on the controller and making use of more spindles and two-channel-SCSI. We're hosting some DB-intensive websites (12M impressions/month) on two PostgreSQL-servers (one DELL Poweredge 6400, 4xPentium III [EMAIL PROTECTED], 2GB RAM, 4x18GB SCSI in RAID0+1, 1 hot-spare and one Dell Poweredge 6650, 4x Intel [EMAIL PROTECTED], 4GB RAM, 4x36 GB SCSI in RAID0+1, 1 hot-spare) and when I switched the 5-disc-RAID5-config over to a 4-disc-RAID0+1 plus one hotspare, I noticed system-load dropping by a very considerable amount. I haven't got any benchmark-figures to show off though, it's just experiences from a realworld application. Regards Markus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Recomended FS
Theory vs. real life. In Theory, RAID5 is faster because less data have to be written to disk. But it's true, many RAID5 controllers don't have enough CPU power. I think it might not be just CPU-power of the controller. For RAID0+1 you just have two disc-I/O per write-access: writing to the original set and the mirror-set. For RAID5 you have three additional disc-I/O-processes: 1. Read the original data block, 2. read the parity block (and calculate the new parity block, which is not a disk I/O), 3. write the updated data block and 4. write the updated parity block. Thus recommendations by IBM for DB/2 and several Oracle-consultants state that RAID5 is the best compromise for storage vs. transaction speed, but if your main concern is the latter, you're always best of with RAID0+1; RAID0+1 does indeed always and reproducably have better write performance that RAID0+1 and read-performance is almost always also slightly better. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
