Re: [GENERAL] Locking
On Sat, Aug 12, 2000 at 11:24:31PM -0400, Tom Lane wrote: > Ian West <[EMAIL PROTECTED]> writes: > > Is there any way to request postgres to immediately fail, or better yet > > fail after some small time when trying to obtain a conflicting lock ? > > Deadlocks should be detected within about a second. If you have an > example where one is not, let's see it... > > regards, tom lane I don't think my problem is a deadlock, and it is almost certainly a limitation of my code. The problem arises where one does a 'select [info] from [table] for update' by one user, which locks the row just fine. The user then proceeds to edit the info, and in some (bad) cases go to lunch. Another user then decides to update the same [info] record in the same table. They hang in the 'select for update' bit forever. (Or at least days) If I terminate the original user process, they proceed fine, which is as it should be. With the old Informix libs, in the same situation I would get an immediate error on the second client session (record in use by another user) and it would identify the user who had the record locked, which was enormously handy. (In sample above where 'user' had literally gone out to lunch half way through an update.) I understand that I can use non-blocking io, and poll for data, and send an abort after a delay if I don't get my lock within a reasonable time, but this doesn't help with ecpg as far as I can tell. (Although I may very well be missing the obvious here :-) The question I think is more can I set the default action on requiring a lock on a record which is in use to be fail, rather than wait. Or can I specify how long to wait. Thanks, Ian West
Re: [GENERAL] Locking
Ian West <[EMAIL PROTECTED]> writes: > Is there any way to request postgres to immediately fail, or better yet > fail after some small time when trying to obtain a conflicting lock ? Deadlocks should be detected within about a second. If you have an example where one is not, let's see it... regards, tom lane
[GENERAL] Locking
Is there any way to request postgres to immediately fail, or better yet fail after some small time when trying to obtain a conflicting lock ? I have ported some old code from Informix (mainly esql) and the default behaviour with the version I was using was to simply return an error. If not, is it planned for the furture ? (I am using 7.0.2 now) If the answer is no I have quite a bit of re-writing to do :-} Thanks, Regards, Ian West
Re: [GENERAL] turning of referential integrity
If you are using pg_dump, you probably should be fine if you're restoring a full database, because it should place the constraints in after the data is loaded. If you do a data only dump, there is code in the dump at the very top and bottom to disable and reenable the constraints and that should let you turn them off and on -- however, you CANNOT define any triggers between those two statements (let's just say it'd probably be bad). Stephan Szabo [EMAIL PROTECTED] On Sat, 12 Aug 2000, XWorkers wrote: > Hi All, > I am wondering is there any way to turning off referential integrity > without using SET CONSTRAINTS and create table with DEFERRABLE for database > restore purpose?
[GENERAL] turning of referential integrity
Hi All, I am wondering is there any way to turning off referential integrity without using SET CONSTRAINTS and create table with DEFERRABLE for database restore purpose? TIA. --Mursalin.
[GENERAL] Re: [HACKERS] Optimizer confusion?
Philip Warner <[EMAIL PROTECTED]> writes: >> Indexscan estimates are supposed to be nonlinear, actually, to account >> for the effects of caching. I doubt the shapes of the curves are right >> in detail, but I haven't had time to do any research about it. > As to the non-linearity, I would have thought the majority of I/Os by far > would be reading rows, and with retrieval by index, you may not get much > buffering benefit on table pages. For a large table, ISTM linear estimates > would be a good estimate. The estimate is linear, for number-of-tuples-to-fetch << number-of-pages- in-table. See src/backend/optimizer/path/costsize.c for the gory details. >> You could push random_page_cost and effective_cache_size around to try >> to match your platform better. Let me know if that helps... > Setting it to 0.1 works (it was 4). Unfortunately, random_page_cost < 1 is ridiculous on its face ... but that's not where the problem is anyway, as your next comment makes clear. > But this (I think) just highlights the > fact that the index is sorted by date, and the rows were added in date > order. As a result (for this table, in this query), the index scan get's a > much better cache-hit rate, so the actual IO cost is low. > Does that sound reasonable? Quite. The cost estimates are based on the assumption that the tuples visited by an indexscan are scattered randomly throughout the table. Obviously, if that's wrong then the estimates will be way too high. > Does the optimizer know if I have used clustering? Nope. To quote from the code: * XXX if the relation has recently been "clustered" using this index, * then in fact the target tuples will be highly nonuniformly * distributed, and we will be seriously overestimating the scan cost! * Currently we have no way to know whether the relation has been * clustered, nor how much it's been modified since the last * clustering, so we ignore this effect. Would be nice to do better * someday. The killer implementation problem here is keeping track of how much the table ordering has been altered since the last CLUSTER command. We have talked about using an assumption of "once clustered, always clustered", ie, ignore the issue of sort order degrading over time. That's pretty ugly but it might still be more serviceable than the current state of ignorance. For a table like this one, where rows are added in date order and (I imagine) seldom updated, the sort order isn't going to degrade anyway. For other tables, you could assume that you're going to run CLUSTER on a periodic maintenance basis to keep the sort order fairly good. I have not yet done anything about this, mainly because I'm unwilling to encourage people to use CLUSTER, since it's so far from being ready for prime time (see TODO list). Once we've done something about table versioning, we can rewrite CLUSTER so that it's actually reasonable to use on a regular basis, and at that point it'd make sense to make the optimizer CLUSTER-aware. > I don't suppose I can get the backend to tell me how many logical IOs and > how much CPU it used? Yes you can. Run psql with PGOPTIONS="-s" and look in the postmaster log. There's also -tparse, -tplan, -texec if you'd rather see the query time broken down by stages. regards, tom lane
Re: [GENERAL] dangers of setlocale() in backend (was: problem with float8 input format)
Louis-David Mitterrand <[EMAIL PROTECTED]> writes: >> IMPORTANT: changing the backend's locale on-the-fly is an EXTREMELY >> DANGEROUS thing to do, and I strongly recommend that you find another >> way to solve your problem. > The "problem" I am trying to solve is to send e-mail notifications to > auction bidders in their own language with the proper number formatting, > etc. From what you are saying I'll probably have to move these > notifications to the mod_perl layer of the application. Well, you could fork a subprocess to issue the mail and change locale only once you're safely inside the subprocess. regards, tom lane
Re: [GENERAL] ClassNotFoundException
I think its "org.postgresql.Driver"
Byron
- Original Message -
From:
Sean
Weissensee
To: [EMAIL PROTECTED]
Sent: Saturday, August 12, 2000 12:51
PM
Subject: [GENERAL]
ClassNotFoundException
Why do I get this above error in my jsp
page
here is the connection code
<%@page language="java" import="java.sql.*"%>
<%Driver DriverRecordset1 =
(Driver)Class.forName("PostgreSQL.Driver").newInstance();
[GENERAL] Re: PostgreSQL interfaces for PHP and Perl
David Steuber <[EMAIL PROTECTED]> writes: > For perl, using CPAN.pm, what install command will get me the > interfaces for PostgreSQL? Do I need to have PostgreSQL setup and > running first? Before you can use DBI with PostgreSQL, the PostgreSQL database server must be running on your computer or some other computer you can access over the network. What you need are the DBI and DBD-Pg perl modules. I generally install it by downloading it from the CPAN website or using RPM packages(under Red Hat Linux) so I can't tell you much about the usage of the CPAN.pm module. > > For PHP, are there any interfaces for PostgreSQL that I can get and > install? Where? Do I need to have PostgreSQL setup and running > first? You can get them from the PostgreSQL website downloads I think. -- Prasanth Kumar [EMAIL PROTECTED]
[GENERAL] ClassNotFoundException
Why do I get this above error in my jsp
page
here is the connection code
<%@page language="java"
import="java.sql.*"%> <%Driver DriverRecordset1 =
(Driver)Class.forName("PostgreSQL.Driver").newInstance();
[GENERAL] dangers of setlocale() in backend (was: problem with float8 input format)
On Sat, Aug 12, 2000 at 12:15:26PM -0400, Tom Lane wrote: > Louis-David Mitterrand <[EMAIL PROTECTED]> writes: > > When "seller_locale" is, for instance, "de_DE", then I get theses > > errors: > > ERROR: Bad float8 input format '0.05' > > Is Postgres expecting the float as 0,05 (notice the comma) because of > > the locale? > > I'm sure that's the issue. If you look at the source of the message > (float8in() in src/backend/utils/adt/float.c) you'll see that it's > just relying on strtod() to parse the input. If your local strtod() is > locale-sensitive then the expected input format changes accordingly. > Not sure whether that's a feature or a bug, but it's how Postgres > has always worked. So using "setlocale(LC_MESSAGES, seller_locale)" instead of "LC_ALL" should be safe? It doesn't touch numeric formatting. > IMPORTANT: changing the backend's locale on-the-fly is an EXTREMELY > DANGEROUS thing to do, and I strongly recommend that you find another > way to solve your problem. The "problem" I am trying to solve is to send e-mail notifications to auction bidders in their own language with the proper number formatting, etc. From what you are saying I'll probably have to move these notifications to the mod_perl layer of the application. Too bad... not being a C programmer it took me a while to be able to send mail from the trigger. Oh well. > Running with a different locale changes the expected sort order for > indices, which means that your indices will become corrupted as items > get inserted out of order compared to other items (for one definition > of "order" or the other), leading to failure to find items that should > be found in later searches. You mean the indices change because accented characters can come into play w.r.t the sort order? > Given that your trigger has been exiting with the changed locale still > in force, I'm surprised your DB is still functional at all (perhaps > you have no indexes on textual columns?). Right, not yet. > But it'd be extremely dangerous even if you were to restore the old > setting before exit --- what happens if there's an elog(ERROR) before > you can restore? > At present, the only safe way to handle locale is to set it in the > postmaster's environment, never in individual backends. What's more, > you'd better be careful that the postmaster is always started with the > same locale setting for a given database. You can find instances of > people being burnt by this sort of problem in the archives :-( Many thanks for the thorough and clear explanation of the issues. Cheers, [much relieved at having found "why"] -- Louis-David Mitterrand - [EMAIL PROTECTED] - http://www.apartia.org "Of course Australia was marked for glory, for its people had been chosen by the finest judges in England."
Re: [GENERAL] problem with float8 input format
Louis-David Mitterrand <[EMAIL PROTECTED]> writes:
> where "date_part" comes from "date_part('epoch', stopdate)" in a
> previous query. The problem is the value of stop_date is not the number
> of seconds since the epoch but some internal representation of the data.
> So I can't compare stop_date with the output of
> GetCurrentAbsoluteTime().
GetCurrentAbsoluteTime yields an "abstime", so you should coerce the
"timestamp" result of date_part() to abstime and then you will get a
value you can compare directly.
> What function should I use to convert the Datum to a C int?
> DatumGetInt32 doesn't seem to work here.
No, because timestamps are really floats. (abstime is an int though.)
> And what is the method for float8 Datum conversion to C double?
double x = * DatumGetFloat64(datum);
This is pretty grotty because it exposes the fact that float8 datums
are pass-by-reference (ie, pointers). 7.1 will let you write
double x = DatumGetFloat8(datum);
which is much cleaner. (I am planning that on 64-bit machines it will
someday be possible for float8 and int64 to be pass-by-value, so it's
important to phase out explicit knowledge of the representation in user
functions.)
regards, tom lane
Re: [GENERAL] solution! (was: Re: problem with float8 input format)
Louis-David Mitterrand <[EMAIL PROTECTED]> writes: > When "seller_locale" is, for instance, "de_DE", then I get theses > errors: > ERROR: Bad float8 input format '0.05' > Is Postgres expecting the float as 0,05 (notice the comma) because of > the locale? I'm sure that's the issue. If you look at the source of the message (float8in() in src/backend/utils/adt/float.c) you'll see that it's just relying on strtod() to parse the input. If your local strtod() is locale-sensitive then the expected input format changes accordingly. Not sure whether that's a feature or a bug, but it's how Postgres has always worked. IMPORTANT: changing the backend's locale on-the-fly is an EXTREMELY DANGEROUS thing to do, and I strongly recommend that you find another way to solve your problem. Running with a different locale changes the expected sort order for indices, which means that your indices will become corrupted as items get inserted out of order compared to other items (for one definition of "order" or the other), leading to failure to find items that should be found in later searches. Given that your trigger has been exiting with the changed locale still in force, I'm surprised your DB is still functional at all (perhaps you have no indexes on textual columns?). But it'd be extremely dangerous even if you were to restore the old setting before exit --- what happens if there's an elog(ERROR) before you can restore? At present, the only safe way to handle locale is to set it in the postmaster's environment, never in individual backends. What's more, you'd better be careful that the postmaster is always started with the same locale setting for a given database. You can find instances of people being burnt by this sort of problem in the archives :-( regards, tom lane
[GENERAL] Plans to support database.table syntax?
Hi, Are there any plans to support specifying database in select statement? Like select * from mydb.mytable while being connected to another db. Cheers, Kari
Re: [GENERAL]
Eric Spaulding wrote: > > Anyone know an easy way to perform multi-database joins in postgres? > > > I would also like to know how people handle a situation where it is neccesary to maitain related databases separately. Right now I have a database which contains geographic data. Yet I want to connect at least 1 other database to it for now and I will be connecting more [differencet ones] to it in the future. Does postgreSQL have some sort of [external] table linking facility ? Trying to do this application side will cause the db to be hammered with numerous selects just to perform a simple logical operation. Is there a better alternative when the database cannot be merged ?
