[ADMIN] order by problem
Hello. I have two bases one in char set win1251 another in koi8-r, locale in koi8-r. The question is, what do I need to be made that right sort (by alphabet) in base with the char set win1251. Sorry for my English. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] Recursive reference of view in view defination crashes postmaster.
My mistake i faced this problem below. is it taken care in 7.4dev ? the problem has no significance in real world though. Regds mallah. tradein_clients=# SELECT * from t_a; userid | username +--- 105989 | afri4ta 105557 | koreantraders 89773 | NQChinh 89800 | yndvijaya 113306 | giriraj 89831 | devipriya 105252 | info684 89990 | hilco 89729 | wangxiuqing 104208 | zq7410 (10 rows) tradein_clients=# CREATE VIEW t_b AS SELECT * from t_a; CREATE VIEW tradein_clients=# CREATE or replace view t_b AS select * from t_b; CREATE VIEW tradein_clients=# tradein_clients=# tradein_clients=# tradein_clients=# \d t_b View "public.t_b" Column | Type | Modifiers --+---+--- userid | integer | username | character varying(30) | View definition: SELECT t_b.userid, t_b.username FROM ONLY t_b; tradein_clients=# tradein_clients=# SELECT * from t_b; 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. !# !# \q -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[ADMIN] A problem
I have a problem partly caused by stupidity, which I desperately need to
fix if at all possible. Me and a colleague have tried fixing it and
may've made it worse...
I have a reasonably big db (21GB on disk, fully vaccuumed). I upgraded
from 7.3.2 to 7.3.3, forgetting to shut it down cleanly first. It then
got killed, for reasons best forgotten, with signal 15 to th main
postmaster.
On attempting to restart it, an error with the initscript, which came in
teh source RPM, led to it partially stomping some bits of the DB,
including pg_control in global. I have the old xlogs, clogs, everything.
I removed the 'new' data subdirectory. The init script was pointed at
/db/data when it should've been just /db, I still have the old
/data/base dir, all that has been altered in there (recent mtime) is
base/1175459/pg_internal.init
Attemtping to use the pg_resetxlog command (having backed up pg_control
and all the xlogs), we managed to start the server again - however, on
connecting to it with psql, it still had the right DB in teh cluster
('stats') but it claimed this db was completely empty.
I have kept everything I've tried reversible as far as my and my
colleagues knowledge allowed.
I hope someone can suggest something - I'm desperate! It'll take weeks
to re-input that data automatically.
Thanks
--
Sam Barnett-Cormack
Software Developer | Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] Recursive reference of view in view defination crashes
On Fri, 27 Jun 2003, Rajesh Kumar Mallah wrote: > My mistake i faced this problem below. > is it taken care in 7.4dev ? In 7.4 I get "ERROR: Infinite recursion detected in rules for relation t_b" > tradein_clients=# CREATE VIEW t_b AS SELECT * from t_a; > CREATE VIEW > tradein_clients=# CREATE or replace view t_b AS select * from t_b; > CREATE VIEW ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [ADMIN] A problem
Sam Barnett-Cormack <[EMAIL PROTECTED]> writes: > [ accidentally clobbered pg_control ] It sounds like the main problem is too small a value in the XID (transaction ID) counter, so that all your data tuples appear to be in the future. pg_resetxlog has an option to force an initial XID but you need to use it. You can guess a suitable XID by looking at the names of the old clog files --- you want something that will be past the end of the newest clog file, keeping in mind that there are 1M (1024^2) XIDs per clog file and that the clog file names are in hex. (I am not sure, but you might also need to provide a zero-filled clog file spanning the point you start XID at.) You will also need to be sure that the WAL file position is greater than the end of the old WAL --- again, look at the existing WAL files to see the largest number, and pick something bigger. BTW, make real sure that pg_control is set up with the same locale values as you were using before, or you'll be in a world of hurt with corrupt indexes. Use pg_controldata to look at what you have put into pg_control. (If you're not sure what was there before, get an old copy of pg_control off your last backup tape ... you do have a filesystem backup someplace, I trust ...) > I still have the old > /data/base dir, all that has been altered in there (recent mtime) is > base/1175459/pg_internal.init You can and probably should blow away pg_internal.init --- it's just a cache file and can be rebuilt. I'd not trust it at this point. regards, tom lane ---(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: [ADMIN] A problem
Late as it is, I'll try this monday. I hope it works, other suggestions still welcome of course, thanks very much. On Fri, 27 Jun 2003, Tom Lane wrote: > Sam Barnett-Cormack <[EMAIL PROTECTED]> writes: > > [ accidentally clobbered pg_control ] > > It sounds like the main problem is too small a value in the XID > (transaction ID) counter, so that all your data tuples appear to be in > the future. pg_resetxlog has an option to force an initial XID but > you need to use it. You can guess a suitable XID by looking at the > names of the old clog files --- you want something that will be past > the end of the newest clog file, keeping in mind that there are 1M > (1024^2) XIDs per clog file and that the clog file names are in hex. > (I am not sure, but you might also need to provide a zero-filled clog > file spanning the point you start XID at.) > > You will also need to be sure that the WAL file position > is greater than the end of the old WAL --- again, look at the existing > WAL files to see the largest number, and pick something bigger. > > BTW, make real sure that pg_control is set up with the same locale > values as you were using before, or you'll be in a world of hurt > with corrupt indexes. Use pg_controldata to look at what you have put > into pg_control. (If you're not sure what was there before, get an > old copy of pg_control off your last backup tape ... you do have a > filesystem backup someplace, I trust ...) > > > I still have the old > > /data/base dir, all that has been altered in there (recent mtime) is > > base/1175459/pg_internal.init > > You can and probably should blow away pg_internal.init --- it's just a > cache file and can be rebuilt. I'd not trust it at this point. > > regards, tom lane > -- Sam Barnett-Cormack Software Developer | Student of Physics & Maths UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] How do I select the last Id in a column???
What could cause a table to act serialized when read committed transactions are set in the configuration? That is something I am running into, which provoked my [incorrect] example. From: "Bruno Wolff III" <[EMAIL PROTECTED]> Sent: Thursday, June 26, 2003 4:30 PM > On Thu, Jun 26, 2003 at 14:00:33 -0700, > Josh Goldberg <[EMAIL PROTECTED]> wrote: > > to get the last record inserted just > > select * from row order by autoincId desc limit 1; > > > > if you have several clients doing this you might want to put your insert and > > the select inside a transaction. > > That won't help. Your suggestion will only work in serializable transactions. > > In read committed mode the select might see a value for autoincId from > a transaction that committed between the insert and the select. > > ---(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
[ADMIN] Japanese Character Support
Hi Folks, I'd like to be able to support both Japanese and English character sets in the same database if possible. First off, is this possible, or are there some restrictions (such as specific tables need to have specific character sets only). I think this is a case of Multi-Byte Support. If this is the case, how do I determine if my web-host's database was compiled with support for that, and if it wasn't is there any way around it, besides reinstalling the database? Thanks, Tom -- Tom Haddon IT Director The Better Health Foundation 414 Thirteenth Street, Suite 450 Oakland, CA 94612 (510) 444-5096 (510) 444-5085 FAX www.betterhealthfoundation.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Japanese Character Support
> I'd like to be able to support both Japanese and English character > sets in the same database if possible. First off, is this possible, > or are there some restrictions (such as specific tables need to have > specific character sets only). > > I think this is a case of Multi-Byte Support. If this is the case, how > do I determine if my web-host's database was compiled with support for > that, and if it wasn't is there any way around it, besides > reinstalling the database? what you need is Unicode-support in your database and application (often referred to as UTF-8). Have a look at http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=multibyte.ht ml Postgresql can automagically convert between different encodings, this way your application does not have to be modified too heavily. Regards, Bjoern ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Postgres RoadMap
On Fri, Jun 27, 2003 at 02:18:52AM +0200, Mendola Gaetano wrote: > I heard only about the point in time recovery in version 7.4, that's all, > what about, for example, a "multi DB/HOST select" or the nested transactions ? multiple-host-queries ?! wow - what did you have smoked ? ;-) cu -- - Enrico Weigelt== metux ITS Webhosting ab 5 EUR/Monat. UUCP, rawIP und vieles mehr. phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] cellphone: +49 174 7066481 smsgate: [EMAIL PROTECTED] - Diese Mail wurde mit UUCP versandt. http://www.metux.de/uucp/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] SQLRelay?
I just saw someone mention SQLRelay on the postfix-users mailing list, and it seems like a really cool project. I haven't heard of it though (which is kind of surprising, since I've been looking for something like this for a while) so I wanted to see if anyone here has any experience with it. http://sqlrelay.sourceforge.net/ -- /* Michael A. Nachbaur <[EMAIL PROTECTED]> * http://nachbaur.com/pgpkey.asc */ ...[Arthur] leapt to his feet like an author hearing the phone ring... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Postgres RoadMap
On Fri, 27 Jun 2003 [EMAIL PROTECTED] wrote: > On Fri, Jun 27, 2003 at 02:18:52AM +0200, Mendola Gaetano wrote: > > > > I heard only about the point in time recovery in version 7.4, that's all, > > what about, for example, a "multi DB/HOST select" or the nested transactions ? > multiple-host-queries ?! > > wow - what did you have smoked ? ;-) I think the two phase commit stuff is likely what he heard of. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] How do I select the last Id in a column???
All, i got the answer i was needing. I used the following: select max(id) from table it works great!! :) --- Josh Goldberg <[EMAIL PROTECTED]> wrote: > What could cause a table to act serialized when read > committed transactions > are set in the configuration? That is something I > am running into, which > provoked my [incorrect] example. > > From: "Bruno Wolff III" <[EMAIL PROTECTED]> > Sent: Thursday, June 26, 2003 4:30 PM > > On Thu, Jun 26, 2003 at 14:00:33 -0700, > > Josh Goldberg <[EMAIL PROTECTED]> wrote: > > > to get the last record inserted just > > > select * from row order by autoincId desc limit > 1; > > > > > > if you have several clients doing this you might > want to put your insert > and > > > the select inside a transaction. > > > > That won't help. Your suggestion will only work in > serializable > transactions. > > > > In read committed mode the select might see a > value for autoincId from > > a transaction that committed between the insert > and the select. > > > > ---(end of > broadcast)--- > > TIP 8: explain analyze is your friend > > > __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] How do I select the last Id in a column???
Note that that has race conditions, i.e. two clients operating at the same time could get the same max(id). The setval currval and nextval functions exist to prevent race conditions, and they work well. Rolling your own is a recipe for disaster. On Fri, 27 Jun 2003, Michael Kovalcik wrote: > All, > > i got the answer i was needing. I used the following: > > select max(id) from table > > it works great!! :) > > --- Josh Goldberg <[EMAIL PROTECTED]> wrote: > > What could cause a table to act serialized when read > > committed transactions > > are set in the configuration? That is something I > > am running into, which > > provoked my [incorrect] example. > > > > From: "Bruno Wolff III" <[EMAIL PROTECTED]> > > Sent: Thursday, June 26, 2003 4:30 PM > > > On Thu, Jun 26, 2003 at 14:00:33 -0700, > > > Josh Goldberg <[EMAIL PROTECTED]> wrote: > > > > to get the last record inserted just > > > > select * from row order by autoincId desc limit > > 1; > > > > > > > > if you have several clients doing this you might > > want to put your insert > > and > > > > the select inside a transaction. > > > > > > That won't help. Your suggestion will only work in > > serializable > > transactions. > > > > > > In read committed mode the select might see a > > value for autoincId from > > > a transaction that committed between the insert > > and the select. > > > > > > ---(end of > > broadcast)--- > > > TIP 8: explain analyze is your friend > > > > > > > > __ > Do you Yahoo!? > SBC Yahoo! DSL - Now only $29.95 per month! > http://sbc.yahoo.com > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] pg_dump: ERROR: cannot read block 1055 of summary: Input/output error
Hi all, using pg_dump I get the following error message: pg_dump stats > stats.sql pg_dump: ERROR: cannot read block 1055 of summary: Input/output error pg_dump: lost synchronization with server, resetting connection pg_dump: SQL command to dump the contents of table "summary" failed: PQendcopy() failed. pg_dump: Error message from server: pg_dump: The command was: COPY public.summary (id, timestamp, user, keyfigure, total, absolutedifference, relativedifference) TO stdout; calls from psql give me similar trouble: # select count(*) from summary; ERROR: cannot read block 1055 of summary: Interrupted system call some more info: - we are using postgresql 7.3.2 - before the problems, the table had approx. 200.000 records without any problems - a regular import is (was) feeding about 500 records a day into the table. There's a good backup from yesterday, but still I am very concerned. Any ideas on how to fix the problem ? Any reasons why it does occur ? thanks -- +++ GMX - Mail, Messaging & more http://www.gmx.net +++ Bitte lächeln! Fotogalerie online mit GMX ohne eigene Homepage! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] Latin character set
how do i get support for latin character on my psql console :-) Sidar Lopez Cruz- Cero Riesgo, S.A.
Re: [ADMIN] How do I select the last Id in a column???
On Fri, Jun 27, 2003 at 10:06:41 -0700, Josh Goldberg <[EMAIL PROTECTED]> wrote: > What could cause a table to act serialized when read committed transactions > are set in the configuration? That is something I am running into, which > provoked my [incorrect] example. Getting lucky. Did you actually run two transactions in parallel and stop between the select and insert so that you could do a select, insert and commit in the other transaction? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[ADMIN] replication/redundancy
some searches on this have produced mixed results... do we have a stable means to replicate transactions between two physical servers, preferrably in master - master configuration where updates/inserts can be done on either database and the results replicated to the other master. Dave ---(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: [ADMIN] Failed to initialize lc_messages to ''
>> I just tried a fresh install of pg 7.3.1 on a SuSE 8.1 box. I keep getting >> messages like >> Failed to initialize lc_messages to '' >> during initdb. I already tried --locale=C but no help. Anyone any >> ideas? >Try initdb with -d option and show us the (last few lines of) output. >Also, what is your LANG/LC_ALL setting? Does the -d output change >when you use --locale=C? > > regards, tom lane Hi, I've had this same problem trying to install 7.3.3 on Suse 8.0. Here is the abridged logfile, as requested. THe -d output does not change when using --locale=C. LANG is set to en_US and LC_ALL is set to C. [Begin logfile] initdb variables: PGDATA=/usr/local/pgsql/data datadir=/usr/local/pgsql/share PGPATH=/usr/local/pgsql/bin ENCODING= ENCODINGID=0 POSTGRES_SUPERUSERNAME=postgres POSTGRES_BKI=/usr/local/pgsql/share/postgres.bki POSTGRES_DESCR=/usr/local/pgsql/share/postgres.description POSTGRESQL_CONF_SAMPLE=/usr/local/pgsql/share/postgresql.conf.sample PG_HBA_SAMPLE=/usr/local/pgsql/share/pg_hba.conf.sample PG_IDENT_SAMPLE=/usr/local/pgsql/share/pg_ident.conf.sample Failed to initialize lc_messages to '' DEBUG: invoking IpcMemoryCreate(size=983040) LOG: database system was shut down at 2003-06-27 19:38:13 EDT LOG: checkpoint record is at 0/10 LOG: redo record is at 0/10; undo record is at 0/10; shutdown TRUE LOG: next transaction id: 3; next oid: 16384 LOG: database system is ready DEBUG: start transaction DEBUG: creating bootstrap relation pg_proc... ... [168000+ lines of insertions, etc] ... DEBUG: start transaction DEBUG: commit transaction DEBUG: proc_exit(0) DEBUG: shmem_exit(0) DEBUG: exit(0) Failed to initialize lc_messages to '' LOG: database system was shut down at 2003-06-27 19:38:17 EDT LOG: checkpoint record is at 0/74 LOG: redo record is at 0/74; undo record is at 0/0; shutdown TRUE LOG: next transaction id: 3; next oid: 16652 LOG: database system is ready LOG: shutting down LOG: database system is shut down Failed to initialize lc_messages to '' LOG: database system was shut down at 2003-06-27 19:38:17 EDT LOG: checkpoint record is at 0/8F24 LOG: redo record is at 0/8F24; undo record is at 0/0; shutdown TRUE LOG: next transaction id: 11; next oid: 16654 LOG: database system is ready LOG: shutting down LOG: database system is shut down Failed to initialize lc_messages to '' LOG: database system was shut down at 2003-06-27 19:38:17 EDT LOG: checkpoint record is at 0/44800 LOG: redo record is at 0/44800; undo record is at 0/0; shutdown TRUE LOG: next transaction id: 21; next oid: 16681 LOG: database system is ready LOG: shutting down LOG: database system is shut down Failed to initialize lc_messages to '' LOG: database system was shut down at 2003-06-27 19:38:17 EDT LOG: checkpoint record is at 0/F671C LOG: redo record is at 0/F671C; undo record is at 0/0; shutdown TRUE LOG: next transaction id: 43; next oid: 16681 LOG: database system is ready LOG: shutting down LOG: database system is shut down Failed to initialize lc_messages to '' LOG: database system was shut down at 2003-06-27 19:38:18 EDT LOG: checkpoint record is at 0/1515D4 LOG: redo record is at 0/1515D4; undo record is at 0/0; shutdown TRUE LOG: next transaction id: 72; next oid: 16765 LOG: database system is ready LOG: shutting down LOG: database system is shut down Failed to initialize lc_messages to '' LOG: database system was shut down at 2003-06-27 19:38:18 EDT LOG: checkpoint record is at 0/1BA9D8 LOG: redo record is at 0/1BA9D8; undo record is at 0/0; shutdown TRUE LOG: next transaction id: 77; next oid: 16771 LOG: database system is ready LOG: shutting down LOG: database system is shut down Failed to initialize lc_messages to '' LOG: database system was shut down at 2003-06-27 19:38:18 EDT LOG: checkpoint record is at 0/213008 LOG: redo record is at 0/213008; undo record is at 0/0; shutdown TRUE LOG: next transaction id: 420; next oid: 16975 LOG: database system is ready LOG: shutting down LOG: database system is shut down Failed to initialize lc_messages to '' LOG: database system was shut down at 2003-06-27 19:38:18 EDT LOG: checkpoint record is at 0/490120 LOG: redo record is at 0/490120; undo record is at 0/0; shutdown TRUE LOG: next transaction id: 425; next oid: 16975 LOG: database system is ready LOG: shutting down LOG: database system is shut down Failed to initialize lc_messages to '' LOG: database system was shut down at 2003-06-27 19:38:19 EDT LOG: checkpoint record is at 0/7FAD6C LOG: redo record is at 0/7FAD6C; undo record is at 0/0; shutdown TRUE LOG: next transaction id: 466; next oid: 16975 LOG: database system is ready LOG: shutting down LOG: database system is shut down [End logfile] Thanks for any assistance. Shawn __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com ---(end of broadcast)---
Re: [ADMIN] pg_dump: ERROR: cannot read block 1055 of summary: Input/output error
Dimitri Nagiev <[EMAIL PROTECTED]> writes: > using pg_dump I get the following error message: > pg_dump: ERROR: cannot read block 1055 of summary: Input/output error > calls from psql give me similar trouble: > ERROR: cannot read block 1055 of summary: Interrupted system call > There's a good backup from yesterday, but still I am very concerned. As you should be. It sounds like your disk has developed a bad sector. I'd recommend replacing the disk --- where one sector has gone bad, others may soon follow. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
