[HACKERS] Question whether this is a known problem in 7.1.2
This problem was discovered in 7.1.2. Was wondering whether this is a known problem or not; we plan to test this on the latest postgres sometime later. We have a large table, lets call it A, millions of rows. And in the table is a field called time, which is TIMESTAMP type. We have an index on it. Oftentimes we like to get the latest row inserted by time on a given constraint. So we do a: SELECT * FROM A WHERE someconstraint = somerandomnumber ORDER BY time desc limit 1; Postgres intellegently uses the index to scan through the table from the end forward. If there are no items that fit the constraint, the query will take a long time (cause it has to scan the whole table). If there are items (plural important here, read below) that fit the constraint, the database finds the first item, and returns it right away (fairly quickly if the item is near the end). However, if there is only ONE item, postgres still scans the whole database. Not sure why. We also find out that if: There are 2 items that match the criteria, and you do a LIMIT 2, it scans the whole table as well. Limit 1 returns quickly. Basically it seems like postgres is looking for one more item than it needs to. -rchit ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Question whether this is a known problem in 7.1.2
By the way, a colleague just reproduced this problem on a 7.2.1 postgres. -Original Message- From: Rachit Siamwalla [mailto:[EMAIL PROTECTED]] Sent: Friday, June 07, 2002 4:27 PM To: pgsql-hackers; Paul Menage Subject: [HACKERS] Question whether this is a known problem in 7.1.2 This problem was discovered in 7.1.2. Was wondering whether this is a known problem or not; we plan to test this on the latest postgres sometime later. We have a large table, lets call it A, millions of rows. And in the table is a field called time, which is TIMESTAMP type. We have an index on it. Oftentimes we like to get the latest row inserted by time on a given constraint. So we do a: SELECT * FROM A WHERE someconstraint = somerandomnumber ORDER BY time desc limit 1; Postgres intellegently uses the index to scan through the table from the end forward. If there are no items that fit the constraint, the query will take a long time (cause it has to scan the whole table). If there are items (plural important here, read below) that fit the constraint, the database finds the first item, and returns it right away (fairly quickly if the item is near the end). However, if there is only ONE item, postgres still scans the whole database. Not sure why. We also find out that if: There are 2 items that match the criteria, and you do a LIMIT 2, it scans the whole table as well. Limit 1 returns quickly. Basically it seems like postgres is looking for one more item than it needs to. -rchit ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Deadlock? idle in transaction
i've had similar problems before. Looks like some thing is in a transaction, blocked on something else. Then vacuum comes in, locks half the tables, and then gets stuck on a table that the transaction has modified. Now most of your other transactions will block forever. Then the connection limit for postgres will be hit. Then you can't connect to postgres at all. Basically, its a death spiral starting from something in a transaction blocking forever on an external command. Nothing postgres itself can do about. Of course, this is just my conjecture based on the info provided. -rchit -Original Message- From: Michael Meskes [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 11, 2001 2:29 AM To: PostgreSQL Hacker Subject: [HACKERS] Deadlock? idle in transaction A customer's machine hangs from time to time. All we could find so far is that postgres seems to be in state idle in transaction: postgres 19317 0.0 0.3 8168 392 ?SOct05 0:00 /usr/lib/postgresql/bin/postmaster -D /var/lib/postgres/data postgres 19983 0.0 0.8 8932 1020 ?SOct05 0:01 postgres: postgres rabatt 192.168.50.222 idle in transaction postgres 21005 0.0 0.0 34844 ?SOct06 0:00 /usr/lib/postgresql/bin/psql -t -q -d template1 postgres 21014 0.0 0.7 8892 952 ?SOct06 0:01 postgres: postgres rabatt [local] VACUUM waiting postgres 21833 0.0 0.4 3844 572 ?SOct06 0:00 /usr/lib/postgresql/bin/pg_dump rabatt postgres 21841 0.0 1.2 9716 1564 ?SOct06 0:00 postgres: postgres rabatt [local] COPY waiting postgres 22135 0.0 0.9 8856 1224 ?SOct06 0:00 postgres: postgres rabatt 192.168.50.223 idle in transaction waiting I'm not sure what's happening here and I have no remote access to the machine myself. Any idea what could be the reason for this? There may be some client processes running at the time the dump and the vacuum commands are issued that have an open transaction doing nothing. That is the just issued a BEGIN command. Thinking about it run some inserts at the very same time, although that's not likely. Any hints are appreciated. Thanks in advance. Michael -- Michael Meskes [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] FOREIGN KEY taking write locks on parent.
I sent a message a while back on this list on why an insert onto a table A which has a foreign key constraint to table B obtains a write (exclusive) lock on that row on table B (basically does a select for update). The answer was there is no SQL construct to obtain read (shared) locks on a particular row, therefore it took a write lock. I was just wondering, isn't the fact that FOREIGN KEY takes a write lock on its parent a bug? I was just wondering whether this is being worked on, and if anyone has any ideas where to start in case I want to work on it, or can I create my own function / constraint which will just emulate a shared lock behavior for a FOREIGN KEY constrant. This is making it tough to sanely handle concurrent long-running transactions, even if I use the INITIALLY DEFERRED for the foreign key constrant. Thanx a lot, and thanx for this wonderful DB. -rchit ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] plperl rpm package
just curious, is there any reason why a plperl RPM package isn't included with the official distribution (from postgres website)? No incredible deal just to build it myself, just wondering. -rchit ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] ERROR: Cannot insert a duplicate key into a unique
Count me in for error codes. You can just see part of the code i'm using to
deal with the problem (some of the error messages changed from 7.0 to 7.1 --
i had to fix that):
def parseError(self, errval):
# first compile all the exceptions. Ideally we don't have to compile
# all of them first, but this makes the code that much readable, and
# speed is not important in the error case.
re_refInt = re.compile('.*referential integrity.*')
re_dupKey = re.compile('.*duplicate key.*')
re_nullAttr = re.compile('.*Fail to add null value in not null
attribute (.*)')
re_nonInt = re.compile('.*pg_atoi: error in (.*): can\'t parse.*')
re_nonBool = re.compile('.*Bad boolean external representation
\'(.*)\'')
re_nonIP = re.compile('.*invalid INET value.*')
re_nonTimestamp = re.compile('.*Bad timestamp external
representation \'(.*)\'')
re_invalidAttribute = re.compile('.*Relation \'(.*)\' does not have
attribute \'(.*)\'.*')
re_deadlockDetected = re.compile('.*Deadlock detected.*')
# various errors captured from posgres
# ERROR: Relation 'users' does not have attribute 'blah'
# ERROR: unnamed referential integrity violation - key in
managedservers stillreferenced from managedserverstatus
#
# ERROR: Bad boolean external representation '3.4.5.6'
# ERROR: invalid INET value '3456' ## for ip
# ERROR: ExecAppend: Fail to add null value in not null attribute
mystr
# ERROR: Relation 'users' does not have attribute 'blah'
# pg_atoi: error in hello: can't parse hello
# ERROR: Bad timestamp external representation 'fdsa'
# ERROR: Deadlock detected.
-Original Message-
From: Christopher Kings-Lynne [mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 13, 2001 3:31 AM
To: Haller Christoph; [EMAIL PROTECTED]
Subject: Re: [HACKERS] ERROR: Cannot insert a duplicate key into a
unique index
In our PHP app, we are also forced to parse error messages to get that kind
of information. Register my vote for error codes (Tom Lane style...)
Chris
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of Haller Christoph
Sent: Thursday, 13 September 2001 7:18 PM
To: [EMAIL PROTECTED]
Subject: [HACKERS] ERROR: Cannot insert a duplicate key into a unique
index
[HACKERS] ERROR: Cannot insert a duplicate key into a unique index
I'm working on a C code application using loads of
insert commands.
It is essential to distinguish between an error
coming from a misformed command or other fatal
reasons and a duplicate key.
In either case, the PQresultStatus() returns
PGRES_FATAL_ERROR
I can check PQresultErrorMessage() for the
error message above, but then I have to rely
on this string never be changed.
This is no good programming style.
Does anybody have another, better idea or is
there at least a header file available, where
all the error messages can be found?
Regards, Christoph
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[HACKERS] Does the oid column have an implicit index on it?
This may sound like a stupid question, and i apologize if it is, but I couldn't find the answer in any documentation. Every table has a implicit column oid. Does this column have an index on it? I assume not, and I am putting an index on it anyway. The real problem is that I have a table like the following: create table foo ( time timestamp DEFAULT CURRENT_TIMESTAMP, ... ) I insert an row, and I want to get the timestamp of that row. So i do a select on oid. I want an index. Does one already exist? -rchit ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] varchar vs. text
Is there any good reason to use VARCHAR over TEXT for a string field? ie. performance hits, etc. Other than running into the row size limit problem, are there any large storage / performance penalties of using TEXT for virtually all strings? For ex. A phone number. This field probably wouldn't be bigger that 40 characters, but I can use TEXT and be sure that nothing gets truncated. Same with a name field. -rchit ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] WaitOnLock: error on wakeup
Anyone know why I could possibly get this error? This doesn't happen deterministically. WaitOnLock: error on wakeup - Aborting this transaction I also got this notice: NOTICE: Deadlock detected -- See the lock(l) manual page --- Actually, what I'm looking for in this mail is a possible way for me to deterministically reproduce this by hand, to see if I can create this situation and then look in my code to see where I could possibly be doing the wrong thing. I'm not using anything fancy in my queries, Just foreign key constraints (all initially deferred), Selects, inserts, updates, views, transactions. No explicit lock or select for updates or triggers or notifiys or rules. I'm using Postgres 7.0.3. BTW, i tried searching the mailing list and turned up nothing interesting. I didn't search super carefully, because the search site is extremely slow. Thanx! -rchit ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
RE: [HACKERS] Any time estimates for 7.1.2 RPM's ?
But beforwarned that if you build the package on rpm 3.0.5, the machines with previous versions of RPM will not be able to install that RPM. So you will have to upgrade all of your machines (and also install a couple of libraries, ie. popt and something else or the other). (correct me if I'm wrong here...) -rchit -Original Message- From: Lamar Owen [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 07, 2001 8:40 AM To: Hannu Krosing Cc: [EMAIL PROTECTED] Subject: Re: [HACKERS] Any time estimates for 7.1.2 RPM's ? -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thursday 07 June 2001 11:24, Hannu Krosing wrote: I have had bad experience upgrading rpm package a few times (the rpm database getting whacked and all subsequent installs claiming some packages to be missing) so I'd rather avoid upgrading unless I absolutely have to. Well,the upgrade to 3.0.5 isn't a problem. Personally, I won't upgrade my 6.2 box to RPM4 -- but that is a personal choice having a lot to do with the RPM release of PostgreSQL built on this machine. Upgrading to 3.0.5 should be completely painless. You do need the new 'rpm-build' package, though, or you won't be able to rebuild. - -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.4 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE7H6Bi5kGGI8vV9eERAveXAJ9gmYsSYas4/CGVucJdl+BYEKEUjACeOCNW 47lMVY1Hjv5SFW0tp4MMBqg= =kTbh -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(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
[HACKERS] inserts on a transaction blocking other inserts
I am having problems with transactions and foreign key constraints in postgres 7.0-3 (RPM distribution). . The foreign key constraints were blocking concurrent transactions. Here is an example where something blocked but shouldn't have blocked: create table hello10 (myid serial primary key, myvalue int4); create table hello11(myvalue int4, foreign key (myvalue) references hello10); insert into hello10 (myvalue) values (1); ok, now everything is set up for the blocking problem. Now have two logins to psql: psql1# begin; psql1# insert into hello11 (myvalue) values (1) psql1# switch to the other login psql2# begin; psql2# insert into hello11 (myvalue) values (1) *** block *** It shouldn't block there. Basically it happens when two transactions try to insert something into tables (doesn't have to be the same one) which both have a foreign key constraint to a common key. I did some poking around and luckily did find something in the archives that was similar here: http://fts.postgresql.org/db/mw/msg.html?mid=30149 It was mentioned that it was a problem, and there was a workaround (add INITIALLY DEFFERED to the constraint). The workaround works. My question is, is this fixed in Postgres 7.1 (i don't have a spare machine to test, sorry)? -rchit ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
RE: [HACKERS] Packaging 7.1.1
oh btw, i completely forgot to mention the minor fixes to the linux init scripts i mentioned earlier (about 2 weeks ago) for things that perhaps should be in the 7.1.1 release. (someone sent out a mail that they were branching 7.1.1) Also i never got a response on who actually packages those linux init scripts that appear in the RPM but not on the pgsql cvs tree. (i am also curious on why it is different, and how the RPM is built). -rchit -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 03, 2001 9:16 AM To: PostgreSQL-development Subject: [HACKERS] Packaging 7.1.1 I am starting to package 7.1.1, and I see I did not brand 7.1 properly. I forgot the date in the HISTORY file, and didn't update register.txt. I will do all those now for 7.1.1. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
RE: [HACKERS] Packaging 7.1.1
Thanks a lot for your total and complete description of the process. (i should have checked out the sprm first before asking). I empathize with what you said about packaging not being a simple task, i have been through the agony. About putting your stuff into the postgres tree, i believe it would be a good thing other than bad to include it in pgsq. It can be put into the contrib directory (because it isn't part of the core portable stuff). This solution was done for the portable openssh cvs tree. not only redhat packaging stuff was included, but the solaris pkg mechanism was also in there (and i also believe there were some others). It usually isn't a lot of files (ie. the spec file and maybe the initscript). Of course its up to the gods of the pgsql tree what they want to do with it, so i'm just going to raise this suggestion and shut up. anyways, getting back to the what brought me to ask about this, can you add the fixes to these two small problems in your initscripts? 1. `pidof` should be `pidof -s` (2 instances) 2. restart) should be stop; sleep x; start ideally, stop should actually wait till postgres fully stops. The sleep is just a temporary fix. I have a more thorough email i sent earlier, i can resend it to you if you want. -rchit ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
RE: [HACKERS] start / stop scripts question
who is it distributed by then? it was on the postgres ftp mirror sites, so it probably can't be redhat. I have found workarounds, so its not a big deal, but... Also, i wonder what else is different from this package from the real source distribution. I am sorry if this has been discussed or explained in the past before, but i cannot find this info in a FAQ or know what keywords to use if i want to search on the mailing list :). -rchit -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 24, 2001 7:28 AM To: Rachit Siamwalla Cc: PostgreSQL Development Subject: Re: [HACKERS] start / stop scripts question You will find that that script is not distributed by us. [ Charset ISO-8859-1 unsupported, converting... ] Hi, I believe i found two minor bugs in the linux start/stop scripts for the downloadable rpm version of postgres 7.1. I don't think these have been reported already (i did some quik searches). Please look these over and see if i'm just smoking something or if these bugs are valid. Also, i did a quick cvs checkout / log of the contrib tree, and i noted that the start/stop scripts have been restructured recently (i do not know where logic of the scripts were moved to, so these points may still be valid, if not, i was wondering if I pull the scripts from the cvs contrib tree myself, would they work out of the box?). --- #1. Every instance of (there are 2): pid=`pidof postmaster` if [ $pid ] should be: pid=`pidof -s postmaster` if [ $pid ] (pidof may return multiple pids if postmaster forked or has multiple threads -- i'm not toofamiliar with postgres architecture, but postmaster does sometimes show multiple pids which could mean multiple threads or processes in linux) If pidof returns multiple pids, the if will barf giving something like the following: Stopping postgresql service: [ OK ] Checking postgresql installation: [ OK ] /etc/rc.d/init.d/postgresql: [: 1223: unary operator expected Starting postgresql service: [FAILED] #2. /etc/rc.d/init.d/postgresql restart sometimes doesn't do what it should. ie. end up with a fresh newly started postgres daemon. This happens because the rc.d script does something very simple: stop; start. This is correct, but stop doesn't do what it should. When stop returns, postgres may not have fully stopped for some reason. start complains that postmaster is still running. After doing some testing, my hypothesis is this (i have no idea how postgres works intermally): 1. I run a bunch of inserts, create tables 2. I call postgres stop 3. one of the postgres processes stops. 4. the other processes are still trying to flush stuff onto the disk before they quit. 5. start is called, and it finds some postmaster processes, and thus says postmaster is running. 6. the other processes finally are done and stop. Now there are no more postgres running. When i added a sleep 10 between stop / start, everything was fine. The correct solution would be for postgres stop to actually wait for the entire db to exit cleanly. BTW, i uncovered this via an automated install / configuration / population of a postgress database which involves a restart right after population of a database. Thanx. -rchit ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] start / stop scripts question
Hi, I believe i found two minor bugs in the linux start/stop scripts for the downloadable rpm version of postgres 7.1. I don't think these have been reported already (i did some quik searches). Please look these over and see if i'm just smoking something or if these bugs are valid. Also, i did a quick cvs checkout / log of the contrib tree, and i noted that the start/stop scripts have been restructured recently (i do not know where logic of the scripts were moved to, so these points may still be valid, if not, i was wondering if I pull the scripts from the cvs contrib tree myself, would they work out of the box?). --- #1. Every instance of (there are 2): pid=`pidof postmaster` if [ $pid ] should be: pid=`pidof -s postmaster` if [ $pid ] (pidof may return multiple pids if postmaster forked or has multiple threads -- i'm not toofamiliar with postgres architecture, but postmaster does sometimes show multiple pids which could mean multiple threads or processes in linux) If pidof returns multiple pids, the if will barf giving something like the following: Stopping postgresql service: [ OK ] Checking postgresql installation: [ OK ] /etc/rc.d/init.d/postgresql: [: 1223: unary operator expected Starting postgresql service: [FAILED] #2. /etc/rc.d/init.d/postgresql restart sometimes doesn't do what it should. ie. end up with a fresh newly started postgres daemon. This happens because the rc.d script does something very simple: stop; start. This is correct, but stop doesn't do what it should. When stop returns, postgres may not have fully stopped for some reason. start complains that postmaster is still running. After doing some testing, my hypothesis is this (i have no idea how postgres works intermally): 1. I run a bunch of inserts, create tables 2. I call postgres stop 3. one of the postgres processes stops. 4. the other processes are still trying to flush stuff onto the disk before they quit. 5. start is called, and it finds some postmaster processes, and thus says postmaster is running. 6. the other processes finally are done and stop. Now there are no more postgres running. When i added a sleep 10 between stop / start, everything was fine. The correct solution would be for postgres stop to actually wait for the entire db to exit cleanly. BTW, i uncovered this via an automated install / configuration / population of a postgress database which involves a restart right after population of a database. Thanx. -rchit ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
