[ADMIN] Lock timeout detection in postgres 7.3.1
Hi I have recently migrated my database from MS Sql Server to postgresql 7.3.1. In MS SQL SERVER, it is very easy to set the lock time equals to zero on ROW LEVEL. So that if some other user try to access the same data, he/she will get the error immediately. I have tried to run the same code through VB 6.0 (windows) using pgsql as database on RED HAT LINUX 8.0, the only problem i am facing is when ever a user try to access a pre LOCKED ROW, the program goes into halt until the first user executes ROLLBACK or COMMIT. Is there any way to set the LOCK TIME equals to ZERO in postgresql 7.3.1? __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com ---(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
[ADMIN] Cascade on update statement, not on table definition
Hi, I saw I can define a table that specifies a ON UPDATE CASCADE for a Foreign Key. I have a lot of tables with FK´s, and a lot of others with others FK\'s for the firts ones, and so on, and I haven´t defined the ON CASCADE clause on the FK´s tables. Now I need to update a primary key on the \"mother\" table. Is there a way to execute a stmt like \'update tbl set id_field=NEW_ID where id_field=OLD_ID on CASCADE\', or something like that? Or do I need to redefine my tables, or the constraints? Tks Felipe Nascimento ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] Duplicate indexes found in the postgres Database
Tom,
User was able to recreate the problem, but this time only on table got
duplicated 'gui_config'. So here is the info you wanted:
select ctid,xmin,xmax,oid,* from pg_class where relname = 'gui_config';
ctid | xmin | xmax | oid | relname | reltype | relowner | relam |
relfilenode | relpages | reltuples | reltoastrelid | reltoastidxid |
relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers |
relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules |
relhassubclass |relacl
+--+--+---++-+--+---+---
--+--+---+---+---+--
---+-+-+--+---+-+--+
--+-+++-+---
-+---
(2,54) | 176 | 191 | 16560 | gui_config | 16561 | 101 | 0 |
16560 | 10 | 1000 | 0 | 0 | t |
f | r |2 | 0 | 0 |0 |
0 | 0 | t | t | f | f |
{=,aesop_gui=arwdRxt}
(2,56) | 191 | 206 | 16560 | gui_config | 16561 | 101 | 0 |
16560 | 10 | 1000 | 0 | 0 | t |
f | r |2 | 0 | 0 |0 |
0 | 0 | t | t | f | f |
{=,aesop_gui=arwdRxt}
(2 rows)
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]]
Sent: Saturday, February 01, 2003 12:42 PM
To: Chris White
Cc: [EMAIL PROTECTED]
Subject: Re: [ADMIN] Duplicate indexes found in the postgres Database
"Chris White" <[EMAIL PROTECTED]> writes:
> How do I look at the catalogs and which ones do I need to look at?
Now that I look at it, pg_tables is not a join in 7.2, but just a
straight select from pg_class. So the problem is definitely in
pg_class. Let's see the results of
select ctid,xmin,xmax,oid,* from pg_class where relname = 'vm_message';
and similarly for the other duplicates.
> No we haven't seen any system crashes, but people have reported that the
> tables that are duplicated are possibly missing some data.
Hm. Trying to avoid theorizing in advance of the data...
regards, tom lane
---(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
[ADMIN] Problems upgrading from 7.1.3
Hi all, I have a database cluster running on PostgreSQL 7.1.3 compiled from source, on Debian Linux. I want to upgrade the cluster to PostgreSQL 7.3.x. In order to get the data over, I ran: PGUSER=postgres /usr/local/pgsql/bin/pg_dumpall > survey1.sql on the production machine running 7.1.3. I then took that file over to test machine running PostgreSQL 7.3.1, and ran: PGUSER=postgres /usr/local/pgsql-7.3.1/bin/psql -d template1 -f survey1.sql I got lots of errors about "Invalid command \N" in the COPY xxx FROM statements, and most of the tables were completely empty. I then tried: PGUSER=postgres /usr/local/pgsql-7.3.1/bin/psql -d template1 < survey1.sql and got the same results. Next, I tried upgrading to 7.2.4 as an intermediate step, but 7.2.4 gave the same "Invalid command \N" errors. So then I then tried the pg_dump from 7.3.1 and 7.2.4 on the 7.1.3 database, and then restoring into the respective server. This still gave the same errors. I also tried editing the dump file, adding explicit "WITH NULL AS '\N'" clauses to each COPY statement. Same result still. Does anyone have ideas on what's going on here? Am I doing something wrong? Is there a step I'm missing? A google search revealed somebody with a similar problem upgrading from 6.5.x a year ago, but there didn't seem to be any resolution to it. TIA, --- Geoffrey Wossum Software Engineer Long Range Systems - http://www.pager.net ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] Problems upgrading from 7.1.3
> I got lots of errors about "Invalid command \N" in the COPY xxx FROM > statements, and most of the tables were completely empty. Try dumping with -d or -D: -d, --insertsdump data as INSERT, rather than COPY, commands -D, --column-inserts dump data as INSERT commands with column names Regards, Bjoern ---(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] Problems upgrading from 7.1.3
Geoffrey Wossum <[EMAIL PROTECTED]> writes: > I then took that file over to test machine running PostgreSQL 7.3.1, Er, how did you copy the file over exactly? This smells to me like it could be a newline-formatting problem (COPY is pretty picky about its newlines). If you passed the file through anything that might choose to convert Unix newlines to DOS newlines, you would have that problem. BTW, the very best way to do this sort of upgrade is to use the newer version's pg_dump (ie, 7.3.1's pg_dump) to extract data from the older server. The newer pg_dump will know how to apply any SQL compatibility adjustments that might be appropriate. I do not think that will make any difference to a COPY-data-format problem, but it could help for other things. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Fwd: Re: [ADMIN] Problems upgrading from 7.1.3
-- Forwarded Message -- Subject: Re: [ADMIN] Problems upgrading from 7.1.3 Date: Wednesday 05 February 2003 01:39 pm From: Geoffrey Wossum <[EMAIL PROTECTED]> To: "Bjoern Metzdorf" <[EMAIL PROTECTED]> On Wednesday 05 February 2003 11:36 am, you wrote: > > I got lots of errors about "Invalid command \N" in the COPY xxx FROM > > statements, and most of the tables were completely empty. > > Try dumping with -d or -D: Thanks! That seems to work around the problem. It's a lot slower than the COPY's (especially for half-million row tables), but it at least it works. Is this some bug I've run into in the newer versions of PostgreSQL that needs to be reported, or just me not reading the manual close enough? Thanks again, --- Geoffrey Wossum Software Engineer Long Range Systems - http://www.pager.net --- ---(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] Problems upgrading from 7.1.3
On Wednesday 05 February 2003 01:12 pm, Tom Lane wrote: > Er, how did you copy the file over exactly? scp > This smells to me like it could be a newline-formatting problem (COPY is > pretty picky about its newlines). If you passed the file through > anything that might choose to convert Unix newlines to DOS newlines, > you would have that problem. No, looking at the file on the machine I dumped it on, there really are '\N's in it. And adding explicit "WITH NULL AS '\N'" clauses to the COPY's didn't help any either. Bjoern suggested I add "-d" or "-D" to the pg_dump/pg_dumpall command line, which worked flawlessly, although recreating the database was a bit slow. > BTW, the very best way to do this sort of upgrade is to use the newer > version's pg_dump (ie, 7.3.1's pg_dump) to extract data from the older > server. The newer pg_dump will know how to apply any SQL compatibility > adjustments that might be appropriate. I do not think that will make > any difference to a COPY-data-format problem, but it could help for > other things. I originally used 7.3.1's pg_dumpall to dump the 7.1.3's database as recommended in the admin manual, but had the same result. Maybe I forgot to mention that in my original email. There were a lot of combinations I had to mention ^_^ Thanks, --- Geoffrey Wossum Software Engineer Long Range Systems - http://www.pager.net ---(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
[ADMIN]
Dear list: I need to move all databases from one fiscal server to other, but, the main problem I have is: I can't dump databases, for internal problems from hdd. My question is, so, how can rescue all databases in 'cp command' way in posgresql version 7.0 ? Needing to know: I copy /usr/local/pgsql/data/base. Any help will be great. regards. MM. -- Matias Monteverde Sistemas SSI - ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[ADMIN] Vacuuming DVs with few/no updates?
Hi all, Just wondering about how useful the vacuumdb functionality is for a database that pretty much only ever gets inserts/selects. I've got a database that is intended for next-to-no deletion of records, which is where I see the vacuum facility mostly intended for. I.e., while there'll be the occasional record edits, these edits will primarily be of the form of adjusting integer records (and therefore shouldn't adjust any space requirements for a row). I'm thinking that with this database I'll configure vacuumdb to only run once a month or so. But before I do that I want to make sure I'm not missing some other important functionality that it does... Cheers, -Preston de Guise. -- Oops. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] pg_restore problem with 7.3.1
Tom Lane wrote: I tried to replicate this, and could not: the restore went into the expected database. (But I did notice that pg_restore needed to be explicitly told -Ft, which seems less than bright of it.) Are you sure those are the exact commands you issued? Were you using the 7.3 versions of pg_dump and pg_restore? I just recreated the problem with a slightly different command. The problem was caused by using a list file to skip loading some function definitions that were causing problems. The command that loaded everything into template1 was: pg_restore -C -d template1 -L mpmx.lst -Ft mpmx.tar The list file was created without doing a It looks like the CREATE DATABASE call isn't done unless the -C flag is included both when the list file is created and when doing the restore. This works correctly: pg_restore -C -l -Ft mpmx.tar > mpmx.lst pg_restore -C -d mpmx -L mpmx.lst -Ft mpmx.tar It would be good if pg_restore warned if the -C flag was included on the list file generation or the - Ian ---(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: [ADMIN] Problems upgrading from 7.1.3
Geoffrey Wossum <[EMAIL PROTECTED]> writes: > On Wednesday 05 February 2003 01:12 pm, Tom Lane wrote: >> This smells to me like it could be a newline-formatting problem (COPY is >> pretty picky about its newlines). > [ Nope ] Drat, another perfectly good theory down the drain. > Bjoern suggested I add "-d" or "-D" to the pg_dump/pg_dumpall command line, > which worked flawlessly, although recreating the database was a bit slow. Hm. The -d business used to be a standard workaround for some known limitations of COPY, but I was of the opinion that we'd fixed the last of them as of 7.3. If you found -d necessary even with 7.3.x pg_dump then I'd like to see the details. The table definition (as dumped by pg_dump) and the first few lines of COPY data from the dump would be useful info to start with... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Vacuuming DVs with few/no updates?
Preston <[EMAIL PROTECTED]> writes: > Just wondering about how useful the vacuumdb functionality is for a database > that pretty much only ever gets inserts/selects. > I've got a database that is intended for next-to-no deletion of > records, which is where I see the vacuum facility mostly intended > for. I.e., while there'll be the occasional record edits, these edits > will primarily be of the form of adjusting integer records (and > therefore shouldn't adjust any space requirements for a row). You have an important misconception lurking in there. In Postgres, an UPDATE is equivalent to INSERT (of the new row version) followed by DELETE (of the old row version). Therefore, it creates dead rows that need to be reclaimed by VACUUM, just as much as DELETE would do. But yeah, if you have very very few updates or deletes then you don't need to vacuum very often. (You might possibly need to ANALYZE more often than you VACUUM, if statistics like column min/max values are changing significantly due to the insertion traffic.) > I'm thinking that with this database I'll configure vacuumdb to only > run once a month or so. But before I do that I want to make sure I'm > not missing some other important functionality that it does... You should read the discussion of transaction wraparound in the Admin Guide's chapter about routine maintenance (specifically VACUUM ;-)). Once-a-month vacuum is fine if your total transaction load doesn't exceed 1 billion per month ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] Vacuuming DVs with few/no updates?
Quoting Tom Lane <[EMAIL PROTECTED]>: > Preston <[EMAIL PROTECTED]> writes: > > Just wondering about how useful the vacuumdb functionality is for a > > database > > that pretty much only ever gets inserts/selects. > > > I've got a database that is intended for next-to-no deletion of > > records, which is where I see the vacuum facility mostly intended > > for. I.e., while there'll be the occasional record edits, these edits > > will primarily be of the form of adjusting integer records (and > > therefore shouldn't adjust any space requirements for a row). > > You have an important misconception lurking in there. In Postgres, > an UPDATE is equivalent to INSERT (of the new row version) followed by > DELETE (of the old row version). Therefore, it creates dead rows that > need to be reclaimed by VACUUM, just as much as DELETE would do. Ah OK - thanks for explaining that to me. It changes my outlook quite a bit. However, the inserts will still outnumber the updates on a scale of about 30:1. > But yeah, if you have very very few updates or deletes then you don't > need to vacuum very often. (You might possibly need to ANALYZE more > often than you VACUUM, if statistics like column min/max values are > changing significantly due to the insertion traffic.) OK, will look into that side of it. > > I'm thinking that with this database I'll configure vacuumdb to only > > run once a month or so. But before I do that I want to make sure I'm > > not missing some other important functionality that it does... > > You should read the discussion of transaction wraparound in the Admin > Guide's chapter about routine maintenance (specifically VACUUM ;-)). > Once-a-month vacuum is fine if your total transaction load doesn't > exceed 1 billion per month ... Thanks - will find and read that... Once again caught by the "too much to do not enough time" bug. Does that transaction load include selects? Due to automated web-based refreshing of views of tables I'd expect the selects to number around 2,000 to 10,000 per day... I know that's still not anywhere near a billion but it's to the point of a more interesting amount if transaction load includes selects... Cheers, -Preston. -- Oops. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Vacuuming DVs with few/no updates?
Preston <[EMAIL PROTECTED]> writes: > Does that transaction load include selects? Yes. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] Using advance update
Dear Milist, Somebody please give me more explanation about using "Update" syntax with cursor. I've seen this on manual of postgre(sql-update.html). Here it is the example; UPDATE table SET column = expression [, ...] WHERE CURRENT OF cursor But there's no further clearly explanation or the sample of usages. Somebody please give me ones. Thank's and GOD Bless You. Yudha Setiawan.VoIP No. : 01119311223344
[ADMIN] Problem with VACUUM
Hello everybody! We have a problem with VACUUM FULL and the our PostgresSQL is very slow. On the try run the vacuum command, its returns: NOTICE: Child itemid in update-chain marked as unused - can't continue repair_frag ERROR: No one parent tuple was found vacuumdb: vacuum nube failed I think it looking for a table or an atributte of inherits but we have already droped it. The PostgresSQL version is: 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96 Thanks Marcelo. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] Problem with VACUUM
Marcelo Pereira Tada <[EMAIL PROTECTED]> writes: >> NOTICE: Child itemid in update-chain marked as unused - can't continue repair_frag >> ERROR: No one parent tuple was found Try updating to 7.2.4; that should fix this. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
