[GENERAL] Error from index pg_type_typname_index????
Hi all... I've a postgresql 7.0.2 used as a backend for a website. Randomly, and not very frequently, an error pops up saying that the following problem has happened: ERROR: Cannot insert a duplicate key into unique index pg_type_typname_index The query causing it it's an innocent query that duplicates a table in a temporary one, i.e. "select * into forum_clone from forums" That of course doesn't cause any problem 99% of the time. The fact that it happens doesn't seem to be related to load, neither vacuumize the db seems to change anything. Now my question is: anybody has a hint on what mey be happening in that darn 1%??? TIA, Ciao! /\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ Fabrizio Ermini Alternate E-mail: C.so Umberto, 7 [EMAIL PROTECTED] loc. Meleto Valdarno Mail on GSM: (keep it short!) 52020 Cavriglia (AR) [EMAIL PROTECTED]
[GENERAL] transaction safety
Hi all. I want to create a new tuple of main info and detail tuples (in different tables) that are joined by a key field. Key field is created by generator automatically and I need that number in order to assign to detail tuples. How can I to get that number in a safe way?. Thanks for ideas. David
Re: [GENERAL] transaction safety
DaVinci wrote: Hi all. I want to create a new tuple of main info and detail tuples (in different tables) that are joined by a key field. Key field is created by generator automatically and I need that number in order to assign to detail tuples. How can I to get that number in a safe way?. A successful INSERT returns the oid of the row just created; so get the new value with a query like this: SELECT key_field FROM table WHERE oid = new_oid_value; In libpq programming, use PQoidValue() to get the oid. Other interfcaes should have equivalent methods. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "When I consider thy heavens, the work of thy fingers, the moon and the stars, which thou hast ordained; What is man, that thou art mindful of him? and the son of man, that thou visitest him? For thou hast made him a little lower than the angels, and hast crowned him with glory and honour."Psalms 8:3-5
Re: [GENERAL] transaction safety
On Mon, Feb 12, 2001 at 11:08:55AM +, Oliver Elphick wrote: DaVinci wrote: Hi all. I want to create a new tuple of main info and detail tuples (in different tables) that are joined by a key field. Key field is created by generator automatically and I need that number in order to assign to detail tuples. How can I to get that number in a safe way?. A successful INSERT returns the oid of the row just created; so get the new value with a query like this: SELECT key_field FROM table WHERE oid = new_oid_value; I have a new question for this idea (thanks). When Database is big, do i need index for oid field to speed select?. Greets. David
Re: [GENERAL] transaction safety
On Mon, Feb 12, 2001 at 01:08:01PM -, Michael Ansley wrote: The number returned by the sequence for the serial ID is retained within the session, and so it can be returned by calling currval, e.g.: ...[a detailed example]... Typically, the insert for a person, and for all the associated addresses would be done in the same transaction so that if the insert for one of the addresses failed, then the whole lot would role back (perhaps a bit extreme, but I think that's what you asked for ;-) That sounds good ;) I thought it is possible to have different transactions opened and insert data in same table from them. It seems my idea was fault, doesn't it?. In sumary: locks with inserts are for table and not for tuple. If this is not true, tell me details, please :) Thanks for all. David
Re: [GENERAL] Error from index pg_type_typname_index????
[EMAIL PROTECTED] writes: I've a postgresql 7.0.2 used as a backend for a website. Randomly, and not very frequently, an error pops up saying that the following problem has happened: ERROR: Cannot insert a duplicate key into unique index pg_type_typname_index The query causing it it's an innocent query that duplicates a table in a temporary one, i.e. "select * into forum_clone from forums" I think you're probably trying to do two of these at the same time. Table creation also creates an entry in pg_type for the table's row type, and IIRC that happens before the pg_class entry is made. Example: session 1: regression=# begin; BEGIN regression=# create table foot (f1 int); CREATE session 2: regression=# create table foot (f1 int); blocks waiting to see if session 1 commits or not session 1 again: regression=# end; COMMIT now session 2 reports: ERROR: Cannot insert a duplicate key into unique index pg_type_typname_index Session 2's check to see if the table name already existed didn't find a conflict because session 1 hadn't committed yet; it was only the first insert into a unique index that caused a synchronization point. I'll take a look to see if the order of operations can't be reversed so that you get a more understandable complaint about a unique index on pg_class in this case. However, the real answer for you is to be using a TEMP table if you are going to have multiple clients creating temporary tables at about the same time. That avoids the name conflict. regards, tom lane
RE: [GENERAL] transaction safety
Title: RE: [GENERAL] transaction safety
There should be no locks on the table for insert. The sequence number is retained by the session, not locked on the database. What this means is that somebody else may start their insert after you, and then you roll back, leaving a gap in the sequences. However, gaps in the sequences are not (or should not be) important. You can test this out using a few sessions of psql.
You need to view this with about 110 chars across, in fixed-width font (tab-width of 4 chars):
a$ psql test | b$ psql test
a= begin; b= begin; |
a= insert into person (name, dob)|
a- values ('Peter', '03/09/1945'); |
INSERT |
a= select currval('person_id_seq'); |
1|
| b= insert into person (name, dob)
| b- values ('Marge', '05/03/1967');
| INSERT
| b= select currval('person_id_seq');
| 2
a= select currval('person_id_seq'); |
1|
a= insert into address (id_person, address) |
a- values (currval('person_id_seq'), ''); |
INSERT XXXY |
| b= insert into address (id_person, address)
| b- values (currval('person_id_seq'), '');
| INSERT XXXY
| b= select currval('person_id_seq');
| 2
a= rollback; |
ROLLBACK |
| b= select currval('person_id_seq');
| 2
| b= end;
| COMMIT
v
time
Now, although the left side has rolled back, the sequence number 1 is lost. However, this is not a problem, as the primary key requires uniqueness, but not continuity, and it means that your inserts do not block the table. Inserts can happen as fast as you can push data into the table.
Read up on sequences, because serial fields are based on sequences, thus avoiding the locking issue.
Hope this helps...
MikeA
-Original Message-
From: DaVinci [mailto:[EMAIL PROTECTED]]
Sent: 12 February 2001 13:43
To: Lista PostgreSql
Subject: Re: [GENERAL] transaction safety
On Mon, Feb 12, 2001 at 01:08:01PM -, Michael Ansley wrote:
The number returned by the sequence for the serial ID is retained within the
session, and so it can be returned by calling currval, e.g.:
...[a detailed example]...
Typically, the insert for a person, and for all the associated addresses
would be done in the same transaction so that if the insert for one of the
addresses failed, then the whole lot would role back (perhaps a bit extreme,
but I think that's what you asked for ;-)
That sounds good ;)
I thought it is possible to have different transactions opened and insert
data in same table from them. It seems my idea was fault, doesn't it?.
In sumary: locks with inserts are for table and not for tuple. If this is
not true, tell me details, please :)
Thanks for all.
David
**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
www.mimesweeper.com
**
Re: [GENERAL] transaction safety
DaVinci [EMAIL PROTECTED] writes: On Mon, Feb 12, 2001 at 01:08:01PM -, Michael Ansley wrote: Typically, the insert for a person, and for all the associated addresses would be done in the same transaction so that if the insert for one of the addresses failed, then the whole lot would role back (perhaps a bit extreme, but I think that's what you asked for ;-) I thought it is possible to have different transactions opened and insert data in same table from them. It seems my idea was fault, doesn't it?. In sumary: locks with inserts are for table and not for tuple. If this is not true, tell me details, please :) It's not true. How did you arrive at that conclusion from what Mike said? regards, tom lane
[GENERAL] Re: startup Postgres on NT
On Mon, Feb 12, 2001 at 12:56:04PM +0700, Jarungwit J. wrote: Now I can start ipc-daemon as NT Service by using invoker methood. But when I start postmaster with invoker method, it shows error: Error 0203: The sytem could not find the environment option that was entered. Note that I can start postmaster in cygwin-shell. Any suggestion? Make sure that the environment variables for whatever NT user the postmaster service is running under are set to everything that postmaster needs. Consider PGDATA, PGHOST, and PATH in particular. Note that the .bash_profile and .bashrc files won't apply to the service since it isn't run in the context of bash/sh. The Cygwin /usr/bin (or /bin) folder should be in PATH too so that postmaster/postgres can call system() on utilities like 'rm' and 'cp'. -- Fred Yankowski [EMAIL PROTECTED] tel: +1.630.879.1312 Principal Consultant www.OntoSys.com fax: +1.630.879.1370 OntoSys, Inc 38W242 Deerpath Rd, Batavia, IL 60510, USA
Re: [GENERAL] Error from index pg_type_typname_index????
On 12 Feb 2001, at 10:10, Tom Lane wrote: ERROR: Cannot insert a duplicate key into unique index pg_type_typname_index The query causing it it's an innocent query that duplicates a table in a temporary one, i.e. "select * into forum_clone from forums" I think you're probably trying to do two of these at the same time. And you do think right. (And this should not came as a surprise, I would add :-)). I've ascertained it doing a little stress-testing, and simply rethinking on the fact that I was doing a dumb thing... I'll take a look to see if the order of operations can't be reversed so that you get a more understandable complaint about a unique index on pg_class in this case. However, the real answer for you is to be using a TEMP table if you are going to have multiple clients creating temporary tables at about the same time. That avoids the name conflict. Nope. This is the first thing I've tried after I've realized what was happening, but it does not work in a web environment, at least in a PHP based like mine; I think it scales down to PHP ways of optimizing connection pool (which, in effect, have given me some worry over time): if use a TEMP table and try to stress test the page (i.e. "hit furiosly F5 cycling to several explorer windows with the mouse" :-)) i got many errors complaining things such "table doesn't exist" or similar. Evidently the various TEMP tables of the various pages where mismatching, since they have a lifetime based on the concept of a "session" that's not 1:1 with the lifetime of a web page. I resorted to handle the creation of the various tables at application level, creating temp tablenames with uniqueid() function. A little overhead but it works well. Summarizing all this thoughts, the moral is that it's not been PG's fault (unless for a less-than-clear error message, but that's a venial sin :-)), that I should think more before screaming wolf, and that I really should study better the way PHP handles PG connection... there's some "hidden magic" in there that doesn't convince me. Thanks for you attention, as ever, and Ciao Fabrizio /\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ Fabrizio Ermini Alternate E-mail: C.so Umberto, 7 [EMAIL PROTECTED] loc. Meleto Valdarno Mail on GSM: (keep it short!) 52020 Cavriglia (AR) [EMAIL PROTECTED]
Re: [GENERAL] strange query results
Anand Raman [EMAIL PROTECTED] writes: The second query is not priniting out site_section column. Odd. What is the exact definition of table exhibit_distributions? Does it have any indices? What plan is printed by EXPLAIN for the problem query? regards, tom lane
Re: Failover (was Re: [GENERAL] PostgreSQL over NFS?)
Shaw Terwilliger [EMAIL PROTECTED] writes: Besides dumping and COPY'ing the data into the second server, is there another form of easy one-way replication available? My schema has just Have you looked at PG's replication? It's included in 7.1, and you can get it from (I think) www.postgresql.com for 7.0. Chris -- [EMAIL PROTECTED] - Chris JonesSRI International, Inc. www.sri.com
[GENERAL] making functions take arrays
Is it possible to make a compiled C function take an array as an argument? Right now I've got a function that takes 32 arguments, all declared as separate variables (int arg1, int arg2, ...) and it would be a lot more natural for the function to be called with an array (int *array, int num_elements). Is this possible? Also, how do I return an SQL NULL from a function? Thanks, Kyle
Re: [GENERAL] making functions take arrays
Kyle [EMAIL PROTECTED] writes: Is it possible to make a compiled C function take an array as an argument? Sure, although disassembling the array is not quite trivial (a Postgres array is not the same as a C array). In the 7.1 code, there is a deconstruct_array() function in src/backend/utils/adt/arrayfuncs.c that will help. Also, how do I return an SQL NULL from a function? In 7.0, you don't. In 7.1, see src/backend/utils/fmgr/README. regards, tom lane
[GENERAL] System Tables
Is it possible to accomplish any SQL statement manipulating the System tables? I was wondering about the possibility to substitute the unrecognized SQL statements (ALTER TABLE table COLUMN , for instance) through some queries to the system tables. Nelio -- Nelio Alves Pereira Filho IFX Networks - www.ifx.com.br +55 11 3365-5863 [EMAIL PROTECTED]
[GENERAL] Casting help
hhs=# INSERT INTO applicants_test (SELECT app_id ,old_id ,emp_id ,inv_id ,createdate ,updatedon ,todelete ,appstatus ,apptype ,infosent ,empinitials ,firstname ,lastname ,salutation ,fontype1 ,fonnumber1 ,fonext1 ,fontype2 ,fonnumber2 ,fonext2 ,fontype3 ,fonnumber3 ,fonext3 ,address1 ,address2 ,city ,state ,postal ,country ,homestate ,email ,careerdate ,degree1 ,degreedate1 ,gpa1 ,university1 ,degree2 ,degreedate2 ,gpa2 ,university2 ,startdate ,currentsalary ,degreecode ,industrycode ,title ,company ,companydiv ,preemploy ,minority as processtype ,inunion ,prodnotes ,sk1 ,sk2 ,sk3 ,sk4 ,sic1 ,sic2 ,sic3 ,certificates ,clearance ,languages ,desiredsalary ,minimumsalary ,whyseeking ,followdate ,personalnotes as technotes ,technotes as personalnotes ,relonote ,follownote ,profile ,relopref1 ,relopref2 ,relo_states ,source ,upinitials FROM applicants); ERROR: Attribute 'processtype' is of type 'varchar' but expression is of type 'bool' You will need to rewrite or cast the expression hhs=# And if I try to cast minority to varchar (or anything else for that matter) I get ERROR: Cannot cast type 'bool' to 'varchar' Is there any way to get the above to work? I have a client down and need this (or something like this) to straighten out his data tables... Thanks! -Mitch
Re: [GENERAL] Casting help
Mitch Vincent writes: if I try to cast minority to varchar (or anything else for that matter) I get ERROR: Cannot cast type 'bool' to 'varchar' Try: CASE WHEN minority THEN 'value of true' ELSE 'value if false' END -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [GENERAL] 7.1 installation problem
Peter Eisentraut wrote: Alexander Jerusalem writes: I'm running into problems while trying to install 7.1 beta 4 (on RedHat Linux 7 with bug fixes applied) I've downloaded the rpms but when I do an rpm -iv postgres-7.1beta4-1.i386.rpm it complains about an unresolved dependency on libreadline.so.3. I have a libreadline.so.4. Is that ok? It should be. The RPMs are broken in that respect. No, they're not broken -- unless being built on RedHat 6.2 qualifies as broken. RedHat 7 has many differences from RedHat 6.2. The best thing to do (until RH 7 RPM's are built -- final release will definitely have RH 7 binaries) is to rebuild from the source RPM on all BUT RedHat 6.2. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
Re: [GENERAL] 7.1 installation problem
Alexander Jerusalem [EMAIL PROTECTED] writes: I'm running into problems while trying to install 7.1 beta 4 (on RedHat Linux 7 with bug fixes applied) I've downloaded the rpms but when I do an rpm -iv postgres-7.1beta4-1.i386.rpm it complains about an unresolved dependency on libreadline.so.3. I have a libreadline.so.4. Is that ok? Install the readline2.2.1-2.2.1-2 rpm (which comes with Red Hat Linux 7 - look on your CD or ftp mirror) for backwards compatibility. -- Trond Eivind Glomsrd Red Hat, Inc.
[GENERAL] Re: (Update) Implicit smallint to boolean conversion?
Found the problem:
In org/postgresql/jdbc2/ResultSet.java
The following line determines true/false:
return ((c == 't') || (c == 'T'));
Changing this to:
return ((c == 't') || (c == 'T') || (c == '1'));
Fixes the problem. This could probably be improved to:
return ((c == 't') || (c == 'T') || (c != '0'));
and in that way any result is 'true' and only '0' returns false (for SMALLINT).
Regards,
Brice Ruth
Brice Ruth wrote:
Here's a code snippet that shows the problem:
+--+
boolean bNeuroPsych = objResults.getBoolean("NeuroPsych");
short sNeuroPsych = objResults.getShort("NeuroPsych");
System.out.println("Neurologic/Psychiatric: "+bNeuroPsych);
System.out.println("Neurologic/Psychiatric: "+sNeuroPsych);
+--+
The "NeuroPsych" field is defined in PostgreSQL as a SMALLINT. The
following is printed out when the code above is run:
Neurologic/Psychiatric: false
Neurologic/Psychiatric: 1
Apparently, Oracle, Sybase, SQL Server Access treat a smallint of '1'
as 'true' when returned from getBoolean(). Is this a bug in the driver
for PostgreSQL? Any help would be appreciated!
See original message below for background further details of the problem.
TIA Best Regards,
Brice Ruth
(Original Message)
Greetings.
I'm working with a third party product that is essentially a Java API to
data housed in the database of your choice. My choice is PostgreSQL in
this case. Access to the database is through JDBC, in this case,
jdbc7.0-1.2.jar. One of the API calls isn't returning the correct
results to my application - so I turned to the vendor for help. They've
tested the call through JDBC to Access, Sybase, Oracle, and SQL Server
and received the correct results. So, the current thought is that
something is wrong in the JDBC library provided for PostgreSQL.
Here's what's happening:
The table being queried has 'smallint' fields in it. Apparently these
fields will contain either 0 or 1, depending on if a condition is 'true'
or 'false'. So, when the API queries the database, it apparently
expects that these smallint fields will be implicitly converted to
boolean 'true/false' values in Java, which is what is then returned to
the application calling into the API. I don't know what Java code
they're using - I've requested the relevant snippets. I can provide the
query that is being run by the API, I can provide the structure of the
table used in the SQL CREATE statement, and I can provide an example of
the data in a record that would be returned. I won't paste all those
things in this e-mail since I don't know if its necessary in this case -
just let me know if it is.
Any help or guidance on this issue would be greatly appreciated, as always.
Sincerest regards TIA,
--
Brice Ruth
WebProjkt, Inc.
VP, Director of Internet Technology
http://www.webprojkt.com/
--
Brice Ruth
WebProjkt, Inc.
VP, Director of Internet Technology
http://www.webprojkt.com/
[GENERAL] (Update) Implicit smallint to boolean conversion?
Here's a code snippet that shows the problem:
+--+
boolean bNeuroPsych = objResults.getBoolean("NeuroPsych");
short sNeuroPsych = objResults.getShort("NeuroPsych");
System.out.println("Neurologic/Psychiatric: "+bNeuroPsych);
System.out.println("Neurologic/Psychiatric: "+sNeuroPsych);
+--+
The "NeuroPsych" field is defined in PostgreSQL as a SMALLINT. The
following is printed out when the code above is run:
Neurologic/Psychiatric: false
Neurologic/Psychiatric: 1
Apparently, Oracle, Sybase, SQL Server Access treat a smallint of '1'
as 'true' when returned from getBoolean(). Is this a bug in the driver
for PostgreSQL? Any help would be appreciated!
See original message below for background further details of the problem.
TIA Best Regards,
Brice Ruth
(Original Message)
Greetings.
I'm working with a third party product that is essentially a Java API to
data housed in the database of your choice. My choice is PostgreSQL in
this case. Access to the database is through JDBC, in this case,
jdbc7.0-1.2.jar. One of the API calls isn't returning the correct
results to my application - so I turned to the vendor for help. They've
tested the call through JDBC to Access, Sybase, Oracle, and SQL Server
and received the correct results. So, the current thought is that
something is wrong in the JDBC library provided for PostgreSQL.
Here's what's happening:
The table being queried has 'smallint' fields in it. Apparently these
fields will contain either 0 or 1, depending on if a condition is 'true'
or 'false'. So, when the API queries the database, it apparently
expects that these smallint fields will be implicitly converted to
boolean 'true/false' values in Java, which is what is then returned to
the application calling into the API. I don't know what Java code
they're using - I've requested the relevant snippets. I can provide the
query that is being run by the API, I can provide the structure of the
table used in the SQL CREATE statement, and I can provide an example of
the data in a record that would be returned. I won't paste all those
things in this e-mail since I don't know if its necessary in this case -
just let me know if it is.
Any help or guidance on this issue would be greatly appreciated, as always.
Sincerest regards TIA,
--
Brice Ruth
WebProjkt, Inc.
VP, Director of Internet Technology
http://www.webprojkt.com/
Re: [GENERAL] Howto install postgresql 7.03 on Darwin 1.2
James Patterson writes: I am having a great deal of difficulting installing postgresql 7.03 on Darwin 1.2. 7.0.3 [sic] doesn't work on Darwin. Try a 7.1 beta. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [GENERAL] PostgreSQL vs Oracle vs DB2 vs MySQL - Which should I use?
On Sat, 10 Feb 2001 02:39, you wrote: Please could someone explain what are the major differences between PostgreSQL, Pro:- It's free in all senses of the word. ( BSD Licence ) Thus no new licence required if you migrate to more powerful hardware. It works well enough to run a set of Ledgers. ( Since version 7, I would trust my own payroll data to it. ) It has transaction support. It has an enormous number of builtin extensions and interface apis in many languages. It just goes slower under very heavy loads. It is supported by a very competent team of developers who are not only determined to stay at the front of open source database race, but also quite patently give the impression that they actually _care_ about their users' problems. The development releases available from the tip of the CVS tree are considerably more stable than the run-of-the-mill CVS tips in other projects. There are several open source projects at various stages of development doing what you intend to do already. Con:- Documentation is not as up-to-date or comprehensive as perhaps it should be. Probably not quite as fast as the commercial products. The point and click interfaces and support program generators are all rather rudimentary when compared to the commercial offerings. All disk access is via the ( slower? ) host file system; no raw disk read or write. Neither the replication nor hot backup facilities have had time to mature. = Oracle 8i, DB2 Closed, expensive, secret commercial offerings. Both have good reputations. Very good point and click interfaces. DB2 is very well respected as a mature and solid product. You forgot to mention Informix which has a distant common ancestor with PostgreSQL. It works well. Its extensive documentation is well written, but totally chaotic. You need 6 books open at the same time to get the Dynamic Server installed! = and MySQL In a word - Don't. Definately not in an financial accounting capacity. No transaction support. MySQL is intended as a very fast, mostly read, data store. The designers have sacrificed data integrity for speed. It is reported to fall over catistrophically under heavy load. This URL will lead through to several papers which explain in more detail. http://www.google.com/search?q=Philip+Greenspun+Why+not+MySQLbtnG=Google+Search We are looking to start re-developing our accounts/order processing system using a major database (we currently use a 4GL). We would appreciate any constructive advise to help us make the decision on which database to use. -- Sincerely etc., NAME Christopher Sawtell CELL PHONE 021 257 4451 ICQ UIN45863470 EMAIL csawtell @ xtra . co . nz CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz -- Please refrain from using HTML or WORD attachments in e-mails to me --
Re: [GENERAL] PostgreSQL vs Oracle vs DB2 vs MySQL - Which should I use?
On Tue, Feb 13, 2001 at 11:55:18AM +1300, Christopher Sawtell wrote: snip It is supported by a very competent team of developers who are not only determined to stay at the front of open source database race, but also quite patently give the impression that they actually _care_ about their users' problems. Let me echo this. I have _never_ been on a list where so many of the developers of a product were subscribed as well. And these folks provide _very_ considerate and clear assistance. Hats off to them. Paul
Re: [GENERAL] Importing a Database
On Sat, Feb 10, 2001 at 11:16:45AM -0500, No Name wrote: If i have a dbf database, how can I import it into PostgreSQL? Thanks I have a program that does this. I've set up a project at SourceForge for it, but I'm still fighting with them over CVS access etc. If you're interested, I can email you a copy of the tarball. Paul
Re: [GENERAL] performance - self-joins vs. subqueries
"K. Ari Krupnikov" [EMAIL PROTECTED] writes: Many queries can be formulated as either a self-join or a subquery on the same table. Is there any difference in performance, or does the analyzer treat them the same? You're a lot better off with a self-join, at present. regards, tom lane
Re: [GENERAL] Howto install postgresql 7.03 on Darwin 1.2
James Patterson [EMAIL PROTECTED] writes: I am having a great deal of difficulting installing postgresql 7.03 on Darwin 1.2. Can anyone give me a few pointers? Sure: forget 7.0.3, give 7.1beta4 (or current snapshot) a try instead. There's no port for Darwin in 7.0.*. regards, tom lane
[GENERAL] Re: [SQL] Re: SQL Join - MySQL/PostgreSQL difference?
heya there are a couple of good example on how to do this in the perl cookbook the trim function in chapter one might help the source code from the book is avail at ora.com
Re: [GENERAL] Re: 7.1 Docs WAS:Auto-timestamp generator (attached)
On Mon, 12 Feb 2001, Bruce Momjian wrote: As a suggestion regarding the docs ... as a newbie, I went straight to the"user's lounge" and didn't check out the "developer's corner" because I figured those docs would be pretty hard-core technical. But in reality, many of the developer's docs are much the same, only more current and more complete. I think I have seen other questions on this listserv deriving from the same problem (they would be easily answered from the developer's docs). IMHO, perhaps there should be some sort of a pointer from the user's lounge to the developer's docs, with warning, if that is necessary?? Can you give us an example of something you found in developers that was not in users? The docs in devel-corner are for a not yet released version. If you're running a beta or developer's version of PostgreSQL, that's what you need. Otherwise you should avoid it or encounter the same pitfall as others who thought the stuff was relevant to their release systems and spent hours trying to get something working that wasn't implemented in their version. While I'm at it ... to show that I am super-anal-retentive ... the "l" in "lounge" on the www.postgresql.org home page should be capitalized. :) Oh, he got us there. No, as he said he's being anal. Since I came up with the user's lounge I'm the one that knows it's not a proper noun and therefore *should* be lower case. I do however make it upper case at times for no apparent reason -- my bad :) Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com ==
Re: [GENERAL] jdbc connection pool settings
it depends on what kind of concurrent connections u will be getting.. For admin interfaces the kind of settingu mentioned might be OK but in case it is a heavily loaded site then u need to think.. Anand On Mon, Feb 12, 2001 at 02:55:08PM -0800, Culley Harrelson wrote: I'm in the process of implementing connection pooling and the setup I'm using (http://www.javaexchange.com - really slick!) has settings for min # connections and max # connection. Any suggestions on where I should set these values? min=2, max=6? My site will be outside the firewall, open to the public for all to trash. Culley __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/
Re: [GENERAL] strange query results
Hi tom
The table description is as follows
arttoday= \d exhibit_distributions
Table "exhibit_distributions"
Attribute | Type | Modifier
--+---+--
exhibit_distribution_id | integer | not null default
nextval('sq_exhibit_dist_id'::text)
exhibit_id | integer | not null
created_by | integer | not null
creation_date| timestamp | not null default
"timestamp"('now'::text)
last_update_date | timestamp |
last_updated_by | integer |
exhibit_type_id | integer | not null
medium | varchar(100) |
image_path_small | varchar(50) |
image_path_big | varchar(50) |
length | numeric(7,2) |
breadth | numeric(7,2) |
width| numeric(7,2) |
diameter | numeric(7,2) | default 8
dimensional_aspect | varchar(50) | default 'Dimensions'
unframed_volume_weight | numeric(10,2) |
framed_volume_weight | numeric(10,2) |
override_vw_computation | boolean | default 'f'::bool
exhibit_code | varchar(25) |
exhibit_options | varchar(10) |
unframed_exhibit_restriction | varchar(25) | default 'WORLD'
framed_exhibit_restriction | varchar(25) | default 'WORLD'
up_for_sale | char(1) | not null default 'T'
gallery_id | integer |
site_section | varchar(20) | not null default 'GALLERY'
Index: exhibit_distributions_pkey
Constraints: ((up_for_sale = 'T'::bpchar) OR (up_for_sale = 'F'::bpchar))
(length '0'::"numeric")
(breadth '0'::"numeric")
(width '0'::"numeric")
(((unframed_exhibit_restriction = 'WORLD'::"varchar") OR
(unframed_exhibit_restriction = 'INDIA'::"varchar")) OR (unframed_exhibit_restriction
= 'ONLY_WORLD'::"varchar"))
(((framed_exhibit_restriction = 'WORLD'::"varchar") OR
(framed_exhibit_restriction = 'INDIA'::"varchar")) OR (framed_exhibit_restriction =
'ONLY_WORLD'::"varchar"))
(((exhibit_options = 'FRAMED'::"varchar") OR (exhibit_options =
'UNFRAMED'::"varchar")) OR (exhibit_options = NULL::"varchar"))
Explain plan results in the following
arttoday= explain select site_section, count(*) from exhibit_distributions group by
site_section;
NOTICE: QUERY PLAN:
Aggregate (cost=69.83..74.83 rows=100 width=12)
- Group (cost=69.83..72.33 rows=1000 width=12)
- Sort (cost=69.83..69.83 rows=1000 width=12)
- Seq Scan on exhibit_distributions
(cost=0.00..20.00 rows=1000 width=12)
The problem still persists.. I will a drop and recreation of the db just
in case..
Thanks for the help
Anand
On Mon, Feb 12, 2001 at 11:04:55AM -0500, Tom Lane wrote:
Anand Raman [EMAIL PROTECTED] writes:
The second query is not priniting out site_section column.
Odd. What is the exact definition of table exhibit_distributions?
Does it have any indices? What plan is printed by EXPLAIN for the
problem query?
regards, tom lane
[GENERAL] Trapping NOTICE using PHP
Is there any way to trap the NOTICE output (specifically in PHP)? I would like to impliment an EXPLAIN feature in phpPgAdmin and have been unsuccessful in finding a way to do this because all the information returned by EXPLAIN is in a NOTICE. Any ideas? -Dan
[GENERAL] Vacuum and Owner
I run as user "spry" on psql, I created the database as "spry" but when I run vacuum, all the system tables get skipped because "spry" is not the owner. Is there a way to fully vacuum my db and the system tables as a user other than "postgres"? Is is very important that the system tables are "vaccumed" in addition to my db tables? Matt Friedman
Re: [GENERAL] transaction safety
On Mon, Feb 12, 2001 at 10:22:30AM -0500, Tom Lane wrote: DaVinci [EMAIL PROTECTED] writes: On Mon, Feb 12, 2001 at 01:08:01PM -, Michael Ansley wrote: Typically, the insert for a person, and for all the associated addresses would be done in the same transaction so that if the insert for one of the addresses failed, then the whole lot would role back (perhaps a bit extreme, but I think that's what you asked for ;-) I thought it is possible to have different transactions opened and insert data in same table from them. It seems my idea was fault, doesn't it?. In sumary: locks with inserts are for table and not for tuple. If this is not true, tell me details, please :) It's not true. How did you arrive at that conclusion from what Mike said? I'll try to explain. Mike said: "in a transaction make an insert and then a read in serial current value". If in gap between those operations occurs another insert from different transaction, then reading serial is not safe. In order to understand this well I have made some basic experiments, freezing a transaction with an insert and making other transaction with an insert to the same table. Second gets frozen until first commit or cancel. That is reason of my last message: "locks with inserts are for table and not for tuple". Perhaps I didn't explain myself very well or there is some detail about locks that I don't understand at all. Thanks all for your time. David
