Re: [SQL] finding if a foreign key is in use
On Thu, 1 Jul 2004, Kenneth Gonsalves wrote: > On Tuesday 29 June 2004 07:19 pm, Phil Endecott wrote: > > Kenneth Gonsalves <[EMAIL PROTECTED]> wrote: > > > in my app i have a table where the id serves as a foreign key for > > > one or more other tables. if i want to delete a row in the table, > > > i currently search the other tables where this table is referenced > > > to see if the row is in use - and then, if not in use, permit > > > deletion. > > > > Now if i want the delete button in my app to be disabled whenever > > > a row that is in use is selected, searching the database every time > > > would dramatically slow down the app. > > > > Basically you do have to do this search. But it won't be too slow if you > > create an index on the foreign key. > > pity. thought postgres would have some function like 'in_use' to tell when a > row that is used as a foreign key is in actual use and hence cannot be > deleted. surely, in a database of millions of records, it wouldnt have search > them all to find if the row is in use? in my case the id field serves as a Well, it's kind of a losing proposition either way. One way (the one we take right now) involves checking rows on the fktable on pk update/delete which is hoped to be fast if the user creates appropriate indexes but has issues if that plan isn't fast. The other would probably involve trying to keep track of known in_use. There are some problems there however in knowing what that value should be. For example after a delete of a row in the fktable, do you know what in_use is on its referenced row without finding all things that reference it and checking them (and what about seeing or locking stuff for concurrent transactions?) You can use a known in_use vs unknown in_use vs known not in use style value to get around that, but then I think there'd still be issues in guaranteeing the correct semantics and I think rows may often end up in unknown state at which point you scan anyway. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] finding if a foreign key is in use
in my app i have a table where the id serves as a foreign key for one or more other tables. if i want to delete a row in the table, i currently search the other tables where this table is referenced to see if the row is in use - and then, if not in use, permit deletion. Now if i want the delete button in my app to be disabled whenever a row that is in use is selected, searching the database every time would dramatically slow down the app. Basically you do have to do this search. But it won't be too slow if you create an index on the foreign key. pity. thought postgres would have some function like 'in_use' to tell when a row that is used as a foreign key is in actual use and hence cannot be deleted. surely, in a database of millions of records, it wouldnt have search them all to find if the row is in use? It doesn't "search them all" if you have an index. If your database has a million records it needs to look at only 20 index entries, as 2^20 is about a million. (At least that's what I, naively, think it should do - anyone who knows more want to correct me?) --Phil. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] finding if a foreign key is in use
O kyrios Phil Endecott egrapse stis Jul 1, 2004 : > >>>in my app i have a table where the id serves as a foreign key for > >>>one or more other tables. if i want to delete a row in the table, > >>>i currently search the other tables where this table is referenced > >>>to see if the row is in use - and then, if not in use, permit > >>>deletion. > >>>Now if i want the delete button in my app to be disabled whenever > >>>a row that is in use is selected, searching the database every time > >>>would dramatically slow down the app. There's an alternative approach to take. Educate your users to be familiar with PostgreSQL error messages (E.g. ERROR: update or delete on "vessels" violates foreign key constraint "$1" on "certificates" ) > >> > >>Basically you do have to do this search. But it won't be too slow if you > >>create an index on the foreign key. > > > > pity. thought postgres would have some function like 'in_use' to tell when a > > row that is used as a foreign key is in actual use and hence cannot be > > deleted. surely, in a database of millions of records, it wouldnt have search > > them all to find if the row is in use? > > It doesn't "search them all" if you have an index. If your database has > a million records it needs to look at only 20 index entries, as 2^20 is > about a million. (At least that's what I, naively, think it should do - > anyone who knows more want to correct me?) > > --Phil. > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > -- -Achilleus ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Table and/or Database Creation Time
Is there such thing as a table or database creation time in the SQL standard, that you could avail yourself of? I mean do databases keep this info. I think they do since they are like little OSs and many of them have internal back up features, that must use some kind of timing. Or? ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] URGENT - Need the DATA TYPES comparison for PostgreSQL and ORACLE and SQL Server
Hi Anyone, I would like to port some databases from ORACLE and MsSQL over to POSTGRESQL Could I get a comparison chart of the data types between these 3 databases ? Thanks Duane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] FW: "=" operator vs. "IS"
I'm just curious - why is it not possible to use the "=" operator to compare values with NULL? I suspect that the SQL standard specified it that way, but I can't see any ambiguity in an expression like "AND foo.bar = NULL". Is it because NULL does not "equal" any value, and the expression should be read as "foo.bar is unknown"? Or is there something else I'm missing? As noted elsewhere, joining two tables on "a.foo = b.foo" where both foo values are NULL is not usually what you want. But if you really, truly do want that, then you always have this: coalesce(a.foo,0) = coalesce(b.foo,0) or a similar construct, using something in place of zero that has the same base type as a.foo and b.foo, and which doesn't occur in either table. (Why? Because you really don't want a.foo = coalesce(b.foo,0) or b.foo = coalesce(a.foo,0) to give you false positives.) -- (Posted from an account used as a SPAM dump. If you really want to get in touch with me, dump the 'jboes' and substitute 'mur'.) Jeffery Boes <>< [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] How to make a portable application?
I am sure that this is way too broad of a question to ask here, but heck, if anyone has any thoughts it would be helpful. I wonder, how do you make a truly portable sql application? You see apps out there that can run on just about any SQL server. I have always wondered how they can do that? I mean, I have run into so many problems just moving the simplest things from one system to the next, from Linux to Windows, from Mysql to Postgres, MSSQL to MySQL, whatever. The reason I ask is this, because when you go from Postgres to say Firebird, you have all your column or tables names in lowercase. Now that can probably just move right into Firebird if the datatypes are all setup right, but then all the names will be in UPPERCASE. Now your client app has a problem, wrong case. So how do they do it? Just enclose everything (all table and column names) in double-quotes? Do all SQL servers support that? FYI: My client app is Zope. Which, as I write this, is occurring to me that IT may be my problem. (Python code is case-sensitive) I am really thinking out loud here, so dont feel like you need to reply. But, having any thought from you more experienced developers is always helpful. Thanks.
Re: [SQL] Unrecognized node type
> I suppose there's some other factor involved in the problem that > you didn't show us. But... what? :-S > As far as finding the bug, you need to provide a complete, > self-contained test case. Can you be more explicit? > As far as loading the schema, how about just removing the DEFAULT > clauses? "default null" is the default behavior anyway. I tried this, but then EJB's doesn't work... Thanks. -- Álvaro Sánchez-Mariscal Arnaiz | Departamento de Comunicación [EMAIL PROTECTED] | javaHispano javaHispano. Una Comunidad al Servicio de Todos www.javaHispano.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] quoting
I'm not a pgsql user, but reading chapter 37 of the user docs -
plpgsql-statements.html, I see that PL/pgsql requires quotes around FUNCTION
and PROCEDURE text, and I just want to comment that that seems like a rather
unfortunate decision. I like MS SQL's batch separation with GO better, but
lacking that, might there be an optional different quoting syntax, perhaps
like the HEREDOC syntax of bash or Perl, or like Perl's q() or qq() quoting
operators (or q{}, q[], etc)?
At least new users would be able to write their pl/pgsql commands in syntax
that doesn't require 2, 4, 8, or more single quote characters to represent
one.
---(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: [SQL] quoting
"Aaron W. West" <[EMAIL PROTECTED]> writes: > I see that PL/pgsql requires quotes around FUNCTION > and PROCEDURE text, and I just want to comment that that seems like a rather > unfortunate decision. There's been something done about this for 7.5. See the pgsql-hackers archives concerning "dollar quoting". 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
