Re: [GENERAL] Object Relational, Foreign Keys and Triggers
On Mon, Jan 24, 2005 at 07:22:32PM -0500, Alex Turner wrote:
I am facing the classic pgsql ORDBMS problem:
snip
Why are you using MAX()? That won't work at all. Perhaps you need to
look up the documentation for nextval and currval. In particular, that
second query should be:
insert into entity_phone select currval('entity_id_seq'),'610 495 5000';
Also, I'm not sure if inheritance works quite the way you think in the
example you give, though other people may correct me on that.
Hope this helps,
--
Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/
Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.
pgpN09KepBluE.pgp
Description: PGP signature
Re: [GENERAL] SCHEMA compatibility with Oracle/DB2/Firebird
On Sun, 23 Jan 2005 12:09:26 -0600, Jeffrey Melloy [EMAIL PROTECTED] wrote: Although Oracle doesn't have a search path, it is possible to make functions publicly available by doing grant blah to public. After that they can be used without a schema identifier. There is also CREATE PUBLIC SINONYM thing in Oracle. Jeff Nicolai ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Extended unit
Hi, My question is purely theoretical. I add use in my time in University some software that use extended type. For each variable, we define the mandatory classic type as integer, float, double array of. And we define an optional extended type as the unit in the MKSA system (Meter, Kilogram, Second, Ampere) or any other unit we would have previously define (eg. Currency). This extended type was wonderful, because there was warning/error if extend type does not match in any computation: you can not add apple to orange. I would appreciate to have such system in PostgreSQL. Do you think, it is feasible ? unrealistic ? Any comment ? Cordialement, Jean-Gérard Pailloncy ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Extended unit
On Tue, Jan 25, 2005 at 10:40:15AM +0100, Pailloncy Jean-Gerard wrote: Hi, My question is purely theoretical. I add use in my time in University some software that use extended type. For each variable, we define the mandatory classic type as integer, float, double array of. And we define an optional extended type as the unit in the MKSA system (Meter, Kilogram, Second, Ampere) or any other unit we would have previously define (eg. Currency). This extended type was wonderful, because there was warning/error if extend type does not match in any computation: you can not add apple to orange. I think it's a wonderful idea. You could use a similar mechanism to implement: - Currencies (so you can't add dollars to pounds) - Timezone aware timestamps (so a time in Australia looks differet from a time in Europe) Probably much more. I would appreciate to have such system in PostgreSQL. Do you think, it is feasible ? unrealistic ? Any comment ? I think it is definitly feasable. There's been discussion before. I think the best way syntax-wise would be to extend the type system generically to have subtypes. For example currency(gbp) and siunit(A). This would simplify operators. You could create a simple add operator that checked the subtype and complained if they didn't match. A multiply operator for siunit might even return the appropriate derived unit. An advanced add unit for currency might lookup an exchange rate table. However, I think this might be a tricky (but very worthwhile) project. Maybe create a subtypes table with the columns (oid, supertypeid, subtypename) and use the oid here to identify the subtype in storage. To be complete it would need to change: - The parser to idenify the new type definitions - pg_dump to dump these types - input/output functions for these types - handle storage But with a bit of work it could be a nice project. Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpqPZ1TZUwOR.pgp Description: PGP signature
Re: [GENERAL] disable trigger from transaction
I'm glad your curiosity got the best of you ;o) I was planning to test it out, but didn't have the time to do it. I too, was very curious as to what the ramifications of dropping the trigger would be in that scenario. Now, we know :o) On Monday 24 January 2005 11:07 pm, Jeff Davis saith: It got me curious enough that I tested it, and apparently droping a trigger locks the table. Any actions on that table must wait until the transaction that drops the trigger finishes. So, technically my system works, but requires a rather nasty lock while the transaction (the one that doesn't want the trigger to execute) finishes. Yours doesn't require any special locking, so it seems yours would be the preferred solution. Regards, Jeff Davis On Mon, 2005-01-24 at 13:45 -0500, Terry Lee Tucker wrote: I don't know if droping a trigger inside a transaction will work. Besides that, we want the trigger to do its work in all other circumstances. With a hundred connections on the database, I don't know what kind of issues that would cause if the trigger were there, and suddenly, not there. We figured this was a safe approach. Work: 1-336-372-6812 Cell: 1-336-363-4719 email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Backup DLL
Hi, Is anyone aware of a standalone DLL that can be integrated with applications for pg_dump and restore without resorting to asking clients to use tools like pgAdmin for their backup and restore cores? Thanks ALL-NEW Yahoo! Messenger - all new features - even more fun!
[GENERAL] EMBEDDED PostgreSQL
Hi, If I may ask, is there any plan for embedded PostgreSQL database as we have it in Firebird database?In Firebird embedded a compact engine of the database that can only accept connections from localhost (127.0.0.1) and easily distributable with single user applications exists. Any plan for such? ALL-NEW Yahoo! Messenger - all new features - even more fun!
Re: [GENERAL] Backup DLL
On Jan 25, 2005, at 7:25 AM, Tope Akinniyi wrote: Is anyone aware of a standalone DLL that can be integrated with applications for pg_dump and restore without resorting to asking clients to use tools like pgAdmin for their backup and restore cores? If your programming tool supports it, you can just build the pg_dump.exe command line and execute it. Your application just needs to have a folder included with pg_dump and the supporting dll libraries. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Backup DLL
AFAIK, there is no such thing. But you can use pg_dump in a pipe- that's all pgAdmin does. //Magnus From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tope AkinniyiSent: Tuesday, January 25, 2005 1:25 PMTo: pgsql-general@postgresql.orgSubject: [GENERAL] Backup DLL Hi, Is anyone aware of a standalone DLL that can be integrated with applications for pg_dump and restore without resorting to asking clients to use tools like pgAdmin for their backup and restore cores? Thanks ALL-NEW Yahoo! Messenger - all new features - even more fun!
Re: [GENERAL] EMBEDDED PostgreSQL
Tope Akinniyi wrote: If I may ask, is there any plan for embedded PostgreSQL database No. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Object Relational, Foreign Keys and Triggers
Actualy max() works just fine. It's not the solution I use in the
middle tier, but it works for a functional example. both max() and
currval() are bad because they can cause a race condition where the
sequence has been incremented by another thread. It's always better
to get nextval('sequence') and store it in a local var, then use it in
the main insert and corresponding sub-inserts.
The example I give has been tested, and works, it's not fake.
Alex Turner
NetEconomist
On Tue, 25 Jan 2005 09:23:31 +0100, Martijn van Oosterhout
kleptog@svana.org wrote:
On Mon, Jan 24, 2005 at 07:22:32PM -0500, Alex Turner wrote:
I am facing the classic pgsql ORDBMS problem:
snip
Why are you using MAX()? That won't work at all. Perhaps you need to
look up the documentation for nextval and currval. In particular, that
second query should be:
insert into entity_phone select currval('entity_id_seq'),'610 495 5000';
Also, I'm not sure if inheritance works quite the way you think in the
example you give, though other people may correct me on that.
Hope this helps,
--
Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/
Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.
---(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: [GENERAL] Validating user-input to be inserted in regular expressions
Vincenzo Ciancia wrote: Should I find every possible character in the documentation for regular expressions? Is the answer trivial? I checked the manual and the FAQ, and googled for the answer, but I didn't find it. Is there a more appropriate place where I can ask my question? Thanks Vincenzo -- Please note that I do not read the e-mail address used in the from field but I read vincenzo_ml at yahoo dot it Attenzione: non leggo l'indirizzo di posta usato nel campo from, ma leggo vincenzo_ml at yahoo dot it ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] EMBEDDED PostgreSQL
Do you need something from PostgreSQL that Firebird doesn't have? My experience has been that Firebird/Interbase is a fairly complete, ACID compliant, low/no maintenance back end for single workstation applications. On the other hand, you can make any RDBMS into an embedded database with enough behind the scenes scripting, cron jobs, etc. The autovacuum feature is a big help in the regard. You can tune once, deploy many times. You can throw away anything you don't need to make smaller distribution media. I've done this with Oracle, believe it or not, including automatic replication when two workstations see each other on the LAN. I've haven't had a chance to try it with PostgreSQL because no customer has accepted a proposal yet. If it can be done with Oracle, it can be done with PostgreSQL. Rick Tope Akinniyi [EMAIL PROTECTED]To: pgsql-general@postgresql.org ukcc: Sent by: Subject: [GENERAL] EMBEDDED PostgreSQL [EMAIL PROTECTED] tgresql.org 01/25/2005 07:21 AM Hi, If I may ask, is there any plan for embedded PostgreSQL database as we have it in Firebird database? In Firebird embedded a compact engine of the database that can only accept connections from localhost (127.0.0.1) and easily distributable with single user applications exists. Any plan for such? ALL-NEW Yahoo! Messenger - all new features - even more fun! ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] EMBEDDED PostgreSQL
Martha Stewart called it a Good Thing when [EMAIL PROTECTED] (Tope Akinniyi) wrote: If I may ask, is there any plan for embedded PostgreSQL database as we have it in Firebird database? In Firebird embedded a compact engine of the database that can only accept connections from localhost (127.0.0.1) and easily distributable with single user applications exists. You can do that with PostgreSQL by configuring pg_hba.conf to only accept connections from localhost. And there is no problem with the notion of creating a database in a local directory. None of this requires any change. -- output = reverse(moc.liamg @ enworbbc) http://www3.sympatico.ca/cbbrowne/postgresql.html Signs of a Klingon Programmer #11: This machine is a piece of GAGH! I need dual Pentium processors if I am to do battle with this code! ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Validating user-input to be inserted in regular expressions
On Jan 25, 2005, at 8:48 AM, Vincenzo Ciancia wrote: Vincenzo Ciancia wrote: Should I find every possible character in the documentation for regular expressions? Is the answer trivial? I checked the manual and the FAQ, and googled for the answer, but I didn't find it. Is there a more appropriate place where I can ask my question? Yes, this is a fine place to ask your question. Sometimes it does take a day or two to get an answer. Will quote_literal do what you want? http://www.postgresql.org/docs/8.0/interactive/functions-string.html Search on that page for quote_literal. Sean ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Object Relational, Foreign Keys and Triggers
On Mon, 24 Jan 2005, Alex Turner wrote: Insert fails with a foreign key constraint error because entity_phone points to entity, not person, and the rows aren't physicaly in entity, they are in person. Two questions: 1) Why can't we make this work the 'right' way - not doing so either breaks OO or brakes RDBMS. 1)a) Whats the point of an RDBMS if you can't specify foreign keys that work because you choose to use OO features (I somewhat appreciate that there is a trigger inheritance problem, can't we just define the rules and order of precident and solve it)? There are multiple problems involved mostly due to the fact that inheritance really need alot of work. For example, the actual implementation of the schema you gave has no interlock to prevent duplicate rows in person and entity (or another entity subclass). The primary key implementation also only guarantees local uniqueness. Inheritance really needs some developers who care strongly about it. 2) Whats the best way to manage this with triggers. Obviously one can create a trigger on entity and on person for delete so that it removes corresponding rows in entity_phone. But whats the best way to create a trigger that ensures that entity_ids that are used in entity_phone exist in entity and it's subtables thats fast. You could do: There's been discussion about this in the past, so you can get details from the archives, but using a separate table to store the ids with triggers between entity and person and the new table which manage the id list has been proposed as a workaround. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] EMBEDDED PostgreSQL
The trick is making database administration invisible to the user. Since Firebird requires no administration, it's easy. The single file database architecture in Firebird is also easy since you generally have only one drive. The only administration item presented to the user should be a dialog that displays available backup media, the databases (user named) on the media, and an archive and restore button. Supporting flash keys and CD burners as archive devices is what Martha would definitely call a Good Thing. They usually have enough space for a compressed database used by a workstation application without splitting, though you can support that too (e.g. Please insert disk two). Rick Christopher Browne [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent by: cc: [EMAIL PROTECTED]Subject: Re: [GENERAL] EMBEDDED PostgreSQL tgresql.org 01/25/2005 08:44 AM Martha Stewart called it a Good Thing when [EMAIL PROTECTED] (Tope Akinniyi) wrote: If I may ask, is there any plan for embedded PostgreSQL database as we have it in Firebird database? In Firebird embedded a compact engine of the database that can only accept connections from localhost (127.0.0.1) and easily distributable with single user applications exists. You can do that with PostgreSQL by configuring pg_hba.conf to only accept connections from localhost. And there is no problem with the notion of creating a database in a local directory. None of this requires any change. -- output = reverse(moc.liamg @ enworbbc) http://www3.sympatico.ca/cbbrowne/postgresql.html Signs of a Klingon Programmer #11: This machine is a piece of GAGH! I need dual Pentium processors if I am to do battle with this code! ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Object Relational, Foreign Keys and Triggers
On Tue, Jan 25, 2005 at 08:36:53AM -0500, Alex Turner wrote:
Actualy max() works just fine. It's not the solution I use in the
middle tier, but it works for a functional example. both max() and
currval() are bad because they can cause a race condition where the
sequence has been incremented by another thread. It's always better
to get nextval('sequence') and store it in a local var, then use it in
the main insert and corresponding sub-inserts.
Like I said, read the docs. currval was explicitly created to avoid the
race condition. It gives you the last number handed out in *this*
connection. It's also a lot faster than max. So different connections
get a different currval() and you get an error if you've not called
nextval() in the current connection (it works across transactions).
Storing in a var works too, but currval is totally safe.
Have a nice day,
On Tue, 25 Jan 2005 09:23:31 +0100, Martijn van Oosterhout
kleptog@svana.org wrote:
On Mon, Jan 24, 2005 at 07:22:32PM -0500, Alex Turner wrote:
I am facing the classic pgsql ORDBMS problem:
snip
Why are you using MAX()? That won't work at all. Perhaps you need to
look up the documentation for nextval and currval. In particular, that
second query should be:
insert into entity_phone select currval('entity_id_seq'),'610 495 5000';
Also, I'm not sure if inheritance works quite the way you think in the
example you give, though other people may correct me on that.
Hope this helps,
--
Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/
Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.
---(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
--
Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/
Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.
pgp4GtQKl0WML.pgp
Description: PGP signature
Re: [GENERAL] EMBEDDED PostgreSQL
On Jan 25, 2005, at 8:44 AM, Christopher Browne wrote: You can do that with PostgreSQL by configuring pg_hba.conf to only accept connections from localhost. And there is no problem with the notion of creating a database in a local directory. None of this requires any change. But on Windows 8.0 you can't run the postmaster with an administrative account, correct? I really wish this was configurable in the PostgreSQL settings (of course, defaulting to the way it is now). John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Extended unit
# [EMAIL PROTECTED] / 2005-01-25 10:40:15 +0100: I add use in my time in University some software that use extended type. For each variable, we define the mandatory classic type as integer, float, double array of. And we define an optional extended type as the unit in the MKSA system (Meter, Kilogram, Second, Ampere) or any other unit we would have previously define (eg. Currency). This extended type was wonderful, because there was warning/error if extend type does not match in any computation: you can not add apple to orange. I would appreciate to have such system in PostgreSQL. Is CREATE TYPE what you're looking for? http://www.postgresql.org/docs/8.0/static/sql-createtype.html -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] software development solution
(Sorry I'm not subscribed, too much mails per day) Hi list, I have to develop a solution for one of my friend who has several shops that he wants to centralize the gestion and remote monitor: * DB will be PostgreSQL (with as many as possible stored procs) * Access *has* to be through a browser (https), in order to provide world-wide availability. Functions will be close to a regular gestion software, except for accounting, which will not be included. My problems are: I'm not a very skillfull programmer (have done some C, Visual Basic, HTML, scripting, read a bit about PHP) AND I must go fast. So, what is your advise about an eventual IDE, or a set of powerfull programming tools and easy to use [PLS *not* Zope, the docs are terrible!] Thanks in advance, Jean-Yves Barbier ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] What is the format of 'binary' data in the postgresql client/server protocol version 3
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 O, I C -- and agree! Unfortunately, though, I don't see many people jumping at the chance to translate all of this open source code into a more readable language (such as Pascal or Ada). And I haven't found (or written) that English compiler I've been wanting yet although some of the Xtalk languages (HyperTalk, Transcript, etc.) get rather close sometimes, depending on what you are trying to do. Hmm... Actually, now that I google it again: http://www.iagora.com/~espel/pleng On Jan 24, 2005, at 7:41 PM, Eric Merritt wrote: (There's still a strong streak of use the source, Luke in this community, if you hadn't noticed...) Yea, but its all C and C hurts my eyes. ;) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster - --- Frank D. Engel, Jr. [EMAIL PROTECTED] $ ln -s /usr/share/kjvbible /usr/manual $ true | cat /usr/manual | grep John 3:16 John 3:16 For God so loved the world, that he gave his only begotten Son, that whosoever believeth in him should not perish, but have everlasting life. $ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (Darwin) iD8DBQFB9mE57aqtWrR9cZoRAsPyAJ9LOY4Wg6RDfT8P7365Zo4Kj8V+QACfej/t fHowlts3f4OQ7vuSJefUF10= =ioBA -END PGP SIGNATURE- ___ $0 Web Hosting with up to 120MB web space, 1000 MB Transfer 10 Personalized POP and Web E-mail Accounts, and much more. Signup at www.doteasy.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Multiline plpython procedure
On Fri, 21 Jan 2005, Martijn van Oosterhout wrote: On Fri, Jan 21, 2005 at 12:02:09PM +0100, Marco Colombo wrote: On Fri, 21 Jan 2005, Greg Stark wrote: I don't think it's reasonable for pg_dump to think about converting data from one language to another. It's important for pg_dump to restore an identical database. Having it start with special case data conversation from one flavour to another seems too dangerous. Makes no sense. pg_dump already make a lot of conversions: from internal representation (which may be platform dependent) to some common format, say text. It's just multi-line text which is a hard to deal with, because there _no_ single format for it. pg_dump may just choose one format, and stick with it. Every dump/restore will work. You may have trouble editing a text dump, but that's another matter. BTW, what pg_dump does on windows? I mean with -F p. Does it produce a text file with CRNL line seperator? What happens if you feed that file to psql on a Unix box? Ah, but you see, looking at it from your point of view, pg_dump doesn't interpret text strings. For example, the python script in a function is a opaque string. Not multiline, nothing. All postgresql does is pass that block of opaque data to the interpreter for that language. pg_dump dumps that opaque data into the output, and the CREATE FUNCTION dumps that opaque data back into the system tables. Postgresql doesn't understand python any more or less than perl, tcl, R or any other language. I was referring to psql output in general. E.g. (comments stripped): CREATE TABLE t2 ( f1 text ); COPY t2 (f1) FROM stdin; test1 test2 test3 \. This dump, produced on Unix, will have lines separated by \n. What does the same dump produced on Windows look like? If it's \n separated, it's not editable (natively) on Windows. Which is fine to me, we just defined pg_dump textual output to be \n terminated, always. Or, it's \r\n terminated. If so, how would it be to restore it on a Unix box (with psql -f). Now, if the data contains a \r I think it shows like that, escaped. Whether intended or not, that's the only thing that saves us (note that there's no need to escape a bare \r in Unix). The argument here is that basically this opaque data has different meanings for Python on windows and Python on unix. You can't make any special cases because I can rename plperl.so to plpython.so (or vice-versa) the opaque data won't be passed to the interpreter that you'd expect from looking at the definition. I'm for defining a format used by PostgreSQL, and force the python parser into accepting it on all platforms. That is, let's set the rule that python programs to be embedded into PostgreSQL use \n as line termination. Wouldn't that disadvantage non-unix pl/python users, whose python functions would have to be converted at run-time to conform to the local text format. With the extra bummer that the resulting string may not be the same size either. Remember, postgresql uses the standard shared library for the language on the platform, it doesn't build its own. But sure, preprocessing the source at run-time seems to be the only realistic solution without a change to the interpreter. Yeah. My fav. solution is to convert the string to platform format before passing it to the parser. See the martian example. Think of this: tomorrow we meet people from Mars. One of them really likes PostgreSQL, and ports it to their platform. Being a martian platform, it uses a different text file format. Line separator there is the first 1000 snip Spurious argument. You're assuming Martians would use ASCII to write programs without using one of the two defined line-ending characters. If they were smart they'd simply use a character set which doesn't have the ambiguity. If they even use 8-bit bytes. An ASCII C compiler won't compile EBCDIC source code either, but nobody thinks that's unreasonable, probably because nobody uses EBCDIC anymore :). You missed the point. Charset has nothing to do with the issue. While you can handle both at the same time, they are unrelated. Line separator is not dictated by the charset, only by the platform. \r\n or \n or \r for line termination is _not_ defined by ASCII. The _same_ ASCII textfile looks differently when looked in binary mode on various platforms. The point was: what if someone introduces another platform with yet-another-line-termination-standard? It's unlikely, just like martians. But it makes you realize that conversion is the job of the software that handles inter-platform communication (much like FTP). No-one is complaining about the use of line-ending characters, they could have said that you need a semi-colon to seperate lines. The problem is that it's *not consistant* across platforms. Have a nice day, What about C? How about fopen(afile, r) in C? Is it portable? Or should you use: fopen(afile, rb)? Define consistant across platforms here. If you use rb, your program will be consistant in that with the same _binary_ input,
Re: [GENERAL] How are foreign key constraints built?
On 1/23/05 1:01 PM, Tom Lane [EMAIL PROTECTED] wrote: If you like you can try the operation with set enable_seqscan = off, but I bet it will take longer. Ouch! That was a big mistake. Since inquiring minds want to know, I decided to give that a try. The expected outcome is to beat the heck out of the index disks as it read one index and referenced the other to see if the value existed. What appears to have happened is that it went through the same process as before, but read each data record via the index. It still created all the pgsql_tmp files, the data disk was still the heaviest hit (expected no or little access there), and it beat the living daylights out of my swap - pageins/outs like crazy. The I/O on the index disks was negligible compared to the data and swap disks. I won't try that again... Wes ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Cube
Hi, I have just installed V 8.0 on XP and I have discovered some interesting functions related to cubes and crosstabs. Navigating on the site I was not able to find any information/documentation. Please help. Thank you Regards Fabrizio ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pg_restore
That worked thanks. Just can not use the DBFile on windows. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] [PERFORM] DWH on Postgresql
Cross-posting to GENERAL for additional comment. Matt Casters wrote: Hi, I have the go ahead of a customer to do some testing on Postgresql in a couple of weeks as a replacement for Oracle. The reason for the test is that the number of users of the warehouse is going to increase and this will have a serious impact on licencing costs. (I bet that sounds familiar) We're running a medium sized data warehouse on a Solaris box (4CPU, 8Gb RAM) on Oracle. Basically we have 2 large fact tables to deal with: one going for 400M rows, the other will be hitting 1B rows soon. (around 250Gb of data) I have heard of databases larger than 1TB on PostgreSQL. Don't have much experience with them. but here are thoughts that come to mind. My questions to the list are: has this sort of thing been attempted before? If so, what where the results? If you search the archives (of the General list, I think) and you will be able to find people talking about databases much larger than this. More look what PostgreSQL can do rather than I need help. I've been reading up on partitioned tabes on pgsql, will the performance benefit will be comparable to Oracle partitioned tables? I am not aware of any data to base such a comparison on. What are the gotchas? A few I can think of: Cross-table indexes don't really work for constraing purposes, so you need to assume that only one table will be actively getting inserts/updates. Secondly, you will probably need to consider the level of transparency you need. If you need more transparency, you can do it with views, rules, etc. (or simply having on insert rules on your base table and inheriting new tables from it regularly). Also, I have seen posts in the past regarding performance issues specific to Solaris. You may want to research this too. Should I be testing on 8 or the 7 version? 8. Has better cache management, meaning will likely perform better. Hope this helps. It is not a typical question on the list, but if you start running into issues, this is a good list to ask question on :-) Best Wishes, Chris Travers Metatron Technology Consulting begin:vcard fn:Chris Travers n:Travers;Chris email;internet:[EMAIL PROTECTED] x-mozilla-html:FALSE version:2.1 end:vcard ---(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: [GENERAL] Validating user-input to be inserted in regular expressions
Sean Davis wrote:
Yes, this is a fine place to ask your question.Sometimesitdoestake
a day or two to get an answer.Willquote_literaldowhatyouwant?
http://www.postgresql.org/docs/8.0/interactive/functions-string.html
Search on that page for quote_literal.
Thank you for your answer. Unfortunately quote_literal is not what I am
looking for, in fact it quotes special characters in the sense of strings,
not in the sense of regular expressions. Here's some example to explain my
problem a little better:
I would like to select strings that begin with 'a.', so I do NOT want the
following (suppose 'a.' is generic user input)
relfs=# select true where 'aa' ~ ('a.'||'.*');
bool
--
t
I could as well use 'a\\.', but what characters should I escape? Surely
$^+.*[] and possibly others. The function quote_literal does:
relfs=# select true where 'aa' ~ (quote_literal('a.')||'.*');
bool
--
(0 righe)
but it's mere illusion :) In fact we have:
relfs=# select true where '\'aa\'' ~ (quote_literal('a.')||'.*');
bool
--
t
Thanks for any suggestions
Vincenzo
--
Please note that I do not read the e-mail address used in the from field but
I read vincenzo_ml at yahoo dot it
Attenzione: non leggo l'indirizzo di posta usato nel campo from, ma leggo
vincenzo_ml at yahoo dot it
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Data entry - forms design or other APIs etc. - what is there?
Chris Green wrote: I'm working on an application for my own use and have been using knoda as a front end to postgresql but I need a bit more power and flexibility. I'm quite happy to get into relatively serious programming as I am a C++/Java programmer, this accounting package is rather out of my normal line of work though. Since You think about writing Your own app, did You had a look at Qt ? It includes a SQL module with the capability to connect to various DB's (PostgreSQL, MySQL, Oracle, MSSQL and others ) With designer You can build the layout of Your application where You can use data aware widgets for display and/or editing. Ulrich Schwab ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] dbf2pg error
Greetings, I've run into a problem with the contributed program dbf2pg. It runs without error, creating a table with the correct columns but only the varchar columns have values: All of the floats are blank. Here is how I've run it: dbf2pg -t zip2 -c -d CBPdata DETL.DBF I'm running postgresql 7.4.5 under SuSE 9.0. Thanks in advance for any assistance. -Andy. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Extended unit
Is CREATE TYPE what you're looking for? http://www.postgresql.org/docs/8.0/static/sql-createtype.html No. I'll try to give an exemple of what I want: I suppose I have the following table CREATE TABLE experiment ( distanceDOUBLE, timeDOUBLE, speed DOUBLE ); I can do the following query : SELECT distance+time+speed FROM experiment; This is a valid SQL query, but there is no physical meaning. Now, I suppose I have extended type. So the table is: CREATE TABLE experiment ( distance DOUBLE(m1), time DOUBLE(s1), speed DOUBLE(m1s-1), ); distance is of type DOUBLE and of unit METER time is of type DOUBLE and of unit SECOND speed is of type DOUBLE and of unit METER/SECOND SELECT distance+time+speed FROM experiment; Would throw an error : Incompatible unit M1, S1, M1S-1 SELECT distance/time+speed FROM experiment; would succeed (obviously). It may be possible to mess with domain/type to achieve a draft. But I pretty sure that we need extend the type system to achieve it cleanly. Cordialement, Jean-Gérard Pailloncy ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Restaurando una base de datos
Hola a todos Hace un rato baje la version 8.0 de para win, la instale y la verdad no se por donde hacer para poder restaurar el back up de mi bd; he ingresado a la opcion psql on template 1 y alli escribo psql dbname c:\infile.bk y no ejecuta el restore. Alguien sabe como debo hacerlo gracias por la ayuda Alejandro
[GENERAL] Recursive queries
Are there any plans on implementing support for recursive queries in postgresql in the near future? If so: When? I can see there has been some discussion on the subject in the developer-group for quite some time ago, but aparently all thoughts of recursive queries has been stalled. :-( Regards ---(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
[GENERAL] postgresql.conf - add_missing_from
postgres does not seem to pick up the following parameter in the postgresql.conf add_missing_from = false Setting the parameter via psql, functions properly SET add_missing_from TO FALSE Using: winxp, Postges 8.0 (note: I did restart the service after updating the parameters in postgresql.conf) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] My problem upgrading to 8.0.0
I upgraded from 7.1.3 to 8.0.0 and I find that almost every one of
my scripts which parse psql output are broken. This is because of
the array output format change that, I believe, happened around 7.2.
I been using awk (and even /bin/sh) to parse psql's output and for 7.1.3
I was able to use three re's to split a character array into it's
components:
sub( /^{/, , $n )
sub( /}$/, , $n )
split( $n, address, /,/ )
This will work for every case but one: when an element ended with
the two characters doublequote-comma. As I know that none of my
data contains this, everything worked.
Now, elements in array output are only quoted if necessary. This
means that re's cannot be used! I have to write a character-by-character
parser in awk, sh (probably impossible), etc. and modify every script
that parses array output.
I didn't see any per-array option to set the element separator
character; it looks like I would have to change it for the database
as a whole.
I suppose I could write a post-processor in C and filter all the output
of psql thru that; perhaps such a thing already exists?
I looked at postgresql's source -- I guess I could force all elements
to be quoted again for my installation.
I can sort of get what I want by:
select '{' || array_to_string(v,',') || '}' from ...
but this has many more cases where the data within the array affects
parsing. Array_to_string removes all backslashes so now any string
containing the three characters , will be inappropriately split.
I don't think any of my data has this, but it would make the scripts
much more vulnerable to breakage in the future.
I am hoping that this issue came up back when the array output was
changed. I could not find any reference on Google or postgresql.org
but I probably am not phrasing my queries well enough. If this was
an issue when array output changed, can anyone point me at some possible
solutions that hopefully don't require me to rewrite all of my
scripts? Or techniques used to parse psql output containing array
output using awk or sh?
Thanks.
Dave Bodenstab
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Backup DLL
It sounds like you are trying to automate administrative tasks. If that's what you are trying to do, you may be well advised to use any one of the Unix/Linux/BSD operating systems: They all have far superior and simpler task automation tools such as cron and fully functional shells, something which windows servers desperately lack natively. Terry FielderAssociate Director Software Development and DeploymentGreat Gulf Homes / Ashton Woods Homes[EMAIL PROTECTED]Fax: (416) 441-9085 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Tope AkinniyiSent: Tuesday, January 25, 2005 7:25 AMTo: pgsql-general@postgresql.orgSubject: [GENERAL] Backup DLL Hi, Is anyone aware of a standalone DLL that can be integrated with applications for pg_dump and restore without resorting to asking clients to use tools like pgAdmin for their backup and restore cores? Thanks ALL-NEW Yahoo! Messenger - all new features - even more fun!
[GENERAL] PostgreSQL 8.0 for RH AS
Hello I wanted to ask if anyone knows if 8.0 is available as a RH AS package? Or perhaps if the rhel-es.3.0 would work on a RH AS system? I'm not much of a RH guru as I was raised on Debian. Thanks in advance for any help you can give. F. E. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] PostgreSQL 8.0 for RH AS
I'd doubt that the RHES3 RPMs work on RHAS-2.1. The RH9 RPMs might work ok on RHAS-2.1, but i've not tested them. On Tue, 25 Jan 2005 10:57:13 -0500 (EST), Fabio Esposito [EMAIL PROTECTED] wrote: Hello I wanted to ask if anyone knows if 8.0 is available as a RH AS package? Or perhaps if the rhel-es.3.0 would work on a RH AS system? I'm not much of a RH guru as I was raised on Debian. Thanks in advance for any help you can give. -- ~ L. Friedman[EMAIL PROTECTED] LlamaLand http://netllama.linux-sxs.org ---(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: [GENERAL] Extended unit
Martijn van Oosterhout wrote: On Tue, Jan 25, 2005 at 10:40:15AM +0100, Pailloncy Jean-Gerard wrote: This extended type was wonderful, because there was warning/error if extend type does not match in any computation: you can not add apple to orange. I think it's a wonderful idea. You could use a similar mechanism to implement: - Currencies (so you can't add dollars to pounds) - Timezone aware timestamps (so a time in Australia looks differet from a time in Europe) Probably much more. Indeed, you could even add a way to convert between different types if they are in the same categories; for example convert between dollars and euro's or between degrees Celsius and degrees Fahrenheit (that's a trickier one, the shell command 'units' returns wrong results there). Of course, it's still not possible to add dollars and degrees Fahrenheit... (hence the category concept I mentioned) You could even determine that if you divide a quantity in meters by a quantity in seconds that you're talking about a speed... I think there are quite a few people on this planet who would be happy about that. OTOH, it's probably not that a good idea to add all kinds of complicated (read: processor intensive) math to a database engine. Just my 0.02 Euro. -- Regards, Alban Hertroys MAG Productions P: +31(0)53 4346874 F: +31(0)53 4346876 E: [EMAIL PROTECTED] W: http://www.magproductions.nl ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] difficult JOIN
Hi, i have the following SQL-Problem: We are using 2 tables. The first, called plan, is holding planned working times for employees per tour: plan.id_tour plan.id_employee plan.begin_time plan.end_time The second table 'work' stores the actual worked times for employees per tour: work.id_tour work.id_employee work.begin_time work.end_time Employees can be multiple times assigned to one tour. One record will be created for every assignment. They can also work multiple times in one tour. Now i wanna merge this infos into one report. I wanna join the first plan entry for one employee in one tour with the first work entry for one employee in one tour and so on. How can i obtain that? A simply USING(id_tour, id_employee) -JOIN will not doit. Thanks for any hints, Thomas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Cube
On Mon, 24 Jan 2005 [EMAIL PROTECTED] wrote: Hi, I have just installed V 8.0 on XP and I have discovered some interesting functions related to cubes and crosstabs. Navigating on the site I was not able to find any information/documentation. Please help. Why not use search engine ? For example, http://www.pgsql.ru/db/pgsearch/index.html?q=crosstabs Thank you Regards Fabrizio ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] PostgreSQL 8.0 for RH AS
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Tue, 25 Jan 2005, Fabio Esposito wrote: I wanted to ask if anyone knows if 8.0 is available as a RH AS package? Or perhaps if the rhel-es.3.0 would work on a RH AS system? I'm not much of a RH guru as I was raised on Debian. If it's RHAS 3.0 (not 2.1), 3.0 RPMs will probably work. Otherwise, you can download the related SRPM (http://www.postgresql.org/download/mirrors-ftp?file=binary%2Fv8.0%2Flinux%2Fsrpms%2Fredhat%2Frhel-es-3.0%2Fpostgresql-8.0.0-1PGDG.src.rpm) and run the following command: rpmbuild -bb --target i686 postgresql-8.0.0-1PGDG.src.rpm (--target i686 is intentional). This command will put the built RPMs on /usr/src/redhat/RPMS/i686 (or to i386, if you don't use --target i686). Then you can install any RPMs you want. BTW, we have a mailing list on RPMs: http://lists.pgfoundry.org/mailman/listinfo/pgsqlrpms-general Regards, - -- Devrim GUNDUZ devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.tdmsoft.com http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQFB9nM5tl86P3SPfQ4RAnvuAJ98ABRh6Hj0Qb12ZcrEbAcMmrmfNwCfRjzr oW1++tx1lP9rI4x57VseiJg= =hy2f -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] difficult JOIN
Hi, i have the following SQL-Problem: We are using 2 tables. The first, called plan, is holding planned working times for employees per tour: plan.id_tour plan.id_employee plan.begin_time plan.end_time The second table 'work' stores the actual worked times for employees per tour: work.id_tour work.id_employee work.begin_time work.end_time Employees can be multiple times assigned to one tour. One record will be created for every assignment. They can also work multiple times in one tour. Now i wanna merge this infos into one report. I wanna join the first plan entry for one employee in one tour with the first work entry for one employee in one tour and so on. How can i obtain that? A simply USING(id_tour, id_employee) -JOIN will not doit. Thanks for any hints, Thomas ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Object Relational, Foreign Keys and Triggers
Am Dienstag, den 25.01.2005, 08:36 -0500 schrieb Alex Turner:
Actualy max() works just fine. It's not the solution I use in the
middle tier, but it works for a functional example. both max() and
currval() are bad because they can cause a race condition where the
sequence has been incremented by another thread. It's always better
to get nextval('sequence') and store it in a local var, then use it in
the main insert and corresponding sub-inserts.
No, thats wrong. If you read the documentation again on that matter, you
will see. currval() works on at least one nextval() in the connection
you are running and therefore keeps showing the last result of nextval()
in this very connection - no matter what other connections/sessions do.
Thats the whole point of sequences anyway.
HTH
Tino
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] Validating user-input to be inserted in regular expressions
On Tue, Jan 25, 2005 at 04:28:06PM +0100, Vincenzo Ciancia wrote:
Thank you for your answer. Unfortunately quote_literal is not what I am
looking for, in fact it quotes special characters in the sense of strings,
not in the sense of regular expressions.
It sounds like you're looking for the equivalent of Perl's quotemeta:
% perl -le 'print quotemeta abc.*'
abc\.\*
I'm not aware of any such function in PostgreSQL, but you could use
a PL/Perl function that simply calls quotemeta:
CREATE FUNCTION quotemeta(text) RETURNS text AS '
return quotemeta $_[0];
' LANGUAGE plperl IMMUTABLE STRICT;
SELECT quotemeta('abc.*');
quotemeta
---
abc\.\*
(1 row)
There might be differences between PostgreSQL's and Perl's regular
expression engines, but perhaps not enough to matter in this case.
I expect it would be easy to add such a function to PostgreSQL, so
consider suggesting it to the developers or even writing it yourself
and submitting a patch.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
[GENERAL] Postgresql, SQL server and Oracle. Please, help
Hi, I was wondering where can I get documentation and especially examples on PgSQL? I've done a lot of work with Ms SQL server and need to rewrite a number of stored procedures in PgSQL. Is there a tutorial? What is the best way to start? Can I read an Oracle book? How close is Oracle to Postgresql? Thanks ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Extended unit
On Tue, Jan 25, 2005 at 05:17:21PM +0100, Alban Hertroys wrote: Martijn van Oosterhout wrote: I think it's a wonderful idea. You could use a similar mechanism to implement: - Currencies (so you can't add dollars to pounds) - Timezone aware timestamps (so a time in Australia looks differet from a time in Europe) Probably much more. Indeed, you could even add a way to convert between different types if they are in the same categories; for example convert between dollars and euro's or between degrees Celsius and degrees Fahrenheit (that's a trickier one, the shell command 'units' returns wrong results there). I think it would be fabulous if it could be implemented as a generic extension to the type system, because I'm sure there are even cooler uses than what we are thinking of here... OTOH, it's probably not that a good idea to add all kinds of complicated (read: processor intensive) math to a database engine. We're talking here about a database with indexes to speed up intersection tests for arbitrary polygons, extensions to handle encryption, full text indexing and even builtin XML support. I think arguing excessive use of CPU cycles is a bit late :) I think the argument is that if it helps people do their work correctly then it's worth supporting. As long as it doesn't hamper anybody else. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpY36xGOvWXX.pgp Description: PGP signature
Re: [GENERAL] Recursive queries
On Mon, Jan 24, 2005 at 05:27:46PM +0100, tmp wrote: Are there any plans on implementing support for recursive queries in postgresql in the near future? If so: When? I can see there has been some discussion on the subject in the developer-group for quite some time ago, but aparently all thoughts of recursive queries has been stalled. :-( What do you mean by resursive queries? A query can have a subquery which calls a function which executes another query. That counts as recursion in my book. What type of recursion are you thinking of? Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpxbW9Ep7uJr.pgp Description: PGP signature
Re: [GENERAL] Search for restricting foreign keys
Michael Fuhr wrote: On Mon, Jan 24, 2005 at 08:35:45PM -0800, Benjamin Smith wrote: Is there a way in PG 7.3, given a field, to find out what other tables records are linked to it via a foreign key? The pg_constraint table contains, among other things, foreign key constraints. By querying it and joining it with pg_attribute, pg_class, and pg_namespace, you could get a list of tables and columns that have foreign key constraints on the given table and column; from that you could build queries to find out which rows in those tables match the given value. You could wrap all this code in a set-returning function. I just needed such a function yesterday, and wrote one. Here it is, use it for whatever you want ;-) create type foreignkey (, table_referenced as regclass, fields_referenced as varchar[], table_referencing as regclass, fields_referencing as varchar[] ) ; create or replace function f_get_pks(regclass) returns foreignkey as ' select pg_constraint.confrelid::regclass as table_referenced, array(select pg_attribute.attname from pg_catalog.pg_attribute where pg_attribute.attrelid = pg_constraint.confrelid and pg_attribute.attnum = ANY(pg_constraint.confkey) order by alienkey.f_array_pos( pg_constraint.confkey, pg_attribute.attnum ) )::varchar[] as fields_referenced, pg_constraint.conrelid::regclass as table_referencing, array(select pg_attribute.attname from pg_catalog.pg_attribute where pg_attribute.attrelid = pg_constraint.conrelid and pg_attribute.attnum = ANY(pg_constraint.conkey) order by alienkey.f_array_pos( pg_constraint.confkey, pg_attribute.attnum ) )::varchar[] as fields_referencing from pg_catalog.pg_constraint where pg_constraint.confrelid = $1 and pg_constraint.contype = 'f' ' language 'sql' stable ; Of course this could be a view too - just remove the where-clause containing = $1, and wrap it in a create view statement. greetings, Florian Pflug smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Postgresql, SQL server and Oracle. Please, help
On Tue, Jan 25, 2005 at 12:35:50PM -0500, Nadia Kunkov wrote: Hi, I was wondering where can I get documentation and especially examples on PgSQL? I've done a lot of work with Ms SQL server and need to rewrite a number of stored procedures in PgSQL. Is there a tutorial? What is the best way to start? Can I read an Oracle book? How close is Oracle to Postgresql? Not specifically what you want, but there's a book with the imaginative name of SQL by Chris Fehily, Peachpit Press, ISBN 0-201-11803-0, meant as a beginner's guide, but which also includes all the variations in SQL for PostgreSQL, Oracle, and MS SQL Server. That is why I got it, and it is pretty handy for me, a non-guru. -- ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._. Felix Finch: scarecrow repairman rocket surgeon / [EMAIL PROTECTED] GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933 I've found a solution to Fermat's Last Theorem but I see I've run out of room o ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] EMBEDDED PostgreSQL
[EMAIL PROTECTED] (John DeSoi) writes: On Jan 25, 2005, at 8:44 AM, Christopher Browne wrote: You can do that with PostgreSQL by configuring pg_hba.conf to only accept connections from localhost. And there is no problem with the notion of creating a database in a local directory. None of this requires any change. But on Windows 8.0 you can't run the postmaster with an administrative account, correct? I really wish this was configurable in the PostgreSQL settings (of course, defaulting to the way it is now). I haven't the foggiest idea what you can do on Windows 8.0; I thought they called it Windows XP or Windows 2000. I'm making the Unix-flavoured assumptions that it's cheap and easy to create an extra directory and to spawn an extra process for a postmaster in a user's own process space. That may vary somewhat for the more VMS-like model of Windows NT... -- cbbrowne,@,ca.afilias.info http://dev6.int.libertyrms.com/ Christopher Browne (416) 673-4124 (land) ---(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: [GENERAL] Restaurando una base de datos
On Jan 25, 2005, at 10:24 AM, alejandro fernandez wrote: Hace un rato baje la version 8.0 de para win, la instale y la verdad no se por donde hacer para poder restaurar el back up de mi bd; he ingresado a la opcion psql on template 1 y alli escribo psql dbname c:\infile.bk y no ejecuta el restore. intente: psql dbname -f c:\infile.bk En Windows pienso que hay un problema usando y John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] ODBC Driver Issue (possibly OT - wrong list?)
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I suppose this should really be on the ODBC driver list, but as I spend
so much time sorting through mail already, I'm rather hoping not to
need to subscribe to yet another list for this one issue...
I'm trying to use a commercial handheld sync program called Visual CE
in conjunction with PostgreSQL. Visual CE works using an ODBC driver
(windows only) and I tried using the one which comes with the
PostgreSQL 8 install package (Windows 2000, btw).
When I try to sync, I get an error message from the ODBC driver. I
turned on the log file feature; I got the log given toward the end of
this message.
I did note a similar issue mentioned on the ODBC list archives while
searching via google, but the only thing which seemed to be an answer
to the question was to turn on the Server side prepare option in the
ODBC driver settings. I tried this, and it didn't work. There also
seemed to be mention of a custom driver build out of CVS or something?
Can someone please help with this?
Thank you!
==log dump follows==
Global Options: Version='08.00.0004', fetch=100, socket=4096,
unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190
disable_optimizer=1, ksqo=1, unique_index=1,
use_declarefetch=0
text_as_longvarchar=1, unknowns_as_longvarchar=0,
bools_as_char=1 NAMEDATALEN=64
extra_systable_prefixes='dd_;', conn_settings=''
conn_encoding='OTHER'
conn=18429304, query=' '
conn=18429304, query='select version()'
[ fetched 1 rows ]
[ PostgreSQL version string = 'PostgreSQL 7.4.3 on
powerpc-apple-darwin7.5.0, compiled by GCC gcc (GCC) 3.3 20030304
(Apple Computer, Inc. build 1656)' ]
[ PostgreSQL version number = '7.4' ]
conn=18429304, query='set DateStyle to 'ISO''
conn=18429304, query='set geqo to 'OFF''
conn=18429304, query='set extra_float_digits to 2'
conn=18429304, query='select oid from pg_type where typname='lo''
[ fetched 0 rows ]
conn=18429304, query='select pg_client_encoding()'
[ fetched 1 rows ]
[ Client encoding = 'SQL_ASCII' (code = 0) ]
conn=18429304, query='set client_encoding to 'UTF8''
conn=18429304,
PGAPI_DriverConnect(out)='DSN=ESAInspections;DATABASE=ESAInspections;
SERVER=192.168.0.202;PORT=5432;UID=admin;PWD=x;ReadOnly=0;
Protocol=6.4;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;
ShowSystemTables=0;ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;
MaxVarcharSize=254;MaxLongVarcharSize=8190;Debug=1;CommLog=1;
Optimizer=1;Ksqo=1;UseDeclareFetch=0;TextAsLongVarchar=1;
UnknownsAsLongVarchar=0;BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;
ExtraSysTablePrefixes=dd_;;LFConversion=1;UpdatableCursors=0;
DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;
UseServerSidePrepare=1'
conn=18429304, query='select current_schema()'
[ fetched 1 rows ]
conn=18429304, query='select relname, nspname, relkind from
pg_catalog.pg_class c, pg_catalog.pg_namespace n where relkind in ('r',
'v') and nspname like 'public' and relname like 'realtors' and relname
!~ '^pg_|^dd_' and n.oid = relnamespace order by nspname, relname'
[ fetched 1 rows ]
conn=18429304, query='select * from realtors where 1=0'
[ fetched 0 rows ]
conn=18429304, query='select relname, nspname, relkind from
pg_catalog.pg_class c, pg_catalog.pg_namespace n where relkind in ('r',
'v') and nspname like 'public' and relname like 'SYWARE_SyncInfo' and
relname !~ '^pg_|^dd_' and n.oid = relnamespace order by nspname,
relname'
[ fetched 0 rows ]
conn=18429304, query='update realtors set OID = 0 where OID IS NULL'
conn=18429304, query='update realtors set OID = 0 where OID =
268435456'
conn=18429304, query='select * from realtors order by OID'
[ fetched 1 rows ]
STATEMENT ERROR: func=PGAPI_SetPos, desc='', errnum=10, errmsg='Only
SQL_POSITION/REFRESH is supported for PGAPI_SetPos'
-
hdbc=18429304, stmt=18457304, result=18456544
manual_result=0, prepare=0, internal=0
bindings=18450544, bindings_allocated=19
parameters=0, parameters_allocated=0
statement_type=0, statement='select * from realtors
order by OID'
stmt_with_params='select * from realtors order by
OID'
data_at_exec=-1, current_exec_param=-1, put_data=0
currTuple=0, current_col=-1, lobj_fd=-1
maxRows=0, rowset_size=1, keyset_size=0,
cursor_type=3, scroll_concurrency=1
cursor_name='SQL_CUR0119A2D8'
QResult Info
- ---
fields=18457264, manual_tuples=0,
backend_tuples=18460512, tupleField=18460512, conn=18429304
fetch_count=0, num_total_rows=1, num_fields=15,
cursor='(NULL)'
message='(NULL)', command='SELECT',
Re: [GENERAL] Extended unit
Martijn van Oosterhout kleptog@svana.org writes: We're talking here about a database with indexes to speed up intersection tests for arbitrary polygons, extensions to handle encryption, full text indexing and even builtin XML support. ... none of which require any extensions to the core type system. AFAICS this could easily be implemented as a user-defined type, along the lines of CREATE TYPE measurement AS (value double, units text); and if you want to constrain a particular column to contain only one value of units, use CHECK. The argument that we should extend the type system for this would become a lot more credible if there were a widely-used extension in existence for it to prove that there's sufficient demand. 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: [GENERAL] EMBEDDED PostgreSQL
On Jan 25, 2005, at 1:55 PM, Christopher Browne wrote: I haven't the foggiest idea what you can do on Windows 8.0; I thought they called it Windows XP or Windows 2000. Sorry, I meant version 8.0 of PostgreSQL on Windows (any variant it works on). I'm making the Unix-flavoured assumptions that it's cheap and easy to create an extra directory and to spawn an extra process for a postmaster in a user's own process space. That may vary somewhat for the more VMS-like model of Windows NT... In general, it should be possible to just copy the right files and do the same thing on Windows but the pgInstaller FAQ says == 2.3) Why do I need a non-administrator account to run PostgreSQL under? When a hacker gains entry to a computer using a software bug in a package, she gains the permissions of the user account under which the service is run. Whilst we do not know of any such bugs in PostgreSQL, we enforce the use of a non-administrative service account to minimise the possible damage that a hacker could do should they find and utilise a bug in PostgreSQL to hack the system. This has long been common practice in the Unix world, and is starting to become standard practice in the Windows world as well as Microsoft and other vendors work to improve the security of their systems. == Again, I think this is fine as the default, but it would be nice if it could be changed with a setting (rather than recompiling the source). Not all Windows users are dummies about security and need PostgreSQL to enforce security measures beyond those implemented on other platforms. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Recursive queries
Martijn van Oosterhout kleptog@svana.org writes: What do you mean by resursive queries? SQL99 WITH syntax. See the archives. Andrew Overholt did some work in this direction a year or so back, but didn't get real far ... regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Recursive queries
On Tue, Jan 25, 2005 at 07:58:33PM +0100, Martijn van Oosterhout wrote: On Mon, Jan 24, 2005 at 05:27:46PM +0100, tmp wrote: Are there any plans on implementing support for recursive queries in postgresql in the near future? If so: When? I can see there has been some discussion on the subject in the developer-group for quite some time ago, but aparently all thoughts of recursive queries has been stalled. :-( What do you mean by resursive queries? A query can have a subquery which calls a function which executes another query. That counts as recursion in my book. What type of recursion are you thinking of? The WITH clause in SQL2003 AFAIR (maybe earlier ones as well). -- Alvaro Herrera ([EMAIL PROTECTED]) Ellos andaban todos desnudos como su madre los parió, y también las mujeres, aunque no vi más que una, harto moza, y todos los que yo vi eran todos mancebos, que ninguno vi de edad de más de XXX años (Cristóbal Colón) ---(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
[GENERAL] Restore de Base de datos
Hola lista, le cuento que ya logre hacer el restore en postgres, tenia que modificar los codificadores y luego copiar elbackup a la carpeta bin luego desde una consola DOS ejecutar psql dbname -U username y cuando ingrese ejecutar\ibackupname gracias
Re: [GENERAL] Recursive queries
On Tue, Jan 25, 2005 at 08:24:54PM +0100, tmp wrote: What do you mean by resursive queries? A query can have a subquery which calls a function which executes another query. That counts as recursion in my book. What type of recursion are you thinking of? SQL:2003 defines a language construct for recursive queries (T131 and T132). What I ment with the question was: Will postgresql soon support a similar (or the same) construct? I don't have the SQL standard but I think you're referring to tables that join to themselves and you want to follow these links recursively. I don't think anybody has written the syntactic sugar, but someone did write a function that provides equivalent output. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgp2TAT21SbhZ.pgp Description: PGP signature
Re: [GENERAL] Extended unit
On Tue, Jan 25, 2005 at 02:31:40PM -0500, Tom Lane wrote: AFAICS this could easily be implemented as a user-defined type, along the lines of CREATE TYPE measurement AS (value double, units text); and if you want to constrain a particular column to contain only one value of units, use CHECK. I've tried this but I can't work out how to make it work. For composite types you can't specify input and output functions. It's all record_in but it's not working for me: # CREATE TYPE measurement AS (value float, units text); CREATE TYPE # select '(5,a)'::measurement; ERROR: Cannot cast type unknown to measurement # select measurement(5,'a'); ERROR: Function measurement(integer, unknown) does not exist Unable to identify a function that satisfies the given argument types You may need to add explicit typecasts # select cast( (5,'a') as measurement); ERROR: parser: parse error at or near as at character 22 # select cast( '5' as measurement); ERROR: Cannot cast type unknown to measurement This is 7.3 though, is it better in later versions? I can't find any examples anywhere. Composite types don't seems to be used much. However, it appears you could just update pg_type to change the input/output functions... The argument that we should extend the type system for this would become a lot more credible if there were a widely-used extension in existence for it to prove that there's sufficient demand. I guess it's mostly syntactic sugar, but it might normalize the varchar(n) and timestamp(n) format. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpGE5zZz1bUO.pgp Description: PGP signature
[GENERAL] Apparently I don't understand full outer joins....
I run this: select coalesce(a.n,0) as a, coalesce(b.n,0) as b, coalesce(a.s,b.s) as s from ( select 1 as n, 0 as s) a full outer join ( select 2 as n, 1 as s) b on a.s = b.s ... and get this: a | b | s ---+---+--- 1 | 0 | 0 0 | 2 | 1 (2 rows) Perfect! Now, I try to extend my understanding to 3 subselects: select coalesce(a.n,0) as a, coalesce(b.n,0) as b, coalesce(c.n,0) as c, coalesce(a.s,b.s,c.s) as s from ( select 1 as n, 0 as s) a full outer join ( select 1 as n, 1 as s) b full outer join ( select 2 as n, 2 as s) c on a.s = b.s and b.s = c.s and get a syntax error at the end of my query. Apparently what I'm trying to do doesn't make sense? Oh, this is on version 7.4, if that makes a difference. --- Ben Chobot Senior Technical Specialist, Washington Mutual 206-461-4005 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Apparently I don't understand full outer joins....
Your second example is breaking the syntax of from_item ( see http://www.postgresql.org/docs/7.4/static/sql-select.html ). Your join_condition has to be applied to the two from_items associated by join_type. I don't think multiple join_conditions can be applied sequentially the way you're trying to do it. You could probably create a nested structure, though. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Jan 25, 2005, at 2:29 PM, Ben wrote: I run this: select coalesce(a.n,0) as a, coalesce(b.n,0) as b, coalesce(a.s,b.s) as s from ( select 1 as n, 0 as s) a full outer join ( select 2 as n, 1 as s) b on a.s = b.s ... and get this: a | b | s ---+---+--- 1 | 0 | 0 0 | 2 | 1 (2 rows) Perfect! Now, I try to extend my understanding to 3 subselects: select coalesce(a.n,0) as a, coalesce(b.n,0) as b, coalesce(c.n,0) as c, coalesce(a.s,b.s,c.s) as s from ( select 1 as n, 0 as s) a full outer join ( select 1 as n, 1 as s) b full outer join ( select 2 as n, 2 as s) c on a.s = b.s and b.s = c.s and get a syntax error at the end of my query. Apparently what I'm trying to do doesn't make sense? Oh, this is on version 7.4, if that makes a difference. --- Ben Chobot Senior Technical Specialist, Washington Mutual 206-461-4005 ---(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
Re: [GENERAL] Apparently I don't understand full outer joins....
On Tue, Jan 25, 2005 at 12:29:07PM -0800, Ben wrote: select coalesce(a.n,0) as a, coalesce(b.n,0) as b, coalesce(c.n,0) as c, coalesce(a.s,b.s,c.s) as s from ( select 1 as n, 0 as s) a full outer join ( select 1 as n, 1 as s) b full outer join ( select 2 as n, 2 as s) c on a.s = b.s and b.s = c.s and get a syntax error at the end of my query. Apparently what I'm trying to do doesn't make sense? The ON clauses have to be attached directly to the outer joins. So you probably mean select coalesce(a.n,0) as a, coalesce(b.n,0) as b, coalesce(c.n,0) as c, coalesce(a.s,b.s,c.s) as s from ( select 1 as n, 0 as s) a full outer join ( select 1 as n, 1 as s) b on a.s = b.s full outer join (select 2 as n, 2 as s) c on b.s = c.s; Richard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Apparently I don't understand full outer joins....
On Tue, 25 Jan 2005, Ben wrote: I run this: select coalesce(a.n,0) as a, coalesce(b.n,0) as b, coalesce(a.s,b.s) as s from ( select 1 as n, 0 as s) a full outer join ( select 2 as n, 1 as s) b on a.s = b.s ... and get this: a | b | s ---+---+--- 1 | 0 | 0 0 | 2 | 1 (2 rows) Perfect! Now, I try to extend my understanding to 3 subselects: select coalesce(a.n,0) as a, coalesce(b.n,0) as b, coalesce(c.n,0) as c, coalesce(a.s,b.s,c.s) as s from ( select 1 as n, 0 as s) a full outer join ( select 1 as n, 1 as s) b full outer join ( select 2 as n, 2 as s) c on a.s = b.s and b.s = c.s and get a syntax error at the end of my query. Apparently what I'm trying to do doesn't make sense? Each outer join gets an on clause. You might want something like: select coalesce(a.n,0) as a, coalesce(b.n,0) as b, coalesce(c.n,0) as c, coalesce(a.s,b.s,c.s) as s from ( select 1 as n, 0 as s) a full outer join ( select 1 as n, 1 as s) b on (a.s=b.s) full outer join ( select 2 as n, 2 as s) c on b.s = c.s; ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Apparently I don't understand full outer joins....
Thanks guys, this works great. On Tue, 25 Jan 2005, Stephan Szabo wrote: Each outer join gets an on clause. You might want something like: select coalesce(a.n,0) as a, coalesce(b.n,0) as b, coalesce(c.n,0) as c, coalesce(a.s,b.s,c.s) as s from ( select 1 as n, 0 as s) a full outer join ( select 1 as n, 1 as s) b on (a.s=b.s) full outer join ( select 2 as n, 2 as s) c on b.s = c.s; ---(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
[GENERAL] Delete with a multi-column join?
Howdy! I apologize in advance for the ugly query I'm about to throw your way I need to delete some data from a table based on a multi-column join. Is there a better way to write this? delete from tbldata where unitID || '_' || variableID || '_' || cycleID in (select unitID || '_' || variableID || '_' || cycleID from temp_data_table) In SQL Server I would just write delete tblData from tblData a inner join temp_data_table b on a.unitID = b.unitID and a.variableID = b.variableID and a.cycleID = b.cycleID but that doesn't seem to be supported in postgres... Thanks in advance for you help! Leon ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Postgresql, SQL server and Oracle. Please, help
On Jan 25, 2005, at 12:35 PM, Nadia Kunkov wrote: Hi, I was wondering where can I get documentation and especially examples on PgSQL? I've done a lot of work with Ms SQL server and need to rewrite a number of stored procedures in PgSQL. Is there a tutorial? What is the best way to start? Can I read an Oracle book? How close is Oracle to Postgresql? Just posted moments ago for a different thread: On Jan 23, 2005, at 10:22 PM, Duffy House wrote: The first issue will be getting up to speed on PL/pgsql. Where can I find primer on PL/pgsql, with lots of examples? How silimar is PL/pgsql to PL/SQL under Oracle? The PostgreSQL documentation is the place to start: http://www.postgresql.org/docs/8.0/interactive/plpgsql.html The PostgreSQL distribution has a file with some examples in it: src/test/regress/sql/plpgsql.sql The pgEdit distribution (http://pgedit.com/download) has a fairly extensive plpgsql example for importing and analyzing web server logs. I'm not familiar with Oracle, but there is a porting section in the documentation: http://www.postgresql.org/docs/8.0/interactive/plpgsql-porting.html John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Extended unit
Martijn van Oosterhout kleptog@svana.org writes: On Tue, Jan 25, 2005 at 02:31:40PM -0500, Tom Lane wrote: AFAICS this could easily be implemented as a user-defined type, along the lines of CREATE TYPE measurement AS (value double, units text); and if you want to constrain a particular column to contain only one value of units, use CHECK. I've tried this but I can't work out how to make it work. For composite types you can't specify input and output functions. No, but as of 8.0 you don't really need them, assuming that you don't mind some parentheses around your output. regression=# CREATE TYPE measurement AS (value float, units text); CREATE TYPE regression=# select cast( (5,'a') as measurement); row --- (5,a) (1 row) Or you can implement it as a scalar type if you really want to define your own I/O functions. 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: [GENERAL] EMBEDDED PostgreSQL
John DeSoi [EMAIL PROTECTED] writes: 2.3) Why do I need a non-administrator account to run PostgreSQL under? Again, I think this is fine as the default, but it would be nice if it could be changed with a setting (rather than recompiling the source). Not all Windows users are dummies about security and need PostgreSQL to enforce security measures beyond those implemented on other platforms. Sorry, but any Windows user who thinks he doesn't need security measures equivalent to (not beyond) minimum Unix practice is a dummy about security. Take a look at this LOAD vulnerability we're in the midst of patching, and ask yourself whether you aren't glad that it can't be used to get admin privileges on your Windows box. (John Heasman pointed out to me off-list that the LOAD hole *is* remotely exploitable on Windows; details left as an exercise for the reader.) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Delete with a multi-column join?
[EMAIL PROTECTED] writes: Is there a better way to write this? delete from tbldata where unitID || '_' || variableID || '_' || cycleID in (select unitID || '_' || variableID || '_' || cycleID from temp_data_table) delete from tbldata where (unitID, variableID, cycleID) in (select unitID, variableID, cycleID from temp_data_table) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] EMBEDDED PostgreSQL
2.3) Why do I need a non-administrator account to run PostgreSQL under? Again, I think this is fine as the default, but it would be nice if it could be changed with a setting (rather than recompiling the source). Not all Windows users are dummies about security and need PostgreSQL to enforce security measures beyond those implemented on other platforms. Sorry, but any Windows user who thinks he doesn't need security measures equivalent to (not beyond) minimum Unix practice is a dummy about security. Take a look at this LOAD vulnerability we're in the midst of patching, and ask yourself whether you aren't glad that it can't be used to get admin privileges on your Windows box. (John Heasman pointed out to me off-list that the LOAD hole *is* remotely exploitable on Windows; details left as an exercise for the reader.) Actually, if you configure your box for high security, it's not. Granted, not everybody does. But if you do, you're fine. It relies on SMB connection out from your box, which can be disabled in several ways (one of which is putting a firewall in front of your server, which really isn't such a bad idea). //Magnus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Size of data stored in bytea record?
Is there a way that I can find out the size of a bytea record? I am storing images into bytea fields and I just want to know the size of the image that it has been saved in the field
Re: [GENERAL] EMBEDDED PostgreSQL
snip This has long been common practice in the Unix world, and is starting to become standard practice in the Windows world as well as Microsoft and other vendors work to improve the security of their systems. == Again, I think this is fine as the default, but it would be nice if it could be changed with a setting (rather than recompiling the source). That can always be argued :-) Not all Windows users are dummies about security and need PostgreSQL to enforce security measures beyond those implemented on other platforms. First of all, it does *not* enforce anything beyond what's enforced on Unix. On Unix, it doesn't run as root. On Windows, it doesn't run as Administrator. If your users are running as administrators, then you *are* very naive about security on your systems (I won't say dummy, but clearly not making a significant effort). That's where you should fix the problem. For an embedded database, one can argue that it's much less of an issue. And if it was possible without making it a major hack, it would seem reasonable to permit running it as administrator as long as only localhost connections are provided (not by default, but possible. Not by default because a ASP page or whatever still turns anything into a root hole, but it could be configurable). But I don't think that can be done in a non-intrusive way. And it'd just be a workaround the real issue anyway. //Magnus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Delete with a multi-column join?
On Tue, Jan 25, 2005 at 04:16:29PM -0500, [EMAIL PROTECTED] wrote: Howdy! I apologize in advance for the ugly query I'm about to throw your way I need to delete some data from a table based on a multi-column join. Is there a better way to write this? Either: delete from tbldata where (unitID,variableID,cycleID) in (select unitID, variableID, cycleID from temp_data_table) Or: delete from tblData where tblData.unitID = temp_data_table.unitID and tblData.variableID = temp_data_table.variableID and tblData.cycleID = temp_data_table.cycleID Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpOM08QsGRil.pgp Description: PGP signature
Re: [GENERAL] Size of data stored in bytea record?
On Tue, Jan 25, 2005 at 04:58:33PM -0500, Carlos wrote: Is there a way that I can find out the size of a bytea record? I am storing images into bytea fields and I just want to know the size of the image that it has been saved in the field See the Binary String Functions and Operators section of the Functions and Operators chapter in the documentation: http://www.postgresql.org/docs/8.0/static/functions-binarystring.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] visualizing B-tree index coverage
Does anyone know of a tools that allows one to visualize the tree created by a multi-column B-tree index? A picture of a tree with branches, showing how branchy the tree is would be great. I'm wondering how well I've clustered the data in my table using the multi-column index. In other words, do my multi-columns sufficiently but not overly discriminate rows from each other? Do I have too many with the same index? (not enough branches) Do I have a unique index for each row? (way too many branches) Thanks, TJ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Data entry - forms design or other APIs etc. - what is there?
On Fri, 21 Jan 2005 18:55:27 +, Chris Green [EMAIL PROTECTED] wrote: What methods are available to produce data entry forms for postgresql databases? If, for example, one wanted to migrate a system that used Oracle Forms to Postgresql what would one use? This seems to me to be an area which is not aired much here and that surprises me because a database is of no use unless one can get data into it. gnue-forms (www.gnuenterprise.org) was created by a few of us that liked Oracle forms but wanted something better (and free :). It supports any backend supported by our common library. A listing of our backend drivers dir displays ( in no order) adodbapi csv dbf informix interbase mysql oracle sapdb sqlitesybase appserver db2 gadfly ingresldap odbc postgresql special sqlrelay Some drivers are more feature complete than others but most should function. Connections to backends are transparent to forms and other gnue-common based apps. So you can create forms on a postgresql backend (we have support for all 4 python postgresql drivers), change one connections.conf file, and have the forms work against the other databases listed above. We also support several front ends including wx, gtk2, win32 native, and curses(rough but functional in simple cases). We have a separate gnue-designer tool that lets you drag and drop tables and fields to create the XML based form files gnue-forms uses. It also supports wizards to create [single|multi]page master/detail forms. Unlike the last version of Oracle Forms (6?) I used our master/detail can nest to any level without trigger kludges. You can also mix and match datasources on the same form so you could (for whatever reason) create master detail relationships between tables on separate types of backends (I haven't tested that in years though)Also unlike Oracle forms our ui system lets you connect multiple widgets on separate form pages to the same fields in a table, again to reduce the number of triggers needed. We do have a trigger system that lets you write triggers in python and possibly javascript (i've never used the js support) Custom namespaces let you manipulate data via blockname.fieldname Most of our tools functionality is embedded in our gnue-common library so you can use the same datasources and types of access in custom programs as you can in forms. If you're willing to use python that is :) Common provides more than just data access abstraction though, and it's description page doesn't cover all it can do. It contains an application framework, output libraries for things like generating barcodes or tabular pdf reports, formatting functions, a trigger system, and lots of other things. We also have a report tool, and an n-tier application server (with it's own forms backend driver). All based upon the same common core. We're happy to answer questions on our mailing list. Or in IRC at #gnuenterprise on irc.freenode.net Take Care, James ---(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
[GENERAL] Problem with Postgres V 8 and DBI maybe
I am trying to add records to a table in DBI and I keep getting an error 7
The exact error is DBD::Pg::st execute failed: at connect.pl line 107.
ERROR!!! (7) INSERT INTO phonegroups VALUES ('TECHIES',1, 1)
I included the statement and when I try it in pgpadmin it works fine.
Any suggestions?
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] Extended unit
... none of which require any extensions to the core type system. AFAICS this could easily be implemented as a user-defined type, along the lines of CREATE TYPE measurement AS (value double, units text); and if you want to constrain a particular column to contain only one value of units, use CHECK. The argument that we should extend the type system for this would become a lot more credible if there were a widely-used extension in existence for it to prove that there's sufficient demand. I have begining to put all the SI unit in a table. I am writing the function to check the unit in a standard way. I plan to use the user-defined type proposed by Tom Lane. The check are done at execution time. But I object that what I am doing is just a proof of concept and not the right thing to do. I do not want for each column and each row to store the value and the unit. I do want to put the unit in the definition of the column and the check on the parser before any execution. Cordialement, Jean-Gérard Pailloncy ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Extended unit
Pailloncy Jean-Gerard [EMAIL PROTECTED] writes: I do not want for each column and each row to store the value and the unit. I do want to put the unit in the definition of the column and the check on the parser before any execution. If you do that, you foreclose the ability to store mixed values in a single column, in return for what? Saving a couple of bytes per value? (I suppose that in a serious implementation we'd store the units as some sort of reference, not as a string.) Compare the implementation of the NUMERIC type: you *can* constrain a column to have a fixed precision, but you do not *have* to. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] visualizing B-tree index coverage
Useful explanation of PostgreSQL index format: http://www.faqs.org/docs/ppbook/c13329.htm I think you are aiming for the wrong thing. The worst possible index is one with every value the same. The second worst (still basically useless) is one with only two values. The greater the differentiation of the data, the more workload is reduced on a search. Since it isn't a straight binary tree, I don't think that having highly dissimilar data in the index should be a problem. Do you have data or experience that shows otherwise? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of TJ O'Donnell Sent: Tuesday, January 25, 2005 2:19 PM To: pgsql-general@postgresql.org Cc: [EMAIL PROTECTED] Subject: [GENERAL] visualizing B-tree index coverage Does anyone know of a tools that allows one to visualize the tree created by a multi-column B-tree index? A picture of a tree with branches, showing how branchy the tree is would be great. I'm wondering how well I've clustered the data in my table using the multi-column index. In other words, do my multi-columns sufficiently but not overly discriminate rows from each other? Do I have too many with the same index? (not enough branches) Do I have a unique index for each row? (way too many branches) Thanks, TJ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] EMBEDDED PostgreSQL
John DeSoi wrote: But on Windows 8.0 you can't run the postmaster with an administrative account, correct? I really wish this was configurable in the PostgreSQL settings (of course, defaulting to the way it is now). I think there have been several threads debating this issue in the past (on whether Postgres should allow running as root if the user wants to). And in Windows case it can also be argued that Windows XP is a single user OS. But then again running as root/Administrator is a _really bad_ idea, so bad that I'm glad there are software like Apache or Postgres which proactively discourage this practice by flat-out refusing to run as root/Administrator. In Windows, if the installing user is an administrator, I don't think it's that hard to add several lines of code in your app's installer to create a normal user for Postgres to run as. -- dave ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] EMBEDDED PostgreSQL
[EMAIL PROTECTED] wrote: The trick is making database administration invisible to the user. Since Firebird requires no administration, it's easy. The single file database architecture in Firebird is also easy since you generally have only one drive. The decision not to create an embedded Postgres version is never about administration issue. If we want to, we can make Postgres administration and configuration as minimum as possible by creating, say, a pg_autotune daemon that monitors the OS, db activity, and usage pattern, and automatically adjusts the kernel and/or db parameters. I think there's something like this in Oracle 10g and perhaps someday there will be too in Postgres. It's about reliability. Running the app and dbms in the same process space will not guarantee that bugs in app will not mess up the database. And after all, the Firebird requires no administration statement is more of a marketing gimmick anyway. Is it really 100% DBA-free? Can Firebird automatically connect to newegg.com and buy an extra harddisk if it runs out of disk space? :-) To me Postgres is already pretty low in administrative demand as it is. Not that it cannot be improved, of course. -- dave ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Problem with Postgres V 8 and DBI maybe
On Tue, Jan 25, 2005 at 05:49:11PM -0500, David Siebert wrote:
I am trying to add records to a table in DBI and I keep getting an error 7
The exact error is DBD::Pg::st execute failed: at connect.pl line 107.
ERROR!!! (7) INSERT INTO phonegroups VALUES ('TECHIES',1, 1)
Hmmm...I don't think DBI or DBD::Pg prints errors like ERROR!!! (7).
Are you doing your own error checking and only looking at $DBI::err?
What does $DBI::errstr say?
Have you tried to reduce the problem to the smallest amount of code
necessary to reproduce the problem? Something like this:
my $dbh = DBI-connect($source, $user, $password, {RaiseError = 1});
my $sth = $dbh-prepare(INSERT INTO phonegroups VALUES (?, ?, ?));
$sth-execute(TECHIES, 1, 1);
$dbh-disconnect;
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] How are foreign key constraints built?
On Tue, Jan 25, 2005 at 09:38:20AM -0600, Wes wrote: On 1/23/05 1:01 PM, Tom Lane [EMAIL PROTECTED] wrote: If you like you can try the operation with set enable_seqscan = off, but I bet it will take longer. Ouch! That was a big mistake. Since inquiring minds want to know, I decided to give that a try. The expected outcome is to beat the heck out of the index disks as it read one index and referenced the other to see if the value existed. What appears to have happened is that it went through the same process as before, but read each data record via the index. It still created all the pgsql_tmp files, the data disk was still the heaviest hit (expected no or little access there), and it beat the living daylights out of my swap - pageins/outs like crazy. The I/O on the index disks was negligible compared to the data and swap disks. I won't try that again... If the OS is swapping you've got serious issues; you need to look at your configurating settings that deal with memory and figure out why you're running out. And yes, PostgreSQL can't do 'index covering', so even when it accesses a table via an index it still has to read the base table. This is why if you need to read the entire table it's faster to seqscan than index scan. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] EMBEDDED PostgreSQL
Hi Tom, On Jan 25, 2005, at 4:35 PM, Tom Lane wrote: Sorry, but any Windows user who thinks he doesn't need security measures equivalent to (not beyond) minimum Unix practice is a dummy about security. Take a look at this LOAD vulnerability we're in the midst of patching, and ask yourself whether you aren't glad that it can't be used to get admin privileges on your Windows box. So a vulnerability exists on Windows even if PostgreSQL is only accepting local connections? The poster asked about embedded databases and that is what I'm trying to address. I realize this has been thoroughly hashed about in the archives, but I don't recall any discussion of PotgreSQL 8 on Windows. Thanks, John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] visualizing B-tree index coverage
Since I'm using a multi-column index, I can greatly influence the nature of the index created, depending on which columns I use and how many. I'm searching for an optimal set of columns that creates an index that, for sure does not have every value the same, nor only two values. Instead, I want to see how well I've spread the index out over the data (if that phrasing makes sense). More specifically, I have character data representing molecular structures. I've written (rather slow) search functions. I can create any number of columns that fingerprint each structure, e.g. # Carbon atoms, # N atoms, # single bonds, etc. I expect my fingerprints will not be unique (fingerprint may be a poor analogy), but rather will classify similar structures together. I create a multi-column index on these counts and get about 2-3 times speedup using 13 columns right now. For example: select count(smiles) from structure where oe_matches(smiles,'c1c1CC(=O)NC') about 15 sec. select count(smiles) from structure where (_c, _n, _o, _s, _p, _halo, _arom_c, _arom_n, _arom_o, _arom_s, _atoms, _single_bonds, _other_bonds) = ( 3,1,1,0,0,0, 6,0,0,0, 11,4,7 ) and oe_matches(smiles,'c1c1CC(=O)NC') about 6 seconds when the (_c, etc.) is a multi-column index. The data isn't inherently structured in any way that invites some particular number of columns for indexing. I don't want to use too many, nor too few columns. I also want to optimize the nature(which atom types, bond types, etc.) of the count columns. While I could do this and use the speedup as the measure of success, I think that if my B-tree were covering the data well, I would get the best results. Covering means finding that optimal situation where there is not one index for all rows and also not a unique index for every row - something inbetween would be ideal, or is that basically a wrong idea? TJ Useful explanation of PostgreSQL index format: http://www.faqs.org/docs/ppbook/c13329.htm I think you are aiming for the wrong thing. The worst possible index is one with every value the same. The second worst (still basically useless) is one with only two values. The greater the differentiation of the data, the more workload is reduced on a search. Since it isn't a straight binary tree, I don't think that having highly dissimilar data in the index should be a problem. Do you have data or experience that shows otherwise? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of TJ O'Donnell Sent: Tuesday, January 25, 2005 2:19 PM To: pgsql-general@postgresql.org Cc: [EMAIL PROTECTED] Subject: [GENERAL] visualizing B-tree index coverage Does anyone know of a tools that allows one to visualize the tree created by a multi-column B-tree index? A picture of a tree with branches, showing how branchy the tree is would be great. I'm wondering how well I've clustered the data in my table using the multi-column index. In other words, do my multi-columns sufficiently but not overly discriminate rows from each other? Do I have too many with the same index? (not enough branches) Do I have a unique index for each row? (way too many branches) Thanks, TJ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] xpath_list() question for contrib/xml2
On Mon, 24 Jan 2005 16:53:47 -0800, Ron Mayer wrote: Short summary: I want something like xpath_list() that returns an array (instead of a delimited, concatenated string) when multiple nodes exist in an XML file. It feels to me like that'd be a better (cleaner) API. Yes. It's been at the back of my head that it would be a nice idea - when I first started on contrib/xml and /xml2 array support was rather primitive. Before I write one, does anyone already have such a patch? If not, would people be interested if I added xpath_array() that behaves like xpath_list() but returns an array instead of one big string? Or... is xpsql on gborg or some other postgresql-xml project a better place for me to be looking? Well, if you like the way that contrib/xml2 works, I would add it there, but I'm obviously biased. It could just be another wrapper around pgxml_xpath but use its own traversal of the nodeset instead of pgxmlnodesettotext. I can't speak for whether anyone else is doing anything similar, but I haven't heard anyone say so! Thanks for your interest John ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] difficult JOIN
On Tue, Jan 25, 2005 at 05:26:50PM +0100, Thomas Chille wrote: Hi, i have the following SQL-Problem: We are using 2 tables. The first, called plan, is holding planned working times for employees per tour: plan.id_tour plan.id_employee plan.begin_time plan.end_time The second table 'work' stores the actual worked times for employees per tour: work.id_tour work.id_employee work.begin_time work.end_time Employees can be multiple times assigned to one tour. One record will be created for every assignment. They can also work multiple times in one tour. Now i wanna merge this infos into one report. I wanna join the first plan entry for one employee in one tour with the first work entry for one employee in one tour and so on. How can i obtain that? A simply USING(id_tour, id_employee) -JOIN will not doit. Hrm. So for a given tour, employee, you want to pair the first record in plan with the first record in work, and the second record in plan with the second record in work? Doing that will be pretty tricky. I'm not sure you can even do it in a single SELECT. More important, does it even make sense? What if an employee ends up not working at all for one of his/her planned times? Every record after that would be completely skewed. Wouldn't it make much more sense to either assign an ID to each record in the plan table, and correlate records in the work table using that ID, or correlate based on begin and end time? BTW, I've never seen the convention id_employee; people generally use employee_id. Is it more important to know that you're talking about an ID or that you're talking about an employee? Just food for thought. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] EMBEDDED PostgreSQL
On Jan 25, 2005, at 5:02 PM, Magnus Hagander wrote: Again, I think this is fine as the default, but it would be nice if it could be changed with a setting (rather than recompiling the source). That can always be argued :-) I had a feeling it would be :) Not all Windows users are dummies about security and need PostgreSQL to enforce security measures beyond those implemented on other platforms. First of all, it does *not* enforce anything beyond what's enforced on Unix. On Unix, it doesn't run as root. On Windows, it doesn't run as Administrator. OK, perhaps I'm not comparing apples to apples. On OS X I have an administrative account and I can run PostgreSQL just fine. So what you are saying is an administrative account on Windows is more like root on Unix. If your users are running as administrators, then you *are* very naive about security on your systems (I won't say dummy, but clearly not making a significant effort). That's where you should fix the problem. Again, I was merely pointing out the issue for the original poster who wanted an embedded database. On Windows there is currently no way to drag any kind folder with PostgreSQL to the hard drive and run (local connections only) if the user is an administrative user. And my guess is that anyone that buys a Windows machine and sets it up themselves has one account which is an administrative user. Personally, I have no users administrative or otherwise. And the Windows machine I typically use is not even connected to the internet :). John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(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: [GENERAL] visualizing B-tree index coverage
Normally, a unique, clustered index is the silver bullet to the best performance (but my experience with unique clustered indexes is largely in non-PostgreSQL database systems -- so take it with a grain of salt). I do not see any extra expense for a unique index verses one that is mostly unique. Further, if an index is unique, that should be an excellent optimizer hint for query acceleration. If you know what queries you run most frequently, I would tailor the index for optimal query execution via the join columns and columns often involved in where clause filtering. If it is easily possible to make a unique index I would definitely time the queries with a unique index as well. I suspect that the unique index will fare better unless there is something odd about your data. -Original Message- From: TJ O'Donnell [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 25, 2005 3:50 PM To: pgsql-general@postgresql.org Cc: Dann Corbit Subject: RE: [GENERAL] visualizing B-tree index coverage Since I'm using a multi-column index, I can greatly influence the nature of the index created, depending on which columns I use and how many. I'm searching for an optimal set of columns that creates an index that, for sure does not have every value the same, nor only two values. Instead, I want to see how well I've spread the index out over the data (if that phrasing makes sense). More specifically, I have character data representing molecular structures. I've written (rather slow) search functions. I can create any number of columns that fingerprint each structure, e.g. # Carbon atoms, # N atoms, # single bonds, etc. I expect my fingerprints will not be unique (fingerprint may be a poor analogy), but rather will classify similar structures together. I create a multi-column index on these counts and get about 2-3 times speedup using 13 columns right now. For example: select count(smiles) from structure where oe_matches(smiles,'c1c1CC(=O)NC') about 15 sec. select count(smiles) from structure where (_c, _n, _o, _s, _p, _halo, _arom_c, _arom_n, _arom_o, _arom_s, _atoms, _single_bonds, _other_bonds) = ( 3,1,1,0,0,0, 6,0,0,0, 11,4,7 ) and oe_matches(smiles,'c1c1CC(=O)NC') about 6 seconds when the (_c, etc.) is a multi-column index. The data isn't inherently structured in any way that invites some particular number of columns for indexing. I don't want to use too many, nor too few columns. I also want to optimize the nature(which atom types, bond types, etc.) of the count columns. While I could do this and use the speedup as the measure of success, I think that if my B-tree were covering the data well, I would get the best results. Covering means finding that optimal situation where there is not one index for all rows and also not a unique index for every row - something inbetween would be ideal, or is that basically a wrong idea? TJ Useful explanation of PostgreSQL index format: http://www.faqs.org/docs/ppbook/c13329.htm I think you are aiming for the wrong thing. The worst possible index is one with every value the same. The second worst (still basically useless) is one with only two values. The greater the differentiation of the data, the more workload is reduced on a search. Since it isn't a straight binary tree, I don't think that having highly dissimilar data in the index should be a problem. Do you have data or experience that shows otherwise? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of TJ O'Donnell Sent: Tuesday, January 25, 2005 2:19 PM To: pgsql-general@postgresql.org Cc: [EMAIL PROTECTED] Subject: [GENERAL] visualizing B-tree index coverage Does anyone know of a tools that allows one to visualize the tree created by a multi-column B-tree index? A picture of a tree with branches, showing how branchy the tree is would be great. I'm wondering how well I've clustered the data in my table using the multi-column index. In other words, do my multi-columns sufficiently but not overly discriminate rows from each other? Do I have too many with the same index? (not enough branches) Do I have a unique index for each row? (way too many branches) Thanks, TJ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] visualizing B-tree index coverage
Some other things that are important: How much is the data in transition (updates/deletes/inserts)? If the data is mostly static or static you can add many special case indexes with little penalty. The biggest cost of indexes (besides disk space consumed) is in the slowdown of inserts, updates, and deletes. If the data hardly changes, you can throw on index after index with little cost. But if the data is in huge flux, you will have to be careful about performance targets for each index you add. This stuff may prove to be of great value: http://www.postgresql.org/docs/8.0/interactive/monitoring-stats.html I would also run EXPLAIN against every distinct sort of query you plan to execute (unless it is for ad-hoc reporting in which case such a requirement cannot be met). ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Good PostgreSQL Based Shopping Cart Software ... ?
Got a client that needs a shopping cart, and if I can help it, would rather offer them a 'ready made' package and then customize that to fit their site visually, then build everything from scratch, and only give them 10% of the features of a full package ... It doesn't have to be OSS ... Searching the web, I'm finding alot of MySQL based ones, but few that support PostgreSQL ... I'm not overly particular on language though ... PHP prefer'd but perl or python is cool too ... The key requirements are that its PostgreSQL based, and, visually, will be easily customized by a non-programmer ... Again, the shopping cart itself doesn't have to be free ... Can anyone recommend software that they've been happy using? That has a rich feature set? For instance, some that I've come across has a 'gift registry' feature, and/or gift certificates, etc ... Thanks ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Allow case-sensitivity without quotes
Our implementation of Sybase allows case-sensitive referencing of objects (table/column names, etc.) in SQL statements without quote delimiters. For example, the following are equivalent: Sybase: SELECT MyColumn FROM MyTable Postgres: SELECT MyColumn FROM MyTable Can Postgres be configured to allow SQL references to case-sensitive objects without quotes? ---(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: [GENERAL] Recursive queries
I don't think anybody has written the syntactic sugar, but someone did write a function that provides equivalent output. I think it is important that the funcionality lies in the database engine itself: In that way it can more efficiently make use of the optimizer. Also, I think this recursive feature is *the* most important upcoming improvements: Currently there are simply no efficient way of fetching linked structures, which however is quite common in many areas. Regards ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Recursive queries
What do you mean by resursive queries? A query can have a subquery which calls a function which executes another query. That counts as recursion in my book. What type of recursion are you thinking of? No, recursion is a pretty well defined term. See http://en.wikipedia.org/wiki/Recursion SQL:2003 defines a language construct for recursive queries (T131 and T132). What I ment with the question was: Will postgresql soon support a similar (or the same) construct? I know that some Andrew was assigned the task for a year ago, but apparently he has been unsubscribed again. :-( ---(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: [GENERAL] Postgres+XML
Jan Szumiec wrote:
Does PG allow you to store XML documents as DOM documents?
Is there such data type? If not, is it very hard to extend
the type system to include a binary representation of an
XML document?
There are a few ways. I like the stuff in contrib/xml2.
There is also the pgsql project at
http://gborg.postgresql.org/project/xpsql/projdisplay.php
that I think provides similar functionality.
What I'm trying to do is the following:
SELECT AVERAGE(xpath_query('/ds/[EMAIL PROTECTED]'left\']')
GROUP BY language;
I think the example below shows the features you're
interested in using the 'xml2' package from contrib.
fli=# create table xmltest (xml text);
CREATE TABLE
fli=# insert into xmltest values('ab id=one1/b/a');
INSERT 218847847 1
fli=# insert into xmltest values('ab id=two2/b/a');
INSERT 218847848 1
fli=# select sum(xpath_number(xml,'/a/[EMAIL PROTECTED]one]')) from xmltest;
sum
-
1
(1 row)
fli=# select sum(xpath_number(xml,'/a/b')) from xmltest;
sum
-
3
(1 row)
You can see another example of it here:
http://www.throwingbeans.org/tech/postgresql_and_xml.html
but the README in Postgresql's source distribution is
probably the best reference.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Good PostgreSQL Based Shopping Cart Software ... ?
On Wed, 2005-01-26 at 00:46 -0400, Marc G. Fournier wrote: Got a client that needs a shopping cart, and if I can help it, would rather offer them a 'ready made' package and then customize that to fit their site visually, then build everything from scratch, and only give them 10% of the features of a full package ... It doesn't have to be OSS ... Searching the web, I'm finding alot of MySQL based ones, but few that support PostgreSQL ... I'm not overly particular on language though ... PHP prefer'd but perl or python is cool too ... The key requirements are that its PostgreSQL based, and, visually, will be easily customized by a non-programmer ... Again, the shopping cart itself doesn't have to be free ... Can anyone recommend software that they've been happy using? That has a rich feature set? For instance, some that I've come across has a 'gift registry' feature, and/or gift certificates, etc ... http://www.pgcart.com/ runs on php and pgsql 7.3+ (tested 8.0) today An example of it running: http://www.johnbenzart.com demo of admin: http://www.pgcart.com/pgcart_demo/pgcart_demo_1.htm Cheers, -Robby -- /*** * Robby Russell | Owner.Developer.Geek * PLANET ARGON | www.planetargon.com * Portland, OR | [EMAIL PROTECTED] * 503.351.4730 | blog.planetargon.com * PHP/PostgreSQL Hosting Development * --- Now hosting PostgreSQL 8.0! --- / ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] text field constraint advice
PostgreSQL users, I would like to use a text field in a table and limit the size to reduce the chance of denial-of-service/buffer overflow/etc. I assume I can define table fields similar to the following field_name text check (len(field) 160) although my syntax is probably wrong. Is checking text length considered a good idea? If so, what would be a reasonable limit? I was thinking about 10 * average_field_char_length (if avg value is 16 char, set limit to 160 char). Thanks in advance for your opinions. Thanks, Dale ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] visualizing B-tree index coverage
Excuse me for bothering but what kind of search engine you developed. Does it looks like sets comparing ? Oleg On Tue, 25 Jan 2005, TJ O'Donnell wrote: Since I'm using a multi-column index, I can greatly influence the nature of the index created, depending on which columns I use and how many. I'm searching for an optimal set of columns that creates an index that, for sure does not have every value the same, nor only two values. Instead, I want to see how well I've spread the index out over the data (if that phrasing makes sense). More specifically, I have character data representing molecular structures. I've written (rather slow) search functions. I can create any number of columns that fingerprint each structure, e.g. # Carbon atoms, # N atoms, # single bonds, etc. I expect my fingerprints will not be unique (fingerprint may be a poor analogy), but rather will classify similar structures together. I create a multi-column index on these counts and get about 2-3 times speedup using 13 columns right now. For example: select count(smiles) from structure where oe_matches(smiles,'c1c1CC(=O)NC') about 15 sec. select count(smiles) from structure where (_c, _n, _o, _s, _p, _halo, _arom_c, _arom_n, _arom_o, _arom_s, _atoms, _single_bonds, _other_bonds) = ( 3,1,1,0,0,0, 6,0,0,0, 11,4,7 ) and oe_matches(smiles,'c1c1CC(=O)NC') about 6 seconds when the (_c, etc.) is a multi-column index. The data isn't inherently structured in any way that invites some particular number of columns for indexing. I don't want to use too many, nor too few columns. I also want to optimize the nature(which atom types, bond types, etc.) of the count columns. While I could do this and use the speedup as the measure of success, I think that if my B-tree were covering the data well, I would get the best results. Covering means finding that optimal situation where there is not one index for all rows and also not a unique index for every row - something inbetween would be ideal, or is that basically a wrong idea? TJ Useful explanation of PostgreSQL index format: http://www.faqs.org/docs/ppbook/c13329.htm I think you are aiming for the wrong thing. The worst possible index is one with every value the same. The second worst (still basically useless) is one with only two values. The greater the differentiation of the data, the more workload is reduced on a search. Since it isn't a straight binary tree, I don't think that having highly dissimilar data in the index should be a problem. Do you have data or experience that shows otherwise? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of TJ O'Donnell Sent: Tuesday, January 25, 2005 2:19 PM To: pgsql-general@postgresql.org Cc: [EMAIL PROTECTED] Subject: [GENERAL] visualizing B-tree index coverage Does anyone know of a tools that allows one to visualize the tree created by a multi-column B-tree index? A picture of a tree with branches, showing how branchy the tree is would be great. I'm wondering how well I've clustered the data in my table using the multi-column index. In other words, do my multi-columns sufficiently but not overly discriminate rows from each other? Do I have too many with the same index? (not enough branches) Do I have a unique index for each row? (way too many branches) Thanks, TJ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
