Re: [BUGS] _bt_check_unique checks every row in table when doing update??
[EMAIL PROTECTED] (Tom Lane) writes: > Mats Lofkvist <[EMAIL PROTECTED]> writes: > > But when doing ~1000 updates (i.e. setting val0 and val1 with > > a where on an existing key0/key1/key2 triplet), I get this which > > seems very strange to me: > > I suppose you repeatedly updated the same row 1000 times? That creates > an O(N^2) behavior because the dead tuples have to be rechecked again > and again. > > 7.3 will be smarter about this. > Seems like I get the same behaviour with 7.3 beta1, updating the same row ~20k times and then 1k times more with profiling enabled (and with no vacuum in between) gives: --- 2.72 166.121002/1002_bt_doinsert [17] [18]53.72.72 166.121002 _bt_check_unique [18] 15.81 149.01 21721926/21721926 _bt_isequal [19] 0.051.00 221414/412979 _bt_getbuf [40] 0.010.21 221414/409772 _bt_relbuf [91] 0.010.022709/6241heap_fetch [187] 0.000.005418/2726620 LockBuffer [50] 0.000.001002/65406369 _bt_binsrch[270] 0.000.002709/1333460 ReleaseBuffer [76] 0.000.002709/4901HeapTupleSatisfiesVacuum [519] 0.000.001707/4910SetBufferCommitInfoNeedsSave [652] --- (In my case, I think the call to _bt_check_unique could be avoided altogether since the update isn't changing any of the columns present in the unique key. But doing this optimization maybe is much harder than just trying to avoid checking the dead tuples over and over again?) _ Mats Lofkvist [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[BUGS] Bug #764: 7.3b1 : SET gives misleading error information with non-valid option name and multiple arguments
Tim Knowles ([EMAIL PROTECTED]) reports a bug with a severity of 3 The lower the number the more severe it is. Short Description 7.3b1 : SET gives misleading error information with non-valid option name and multiple arguments Long Description PostgrsqSQL 7.3b1 If you use SET with a non-valid option name and multiple arguments it gives a misleading error message suggesting that the non-valid option name is correct and can take only one argument. Sample Code test=# set anythinyoulike=1; ERROR: 'anythinyoulike' is not a valid option name test=# set anythinyoulike=1,2; ERROR: SET anythinyoulike takes only one argument No file was uploaded with this report ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] HELP
On Thu, 2002-09-12 at 01:13, Brendon Matthews wrote: > Hi, > > I have been using PostgreSQL 7.2.2 for a while now on one of our live > machines, however all of a sudden with no warning, we are no longer able to > run any UPDATE statements on one of our databases. SELECT and INSERT > statements have no issues. Try VACUUM FULL (different than regular VACUUM). Try dumping data / shutdown daemon / fry data directory / initdb / start daemon / restore data. -- Rod Taylor ---(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: [BUGS] HELP
"Brendon Matthews" <[EMAIL PROTECTED]> writes: > I have been using PostgreSQL 7.2.2 for a while now on one of our live > machines, however all of a sudden with no warning, we are no longer able to > run any UPDATE statements on one of our databases. SELECT and INSERT > statements have no issues. > The error we are getting is: > "The backend has broken the connection. Possibly the action you have > attempted has caused it to close." > Immediately after this call the database system restarts itself. Sounds like a backend crash. What do you get in the postmaster log when this happens? If there's a core file, can you provide a gdb stack trace? (If there's not a core file, restarting the postmaster under "ulimit -c unlimited" should allow one to be produced.) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] _bt_check_unique checks every row in table when doing update??
Mats Lofkvist <[EMAIL PROTECTED]> writes: > [EMAIL PROTECTED] (Tom Lane) writes: >> 7.3 will be smarter about this. > Seems like I get the same behaviour with 7.3 beta1, updating > the same row ~20k times and then 1k times more with profiling > enabled (and with no vacuum in between) gives: > 2.72 166.121002/1002_bt_doinsert [17] > [18]53.72.72 166.121002 _bt_check_unique [18] >15.81 149.01 21721926/21721926 _bt_isequal [19] > 0.051.00 221414/412979 _bt_getbuf [40] > 0.010.21 221414/409772 _bt_relbuf [91] > 0.010.022709/6241heap_fetch [187] Yeah, but you'll notice there is no heap_fetch for most of 'em, unlike before... The loop in _bt_check_unique tests _bt_isequal before checking ItemIdDeleted, so the short-circuit for deleted items doesn't serve to reduce the number of key comparisons, only the number of heap tuple fetches. I do not think that reversing this logic would be a net improvement in typical cases: we want to fall out of the loop as soon as we've run off the set of equal keys, whether the current index entry is dead or alive. If we switched the test order then we'd not get out of the loop until we found a live entry that's bigger than the insertion key. > (In my case, I think the call to _bt_check_unique could be > avoided altogether since the update isn't changing any of > the columns present in the unique key. It's fairly difficult for the index AM to know that; in general we don't have access to the old tuple to check, at the time the index update is running. 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
Re: [BUGS] Bug #764: 7.3b1 : SET gives misleading error information with non-valid option name and multiple arguments
[EMAIL PROTECTED] writes: > 7.3b1 : SET gives misleading error information with non-valid option name and >multiple arguments Fixed, thanks. 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])
Re: [BUGS] _bt_check_unique checks every row in table when doing update??
[EMAIL PROTECTED] (Tom Lane) writes: > Mats Lofkvist <[EMAIL PROTECTED]> writes: > > [EMAIL PROTECTED] (Tom Lane) writes: > >> 7.3 will be smarter about this. > > > Seems like I get the same behaviour with 7.3 beta1, updating > > the same row ~20k times and then 1k times more with profiling > > enabled (and with no vacuum in between) gives: > > > 2.72 166.121002/1002_bt_doinsert [17] > > [18]53.72.72 166.121002 _bt_check_unique [18] > >15.81 149.01 21721926/21721926 _bt_isequal [19] > > 0.051.00 221414/412979 _bt_getbuf [40] > > 0.010.21 221414/409772 _bt_relbuf [91] > > 0.010.022709/6241heap_fetch [187] > > Yeah, but you'll notice there is no heap_fetch for most of 'em, unlike > before... Ah, missed that one. > > The loop in _bt_check_unique tests _bt_isequal before checking > ItemIdDeleted, so the short-circuit for deleted items doesn't serve to > reduce the number of key comparisons, only the number of heap tuple > fetches. I do not think that reversing this logic would be a net > improvement in typical cases: we want to fall out of the loop as soon as > we've run off the set of equal keys, whether the current index entry is > dead or alive. If we switched the test order then we'd not get out of > the loop until we found a live entry that's bigger than the insertion > key. In my case the problem is that my unique index consists of three varchar(32) columns which I suppose is making the comparison rather expensive. But I agree that it is not obvious that changing the logic would be an improvement in general. > > > (In my case, I think the call to _bt_check_unique could be > > avoided altogether since the update isn't changing any of > > the columns present in the unique key. > > It's fairly difficult for the index AM to know that; in general we don't > have access to the old tuple to check, at the time the index update is > running. But you really shouldn't need the old tuple to know this since none of the columns present in the unique index are 'set' by the update? I.e. the 'not changing the unique key part' is not data dependent, it is guarantied by the form of the update statement. Isn't it a very common case to do updates with sets only on non-key columns (only selecting on the key columns) ? > > regards, tom lane _ Mats Lofkvist [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] _bt_check_unique checks every row in table when doing update??
Mats Lofkvist <[EMAIL PROTECTED]> writes: > [EMAIL PROTECTED] (Tom Lane) writes: >>> (In my case, I think the call to _bt_check_unique could be >>> avoided altogether since the update isn't changing any of >>> the columns present in the unique key. >> >> It's fairly difficult for the index AM to know that; in general we don't >> have access to the old tuple to check, at the time the index update is >> running. > But you really shouldn't need the old tuple to know this since none > of the columns present in the unique index are 'set' by the update? > I.e. the 'not changing the unique key part' is not data dependent, > it is guarantied by the form of the update statement. (a) that's even further upstream from the index AM, and (b) what about BEFORE triggers that change the tuple contents? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] _bt_check_unique checks every row in table when doing update??
[EMAIL PROTECTED] (Tom Lane) writes: > > But you really shouldn't need the old tuple to know this since none > > of the columns present in the unique index are 'set' by the update? > > I.e. the 'not changing the unique key part' is not data dependent, > > it is guarantied by the form of the update statement. > > (a) that's even further upstream from the index AM, and (b) what about > BEFORE triggers that change the tuple contents? > > regards, tom lane Ok, I rest my case. I obviously don't know enough of the postgres internals to suggest a usable solution for this problem. Thanks for your quick answers. _ Mats Lofkvist [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] unsubscribe *
unsubscribe * ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] _bt_check_unique checks every row in table when doing update??
Tom Lane wrote: > > (In my case, I think the call to _bt_check_unique could be > > avoided altogether since the update isn't changing any of > > the columns present in the unique key. > > It's fairly difficult for the index AM to know that; in general we don't > have access to the old tuple to check, at the time the index update is > running. We do have a TODO item: * Prevent index uniqueness checks when UPDATE does not modifying column -- 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] HELP
I would create new tables, copy the data over, then drop the old tables, and rename the new tables. I have had similar problems iwht MySQL, but never with PostgreSQL. Mark > > Hi, > > I have been using PostgreSQL 7.2.2 for a while now on one of our live > machines, however all of a sudden with no warning, we are no longer able to > run any UPDATE statements on one of our databases. SELECT and INSERT > statements have no issues. > > The error we are getting is: > "The backend has broken the connection. Possibly the action you have > attempted has caused it to close." > Immediately after this call the database system restarts itself. > > We are using: > Red Hat Linux > Apache 1.3.26 > PostgreSQL 7.2.2 > Java 1.4 + the latest PostgreSQL jdbc driver from jdbc.postgresql.org > > Configuration: > We are running postmaster -i listening on localhost only. > > We have 3 PostgreSQL databases on the same machine, and the other two have > no problems at all (yet). They are all using the same software mentioned > above. > > Things i have tried: > Restarting JVM and Apache (problem still occurs) > Restarting postmaster -i (problem still occurs) > Vaccuming all databases (problem still occurs) > Rebooting entire machine (problem still occurs) > Dropping the database and creating another (worked for a while, but after > processing SQL calls for about a day and a half the problem started up > again) > > Is there anything i can do to fix this? Has anyone else seen this before? > We need our database system back up and running ASAP. > > Regards, > > Brendon Matthews > Analyst Programmer > BBTech Ltd > Ph: (03) 381 1127 > Fax: (03) 381 1185 > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] HELP
Then again, dumping is probably better, and deleting the database, and then restoring it. I am just curious if it is a database problem or a table problem. If you can creat new tables that work fine, but it is a strange issue. Mark > > I would create new tables, copy the data over, then drop the old tables, > and rename the new tables. > > I have had similar problems iwht MySQL, but never with PostgreSQL. > Mark > > > > > > Hi, > > > > I have been using PostgreSQL 7.2.2 for a while now on one of our live > > machines, however all of a sudden with no warning, we are no longer able to > > run any UPDATE statements on one of our databases. SELECT and INSERT > > statements have no issues. > > > > The error we are getting is: > > "The backend has broken the connection. Possibly the action you have > > attempted has caused it to close." > > Immediately after this call the database system restarts itself. > > > > We are using: > > Red Hat Linux > > Apache 1.3.26 > > PostgreSQL 7.2.2 > > Java 1.4 + the latest PostgreSQL jdbc driver from jdbc.postgresql.org > > > > Configuration: > > We are running postmaster -i listening on localhost only. > > > > We have 3 PostgreSQL databases on the same machine, and the other two have > > no problems at all (yet). They are all using the same software mentioned > > above. > > > > Things i have tried: > > Restarting JVM and Apache (problem still occurs) > > Restarting postmaster -i (problem still occurs) > > Vaccuming all databases (problem still occurs) > > Rebooting entire machine (problem still occurs) > > Dropping the database and creating another (worked for a while, but after > > processing SQL calls for about a day and a half the problem started up > > again) > > > > Is there anything i can do to fix this? Has anyone else seen this before? > > We need our database system back up and running ASAP. > > > > Regards, > > > > Brendon Matthews > > Analyst Programmer > > BBTech Ltd > > Ph: (03) 381 1127 > > Fax: (03) 381 1185 > > > > > > ---(end of broadcast)--- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Bug #759: 7.3beta1 DROP COLUMN DEPENDENCY PROBLEM
[EMAIL PROTECTED] writes: > Been playing with the 7.3beta1 version and I've noticed a small > problem with dependency checking when dropping a column. If you have > a view which uses JOIN's to join tables then dropping a column will > fail on a dependency check, even though the column being dropped is > not used at all in the view. FYI, I've fixed this in CVS tip. Thanks for the report! 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])
[BUGS] No dependency between fkey constraint and unique index
The following statements will fail... CREATE TABLE t1 (a int); CREATE TABLE t2 (a int references t1(a)); ERROR: UNIQUE constraint matching given keys for referenced table "t1" not found But I can do the following... CREATE TABLE t3 (a int primary key); CREATE TABLE t4 (a int references t3(a)); ALTER TABLE t3 DROP CONSTRAINT t3_pkey; There is no dependency generated between the foreign key constraint and the primary key. I would like to see a column in pg_constraint confconid that indicated which unique constraint is supporting the foreign key and the supporting dependency in pg_depend. This would be useful because you can create multiple unique constraints over the same set of keys and not know which one is supporting a foreign key constraint. CREATE TABLE t5 (a int); ALTER TABLE t5 ADD CONSTRAINT t5_un_1 UNIQUE (a); ALTER TABLE t5 ADD CONSTRAINT t5_un_2 UNIQUE (a); On a somewhat related note... CREATE UNIQUE INDEX does not add an entry to pg_constraint. Is this because unique constraints are different from unique indexes in that the index can be functional and/or partial? Would it be possible to add an entry to pg_constraint in the simple case? Kris Jurka ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
