Re: [SQL] pg_dump error
On Mon, Jan 15, 2001 at 08:36:46PM -0500, Tom Lane wrote: > Tatsuo Ishii <[EMAIL PROTECTED]> writes: > Can not create pgdump_oid table. Explanation from backend: 'ERROR: > cannot create pgdump_oid > > Is there a way to repair this? > > I would guess that this means there is a file named "pgdump_oid" hanging > around in the database directory, no doubt leftover from some previous > pg_dump cycle. Manually removing the file should get you up and running > again. However, that just begs the question of why the file is there... > I found the file "pgdump_oid" and removed it. Running the dump script after that resulted in: "$Can not drop pgdump_oid table. Explanation from backend: 'pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. '." Running the same script (pg_dump js -o -c -f /home/js/psql/jhspiesdb.out) a second time: - 'Can not create pgdump_oid table. Explanation from backend: 'ERROR: cannot create pgdump_oid '. -- Trying after that to "drop table pgdump_oid" resulted in: js=> drop table pgdump_oid; QUERY: drop table pgdump_oid; ERROR: Relation 'pgdump_oid' does not exist ERROR: Relation 'pgdump_oid' does not exist Also doing \dt in psql resulted in an seemingly endless loop with no output produced. I had to kill psql. However, my other applications using postgresql is still working. I also tried reinstalling postgresql. The process also resulted in errors (one was a message about not being able to find a file with the name "can_i_run") and when the installation script (Debian) tried to dump the data it also failed. In the end I had postgresql running again but the same pg_dump problem remains. Regards. Johann -- J.H. Spies - Tel. 082 782 0336 "Jesus said unto her, I am the resurrection, and the life; he that believeth in me, though he were dead, yet shall he live"John 11:25
Re: [SQL] pg_dump error - further information
On Mon, Jan 15, 2001 at 08:36:46PM -0500, Tom Lane wrote: > I would guess that this means there is a file named "pgdump_oid" hanging > around in the database directory, no doubt leftover from some previous > pg_dump cycle. Manually removing the file should get you up and running > again. However, that just begs the question of why the file is there... I found that I could dump another database, but not the one with my data. So there is a problem in my database. Johann -- J.H. Spies - Tel. 082 782 0336 "Jesus said unto her, I am the resurrection, and the life; he that believeth in me, though he were dead, yet shall he live"John 11:25
[SQL] deferred constraints failing on commit
Hi. I'm having trouble with committing a transaction. Intuitively it should work but does not. I've got a table with 2 foreign keys, minrev and maxrev. They refer to a the revisionid value in another table. I need to update the second table to reduce the revisionid, then insert a new row. At the end of this all the keys match up yet the commit fails. urdr=> begin; BEGIN urdr=> update objects set revisionid=2 where id=2 and revisionid=; UPDATE 1 urdr=> insert into objects (id,typeid,repositoryid,parentid,deleted,permissions,revisionid,name) values (2,2,1,NULL,'f',NULL,,'test.sql'); INSERT 246107 1 urdr=> select id,revisionid from objects; id | revisionid + 1 | 2 | 1 2 | 2 2 | (4 rows) urdr=> select * from objcatalog ; objectid | repositoryid | minrev | maxrev | key| data --+--+--+--+--+-- 2 |1 | | | mimetype |text/plain (1 row) urdr=> commit; ERROR: referential integrity violation - key in objects still referenced from objcatalog At commit all the keys check out properly. minrev and maxrev both point to the same revisionid in the row we just inserted. Is this a bug or me just misreading how things should work again? -Michael _ http://fastmail.ca/ - Fast Free Web Email for Canadians
Re: [SQL] Question #4 about PL/PGSQL
Josh Berkus wrote: > Folks, > > Oh, yes, one more: > > 4. If I pass a NULL to any of the parameters of a PL/PGSQL > function, any (other) VARCHAR parameters are set to NULL as > well. Fixed in 7.1. Impossible to fix in 7.0.x or earlier due to missing capabilities of the function manager. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
Re: [SQL] Three questions regarding PL/PGSQL
Josh Berkus wrote: > Folks, > > 1. While I am able to use the %TYPE declaration within > PL/PGSQL functions, I am unable to use this declaration in > the parameters for the function -- I get 'Parse Error at or > near "."' I assume you're trying to do something like CREATE FUNCTION myfunc(mytab.x%TYPE, mytab.y%TYPE) ... because that's the only way I've found to get this error message. Not PL/pgSQL's error here. It's the main parser interpreting the CREATE FUNCTION utility statement that doesn't know how to get it. Indeed, a good idea (for 7.2). Bruce, put it onto TODO please. > 2. When I have a PL/PGSQL function return a custom message > using a VARCHAR return value, I get backslashes in front of > all of the spaces in the message. Can't reproduce that in 7.1(BETA). Could you send a little sql snippet reproducing the behaviour? > 3. Given the odd/weak exception handling within the current > Postgres database engine, has anyone developed strategies to > make certain that their PL/PGSQL functions do not perform > inconsistent updates? If so, can you give some examples? Dunno what's exactly meant by that. Up to now we don't have savepoints and thus, anything done eventually in a PL/pgSQL trigger or function will allways roll back if a transaction get's aborted. Single statements (outside transaction block) have their own transaction, so nothing to worry about. Second you could mean what's been discussed over and over again under subjects like "LOCK arbitrary string" and such, to prevent functions to try things that could produce errors in the first place. Lookup those threads in the archives. Or you could mean to prevent that a trigger, that you expect to UPDATE/DELETE an exact number of rows. Here you could check after the statement in question with GET DIAGNOSTICS (new feature in 7.1) if the correct number of rows has been hit. > Anybody (Jan?) who can shed some light on the above will > receive my enthusiastic gratitude in ASCII text. Some sql examples would allways help. > P.P.S. My most heartfelt gratitude to Jan Wieck for writing > some decent compile error text into the PL/PGSQL compiler, > and to Constantin Teodorescu for putting a terrific function > editor into pgaccess! Getting better compile error messages (anything else than "parse error at or near ...") isn't easy in yacc/bison. Of course, the PL/pgSQL function handler does write some more as DEBUG messages to the Postmaster log. Unfortunately, these don't show up at the frontend side and cannot easily get turned into NOTICE ones because at that time the original ERROR has already been sent to the client and emitting NOTICE's then could confuse the fe/be protocol. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
[SQL] www page - need a dbase? interested ?
Hi Everybody... Who can design a secure database for my www project? I don't have any idea where to start? What language to use? Sugestions? Will you be interested in doing it? ($) E-mail : momomor(at)dingoblue.net.au at=@ Greetings from Australia...:)
Re: [INTERFACES] Re: [SQL] improve performance
Tom Lane wrote: > > > I've heard lots of people want to increase BLCKSZ, but you're the first > one who ever wanted to reduce it. You sure you want to do this? It's > going to make the maximum row length uncomfortably short. And it may even not work, as some system tables (that are also affected by this) may need the full 8k. AFAIK it has never been tested with BLCKSZ < 8k -- Hannu
[SQL] I want to know the Query in order to insert the sound file into the database
hi, I am having a problem in inserting a sound file into the database(i am using oracle 8). I heard that I can use Blob datatype for this but i don't know how to use it or how to write a query for that .So if you know this please tell me as soon as possible. Thank you.
[SQL] important
hi, I am having a problem in inserting a sound file into the database(i am using oracle 8). I heard that I can use Blob datatype for this but i don't know how to use it or how to write a query for that .So if you know this please tell me as soon as possible. Thank you.
[SQL] outer join in PostgreSql
I need help, which is the symbol used in postgreSql to carry out the outer join. in oracle the is used (+) in sybase the is used * and in postgreSql? thank you Hipp Mauricio
[SQL] Re: Querying date interval
> > select * from testdate where field1 between '2000-10-01' and > > '2000-11-30' ; > > > >field1 > > > > 2000-09-30 < why is it here?? > > 2000-10-20 > > 2000-11-25 > > It works fine for me (7.0.3, Debian GNU/Linux "unstable"). > > I suspect your problem has to do with the "DATESTYLE" setting you're using; > does it help if you do "SET DATESTYLE TO 'ISO'" prior to INSERTing your test > values? Just reporting, I´ve tried: \set DATESTYLE ´ISO´ before adding values, but the result is still wrong. Thanks anyway, -- Renato Sao Paulo - SP - Brasil [EMAIL PROTECTED]
[SQL] Re: [INTERFACES] improve performance
On Mon, 15 Jan 2001, Alexaki Sofia wrote: > Hello, > > A) > I am going to load a huge amount of data in the DBMS using JDBC > and I want to reduce as much as possible the required loading time. > > Initially I loaded all data in one transaction. Subsequently, I increased > the number of buffers and disabled fsync() (-o -F) and I loaded the > data again but the performance was almost unchanged. Does it make sense?? Yes. Syncing is only done at the end of a transaction. The difference between one fsync() and none is not measurable. Large amounts of buffers helps in read performance, not write performance. > How can I improve performance? To note that no indexes are created on the > tables and that I load both "insert into" and "create table" statements. > -- > B) > I want to reduce the space (8 Kb) allocated by > DBMS when more space is required to load the data in a table. I reduced > the parameter BLCKSZ but the space allocated remains the same, that is 8 > KB. > How can the allocated space be reduced? If the space was reduced > would loading time and query time increase? No. Reducing the blocksize does not sound like a good idea. Why would you want to reduce the amount of allocated space? I actually wish that Postgres would preallocate a lot of blocks in advance (ie. 100 x 8KB). That would probably improve performance, as it would not have to keep re-extending the file. The best way to improve write performance, is to get faster disks connecting to a fast interface. Also, get lots of disks (4 to 6). Pair them into RAID1 sets, then strip over them. Use a RAID controler with a battery backed cache in write-back (write cache) mode. > Thank you in advance for your help > Sofia Alexaki Tom
[SQL] Re: [INTERFACES] outer join in PostgreSql
Mauricio Hipp Werner wrote: > > I need help, which is the symbol used in postgreSql to carry out the outer > join. > > in oracle the is used (+) > in sybase the is used * and > in postgreSql? The PostgreSQL outer join is accomplished using SQL92 syntax. You will not find real outer joins *except* in the current beta release, and beware that there may be some tweaks to the grammar to help with conformance to the standard. In any case, check the standard or try something like "select * from t1 left outer join t2 on (i)".
[SQL] Re: [INTERFACES] outer join in PostgreSql
On Mon, Jan 15, 2001 at 06:16:00PM -0400, Mauricio Hipp Werner wrote: > I need help, which is the symbol used in postgreSql to carry out the outer > join. > > in oracle the is used (+) > in sybase the is used * and > in postgreSql? I don't really understand your question, but from src/test/regress/sql/join.sql some examples of use are: -- -- Outer joins -- Note that OUTER is a noise word -- SELECT '' AS "xxx", * FROM J1_TBL LEFT OUTER JOIN J2_TBL USING (i); SELECT '' AS "xxx", * FROM J1_TBL LEFT JOIN J2_TBL USING (i); SELECT '' AS "xxx", * FROM J1_TBL RIGHT OUTER JOIN J2_TBL USING (i); SELECT '' AS "xxx", * FROM J1_TBL RIGHT JOIN J2_TBL USING (i); SELECT '' AS "xxx", * FROM J1_TBL FULL OUTER JOIN J2_TBL USING (i); SELECT '' AS "xxx", * FROM J1_TBL FULL JOIN J2_TBL USING (i); SELECT '' AS "xxx", * FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (k = 1); SELECT '' AS "xxx", * FROM J1_TBL LEFT JOIN J2_TBL USING (i) WHERE (i = 1); Cheers, Patrick
Re: [SQL] outer join in PostgreSql
On Mon, 15 Jan 2001, Mauricio Hipp Werner wrote: MHW> in oracle the is used (+) MHW> in sybase the is used * and MHW> in postgreSql? In PostgreSQL we have no outer joins at all. But it is promised they are going to arrive soon, perhaps in 7.1? Bye Borek -- = BOREK LUPOMESKYUsti nad Labem, Czech Republic, Europe WWW: http://www.volny.cz/borekl/ PGP keyid: B6A06AEB ==[ MIME/ISO-8859-2 & PGP encrypted mail welcome ]===
Re: [SQL] deferred constraints failing on commit
Here is a test case that illustrates the problem. I figured I was doing it all wrong before and didn't bother to distill and include a test case. create table objects( revisionid int4, primary key (revisionid)); create table objcatalog( minrev int4, maxrev int4, foreign key (minrev) references objects(revisionid) INITIALLY DEFERRED, foreign key (maxrev) references objects(revisionid) INITIALLY DEFERRED); insert into objects values (999); insert into objcatalog values (999,999); begin; SET CONSTRAINTS ALL DEFERRED; update objects set revisionid=1; insert into objects values (999); select * from objects; select * from objcatalog; commit; -Michael > Can you send the full schema of the tables you are using for > this? > > On Tue, 16 Jan 2001, Michael Richards wrote: > >> Hi. >> >> I'm having trouble with committing a transaction. Intuitively it >> should work but does not. >> >> I've got a table with 2 foreign keys, minrev and maxrev. They >> refer to a the revisionid value in another table. I need to >> update the second table to reduce the revisionid, then insert a >> new row. At the end of this all the keys match up yet the commit >> fails. >> >> urdr=> begin; >> BEGIN >> urdr=> update objects set revisionid=2 where id=2 and >> revisionid=; >> >> UPDATE 1 >> urdr=> insert into objects >> (id,typeid,repositoryid,parentid,deleted,permissions,revisionid,n >> ame) values (2,2,1,NULL,'f',NULL,,'test.sql'); >> INSERT 246107 1 >> urdr=> select id,revisionid from objects; >> id | revisionid >> + >> 1 | >> 2 | 1 >> 2 | 2 >> 2 | >> (4 rows) >> urdr=> select * from objcatalog ; >> objectid | repositoryid | minrev | maxrev | key| data >> --+--+--+--+--+-- >> 2 |1 | | | mimetype >> |text/plain (1 row) >> >> urdr=> commit; >> ERROR: referential integrity violation - key in >> objects still referenced from objcatalog >> >> At commit all the keys check out properly. minrev and maxrev both >> point to the same revisionid in the row we just inserted. >> >> Is this a bug or me just misreading how things should work again? >> >> -Michael >> _ >> http://fastmail.ca/ - Fast Free Web Email for Canadians >> _ http://fastmail.ca/ - Fast Free Web Email for Canadians
[SQL] python+postgresql
I wanted to compile python will an installed postgres package ...can i do it .How? TIA Sharmad
Re: [SQL] Three questions regarding PL/PGSQL
Jan, > I assume you're trying to do something like > > CREATE FUNCTION myfunc(mytab.x%TYPE, mytab.y%TYPE) ... > > because that's the only way I've found to get this error That's correct. > Indeed, a good idea (for 7.2). Bruce, put it onto TODO > please. Thanks! I'm a little surprised that this hasn't come up before -- after all, why did you include PLSQL-style %TYPE and %ROWTYPE declarations if not for parameters? > Can't reproduce that in 7.1(BETA). Could you send a little > sql snippet reproducing the behaviour? Sure, when I get home. I've a feeling that it's related to the Function handler in 7.0.x storing functions as TEXT. What I'm trying to make sure of is that it's not related to using PGAccess. > Dunno what's exactly meant by that. Up to now we don't have > savepoints and thus, anything done eventually in a PL/pgSQL > trigger or function will allways roll back if a transaction > get's aborted. Single statements (outside transaction block) > have their own transaction, so nothing to worry about. What I'm talking about is how, if an error occurs, the entire function rolls back, not just a selected portion. I can't even include a BEGIN TRANSACTION statment in a function; it errors out on compile. Nor can I return a custom error message in place of a database error. ALso, in other database engines, I've been able to use transactions to prevent the interleaving of conflicting updates on the database server. For example, I have some functions that insert a row into a table and then report back the ID of the new row: INSERT INTO clients ( ... ) VALUES ( ... ); SELECT CURRVAL(client_id) INTO new_client; It's vitally important that another operation on the clients table does not execute between the INSERT and the SELECT CURRVAL. It may be that by creating transactions by default PGSQL functions are alredy doing this; some reassurance on that count would be nice. > Some sql examples would allways help. More later when I get back to my PGSQL server. > Getting better compile error messages (anything else than > "parse error at or near ...") isn't easy in yacc/bison. Of > course, the PL/pgSQL function handler does write some more as > DEBUG messages to the Postmaster log. Unfortunately, these > don't show up at the frontend side and cannot easily get > turned into NOTICE ones because at that time the original > ERROR has already been sent to the client and emitting > NOTICE's then could confuse the fe/be protocol. Hey, just the fact that you spit back "Error on Line 38" cuts my debugging time in half over the SQL handler's "Error at or near ';'" Of course, running a tail on the postmaster log helps, too ... -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
[SQL] Boolean and Bit
Hello All, This is my first post (so be gentle with me)... Is there a searchable archive? I would like suggestions and examples of adding SQL-92 data type BIT compatibility to a PostgreSQL schema. >From the doc's I gather you can "CREATE TYPE bit" with storage int or int4... but I don't know about the input/output for zero and one. Should SQL (ODBC) be able to ask "WHERE bitfield;" or should it ask "WHERE bitfield = 1;" ? Any response gratefully recognized... Keith
Re: [SQL] Boolean and Bit
Keith, > This is my first post (so be gentle with me)... Well, yea-hah! Fresh meat, boys! Bring out the branding iron and the Wayne Newton B-sides! > I would like suggestions and examples of adding SQL-92 > data type BIT compatibility to a PostgreSQL schema. First let me ask you: Why do you want a Bit type? What purpose does it serve that INT2 and BOOLEAN do not? I'm not being sarcastic -- this is an important question to ask before creating *any* custom type on any RDBMS. -Josh Berkus
[SQL] Bruce's Book and Built-in Functions
Folks, Well, I now have a copy of Bruce's book on order. Bruce, if your sales have been slow, don't let A-W blame it on the online draft. According to Stacy's, it takes them an average of 7 working days to get Ingram to cough up a new copy, which is twice the normal period for tech books. As a result, they're out of stock a lot. Plus www.postgresql.org could do a little more to promote the book. There's *still* nothing on the web site to tell me the book's in print. HINT, HINT. So, while I wait for my copy ... I can't find in the online docs anywhere a comprehensive list of built-in functions. You know, stuff like CURRVAL() and NOW(). Can anyone point me to such a list? Guessing parameters is getting frustrating! -Josh
[SQL] Re: Bruce's Book and Built-in Functions
On 16 Jan 2001 at 20:40 (-0800), Josh Berkus wrote: | So, while I wait for my copy ... I can't find in the online | docs anywhere a comprehensive list of built-in functions. | You know, stuff like CURRVAL() and NOW(). Can anyone point | me to such a list? Guessing parameters is getting | frustrating! psql -c '\df' > pg.functions pg.functions will then contain a list of all registered functions, return types, as well as parameters. b
Re: [SQL] Bruce's Book and Built-in Functions
[ Charset ISO-8859-1 unsupported, converting... ] > Folks, > > Well, I now have a copy of Bruce's book on order. Bruce, if > your sales have been slow, don't let A-W blame it on the > online draft. According to Stacy's, it takes them an Sold 2,155 copies in the first month. I know they think that is a good sales number. Having it online is clearly a win. Frankly, I can't buy a book without seeing it. I need to know it is written in style that emphasizes concepts, rather than emphasizing examples. So, to choose a book, I either have to see it in a book store, or browse it online. > average of 7 working days to get Ingram to cough up a new > copy, which is twice the normal period for tech books. As a > result, they're out of stock a lot. They only printed 6k copies, so they went through >1/3 of the books in the first month. My guess is that they are trying to prioritize the distribution. > > Plus www.postgresql.org could do a little more to promote > the book. There's *still* nothing on the web site to tell > me the book's in print. HINT, HINT. Well, the web site mentions my book, and the book web page says it is in print. > > So, while I wait for my copy ... I can't find in the online > docs anywhere a comprehensive list of built-in functions. > You know, stuff like CURRVAL() and NOW(). Can anyone point > me to such a list? Guessing parameters is getting > frustrating! Sure, chapter 9 has it. See the online version. There is a huge table, though currval is mentioned in the sequences chapter because it is not like normal functions. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
[SQL] Re: Boolean and Bit
Keith, > This is a compatibility issue. While I prefer to use > BOOLEAN, this is SQL3 > and not available on the (unfortunately must use) > MS-SQL/MSDE platform. > > My options are to use a CHAR field and re-write my code > for "T" and "F" or > an int field and re-write my code to use "field=0" and > "field<>0" Given that all the MS-SQL BIT field is, is INT1, using INT2 should not be much of a problem. Go ahead an create a custom type based on INT2 and add constraints to prevent any values outside of the range of 0 and 1. This is where the SQL92 DOMAIN (not, as far as I know, available in PGSQL) construction would be useful in PostgreSQL instead of TYPE (Tom?). The problem with TYPE is that you theoretically need to define a whole set of operators for your TYPE, while DOMAIN is a bit simpler. > I would like to distribute a script (SQL) file to our > users to update > databases to new versions... obstacles include > BOOLEAN/BIT and the > inconsistent use of BLOB/MEMO/[long]varchar(4096). Well, yes. This is beacause BLOBs are NOT part of the SQL standard and IMHO a bad idea relationally; thus their implementation is entirely proprietary to the RDBMS. The solution is not to use BLOBs. > Distributing schema patches is proving troublesome across > multiple > platforms. Yup. Yer in for a world of pain, sonny. Hope you get paid hourly. -Josh
