[GENERAL] binary operators on integers
I want to do simple and/or/xor/test operations on integers but PostgreSQL seems not to support this. I found in conrib area the 'bit' package, which supports 'SQL-compliant bitstrings' but this is too weird for me and anyway it crashes too. So, my question 'How can I do this?' Is there any plans to implement those operators for integer types, or good reasons why they cannot be implemented? Should I try to implement those myself or start debugging the 'bit' package? My situation: I want to implement a 'capabilities' field for application server, so different bits are for different capabilities. TIA. -- marko
[GENERAL] Geometric Operations
Hi, long time listener first time caller. Does anybody have any definative info on the geometric data types? for example is there an easy way of returning the x,y co-ords seperatly for the box type ie select x1,y1,x2,y2 from . at the moment it looks like I'm going to have to parse out the results to separate the XY co-ords. Can't find much info at all on the geometric data types at all on the Postgres site and the doc's are sparse to say the least Any help appreciated Keith
[GENERAL] RE: Error in servlet
On Fri, 15 Sep 2000, Tim Kientzle wrote: > The Statement variable here CANNOT be a class > variable unless you're taking other steps to > synchronize access. Otherwise, you risk having > two different threads trying to manipulate the > same statement object at the same time. OK, I followed your hint and made statement and resultset local in all methods. > The Connection object is what holds the connection > to the database. Whether or not that can safely > be class scope depends on your particular JDBC driver > and how you're using it. This might work. Hmm, I'm using the driver shipped with Debian which is from ftp://ftp.postgresql.org/pub/postgresql.6.4.2.tar.gz What about this? Would it work. Would anybody recommend the driver from http://www.retep.org.uk/postgres/ ? > If you're not using auto-commit, this won't work, > since each connection is a single transaction > environment, and you'll have multiple transactions > interfering with one another. Could you explain this a little bit more detailed for a beginner or is there any information source about this topic? > Another ugly problem you'll encounter: many database > servers don't like long-lived connections, and will > spontaneously drop them after a few hours. At the very > least, you should timestamp when you opened the connection > (long timestamp = System.currentTimeMillis();) > and close/reopen it every 30 minutes or so. Also, > you'll want to be sure to ping the connection regularly > in case something goes down (like a bad network cable). You speak about "many database servers". What about PostgreSQL? > If you have a relatively low-traffic site, opening > one new connection for each request is not a real > problem. I've measured connection opens at around 0.1-0.2 > seconds on local MySQL and networked Oracle, which isn't at > all prohibitive for a lot of applications. Plus, that > approach is easy to understand and very reliable. I think in my case it would be best to hold the connection open while performing the about 10 requests of the servlet which are necessary to build my web-pages. > If you have a higher-traffic site, look into connection > pooling. A good connection pool will cycle the connections, > open more if you need them, and can deal with a lot of other > issues as well. Speaking about connection pooling I considered another problem: I'm using JServ and it seems to try to open more than one connection to the PostgreSQL server. Formerly I used MS SQL server and there where 5 open connections per servlet. How do I check the open connections of a servlet to the PostgreSQL server? How do I enforce connection pooling? Kind regards Andreas.
Re: [GENERAL] Re: LOCK TABLE
On Sun, Sep 17, 2000 at 12:50:26PM -0700, Stephan Szabo wrote: > > If you always SELECT ... FOR UPDATE (in all transactions that access it), > then the second one will not see the DB state before the transaction is > started, because the row is locked and the second transaction won't be > able to get its lock and will instead wait. Admittedly this lowers your > ability to have concurrent reads of the same rows as well, so you would > want the other transactions to hold the lock for as short a time as > possible. I was wondering, if I do something like select * from person order by surname for update limit 1 offset 10; as there is no where clause, am I locking the whole table? Cheers, Patrick
[GENERAL] multiple referential integrity
suppose i have two tables whose primary keys i want to be generated from the same sequence and a third table where i want to establish a foreign key based on a primary key from either of the two initial tables whose id's are from the same sequence. e.g., t1t2 ---- idid t3 -- t_id where t1.id and t2.id each get their values from, say, t_id_seq, and t3.t_id references t1.id and references t2.id. i thought this might be possible in postgres by specifying two references as constraints in t3, e.g., create table t3 ( t_id references t1( id ) references t2( id ) this is valid in postgres. unfortunately, the behavior seems to be that it expects _both_ tables t1 and t2 to have the same value in order to insert successfully into t3, e.g., insert into t1 ( id ) values( 1 ); insert into t3 ( t_id ) values( 1 ); will cause an error because it can't find "1" in t2.id. is there any way to have stronger referential integrity in such a situation than by merely relying on the unique values of a sequence such as t_id_seq, which is shared by two (or more) tables for generation of primary key values? any suggestions/explanations would be much appreciated -tfo
[GENERAL] WTF is going on with PG_VERSION?
Greetings. The problem is: from time to time, PostgreSQL seems to crash. Inspection of the logs revealed the following: óÅÎ 18 15:53:06 arbat logger: FATAL 1: File '/var/lib/pgsql/PG_VERSION' does not exist or no read permission. Well, '/var/lib/pgsql/PG_VERSION' does exist, it has read permission for user 'postgres' (I made it world readable, in fact, after I discovered this). Now, two questions: 1) Who the hell needs to read this file? 2) Why can't he do it? -- Yours, Alexey V. Borzov
Re: [GENERAL] RE: Error in servlet
On Fri, 15 Sep 2000, Andreas Tille wrote:
> On Thu, 14 Sep 2000, chris markiewicz wrote:
>
> > could this be a servlet/thread issue? i cannot tell from the code snippet,
> Solved. I really stupidly forgot an rs.next() :-(((.
>
> > but remember that variables in a servlet with class scope are essentially
> > static. (i am guessing that query, rs, stmt, etc are all class scope. this
> > is very dangerous, in the programming sense of the word...) i've had
> > similar (but not the same) problems before. as a general rule, i NEVER put
> > a class scope variable in a servlet unless i really mean to.
> Well, that might be true for query and rs and I'll change that, but
> in my opinion
>
> public class ServletSQLClass
> {
> private Connection con;
> private Statement stmt;
>
> ...
> con = DriverManager.getConnection(url,user,passwd);
> stmt = con.createStatement();
> ...
> }
>
> con and stmt have to be class scope to hold the connection to the
> database and don't have to reopen over and over. Or did I understand
> something wrong?
IMHO, only Connection should be of class scope, as you can create as many
Statement/PreparedStatements as you want.
Peter
--
Peter T Mount [EMAIL PROTECTED] http://www.retep.org.uk
PostgreSQL JDBC Driver http://www.retep.org.uk/postgres/
Java PDF Generator http://www.retep.org.uk/pdf/
Re: [GENERAL] WTF is going on with PG_VERSION?
On Mon, 18 Sep 2000, Alexey V. Borzov wrote: > Greetings. > > The problem is: from time to time, PostgreSQL seems to crash. > Inspection of the logs revealed the following: > > óÅÎ 18 15:53:06 arbat logger: FATAL 1: File '/var/lib/pgsql/PG_VERSION' does not >exist or no read permission. > > Well, '/var/lib/pgsql/PG_VERSION' does exist, it has read permission > for user 'postgres' (I made it world readable, in fact, after I > discovered this). > > Now, two questions: > 1) Who the hell needs to read this file? > 2) Why can't he do it? What version fo PostgreSQL are you running?
[GENERAL] Oracle Conversions Tools
Hi, I've been poking through the archive and PostgreSQL sites looking for tools that would allow me to migrate from an Oracle DB. I'm getting the impression that there aren't any. Are there any projects underway to work on a tool like this (or a general Other DB -> PostgreSQL framework)? If not would anyone be interested in starting one up or working on it? I think that this would help PostgreSQL enormously. It's one thing thing to have a great OpenSource product but another to get acceptance in the "enterprise". I think one of Linux's undersung killer apps is Samba, because it greatly reduces the pain of moving to Linux/UNIX. By the same token, If I can pop up a window, go through a few dialogs, and end up with a PostgreSQL schema that makes this a lot easier to sell. Granted converting PL/SQL and what have you is just a bit more complicated :-) But I think at least a schema and data migration tool would be a powerful addition to the PostgreSQL arsenal. Just my two cents, Erich smime.p7s
[GENERAL] ODBC/IIS/PsotgreSQL
I've inherited a Web based application that was developed using VisualBASIC, IIS, and SQL Server 6.5 ... which for some reason or other decided to stop working. (Before I got it.) I'm pretty sure the issue is with SQL Server and authentication but my attempts to fix it have not been successful. So I got a 'very clever idea'. Since the application uses ODBC (and ADO) I should be able to just set up a PostgreSQL database and point the ODBC driver at it and let MS, IIS, VisualBASIC, and friends do their own thing. The problem is I can't seem to get things set up correctly. I am still researching but was hoping someone might have their own quick-and-dirty list of directions. Heck I'd go for a URL even ;-) TIA, Rod -- Roderick A. Anderson [EMAIL PROTECTED] Altoplanos Information Systems, Inc. Voice: 208.765.6149212 S. 11th Street, Suite 5 FAX: 208.664.5299 Coeur d'Alene, ID 83814
Re: [GENERAL] binary operators on integers
Marko Kreen writes: > I want to do simple and/or/xor/test operations on integers > but PostgreSQL seems not to support this. I found in conrib > area the 'bit' package, which supports 'SQL-compliant bitstrings' > but this is too weird for me and anyway it crashes too. These were the remainders of a slightly too late implementation attempt for 7.0. In 7.1 they should be fully functional. > My situation: I want to implement a 'capabilities' field for > application server, so different bits are for different > capabilities. Personally, I'd say using bit fields for that is pushing it a little too hard. You could use shortint fields, or char(1)'s if you are not that concerned about space. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
[GENERAL] SPI_modifytuple for varchar
Hi, my query is about using the function SPI_modifytuple for a varchar field. I have (for example) a table with a field my_field of type varchar(100). And I need to create a trigger that adds the phrase "hello, world" to any value, that user inserts (or updates) in field my_field. Using SPI_getvalue I can retrive the value that user tries to insert, I can concatenate this value with my phrase "hello world" using standard function strcat, but I do not understand what arguments should I pass to SPI_modifytuple function. Say my_value is the pointer to the concatenated string: char *my_value; If I use the function SPI_modifytuple( rel, newtuple, 1, attnums, my_value, NULL ) then my backend crashes. Can anybody say what I'm doing wrong or give me an example of modification of varchar value using SPI_* interface? Best regards, Alex
[GENERAL] Oracle Conversions Tools
I did it. I reverse engineered Oracle with ERWin, set the server to Watcom, converting domain data types. Then forward engineered it, then some small sed script to convert e.g. NUMBER(8) to int8 etc. Works fine with postgresql, especially the referential integrity is absolutely compatible. Delete on cascade etc works just fine. I like postgres due to that and I am currently converting my first M$-SQL project to postgres (smaller datadictionary). Yes, same procedure for reverse engineering worked also with Micro$oft SQL server. I would propably like to attend in an an open source project, ERWin costs an arm and a leg and, does not run on linux and does not support postgres nativly, and recently I saw that the homepage of logicwork can be baught, so I don't know if that company still exists. I already searched a while, first I supposed the TCM project was nearest do what I need, but it restricts itself to only draw lines, as far as I could see no near plans for code generation (sql). The output file is a propriatary format, not XML. And it uses motif, not KDE. I would do it differently. So I suppose it is necessary to do the drawing and the code generation from scratch, maybe even some reverse engeneering. So if somebody let me know. wfR ChrisA > Hi, > I've been poking through the archive and PostgreSQL sites looking for tools= > that would allow me to migrate from an Oracle DB. I'm getting the impress= > ion that there aren't any. Are there any projects underway to work on a to= > ol like this (or a general Other DB -> PostgreSQL framework)? If not would= > anyone be interested in starting one up or working on it?=20=20 > > I think that this would help PostgreSQL enormously. It's one thing thing t= > o have a great OpenSource product but another to get acceptance in the "ent= > erprise".
[GENERAL] Can't connect to 6.5.2 server with 7.0.2 client
I was just about to give postgres 7.0.2 a try on my development machine, but after installing it, I find that I can't connect to my 6.5.2 production servers. The following error message is reported by both psql and pgaccess, upon trying to connect: ERROR: MultiByte strings (MB) must be enabled to use this function The connection then fails. Is there some way around this, short of upgrading all my production servers? Cheers, Forest
Re: [GENERAL] Can't connect to 6.5.2 server with 7.0.2 client
> I was just about to give postgres 7.0.2 a try on my development machine, > but after installing it, I find that I can't connect to my 6.5.2 > production servers. The following error message is reported by both psql > and pgaccess, upon trying to connect: > > ERROR: MultiByte strings (MB) must be enabled to use this function > > The connection then fails. Is there some way around this, short of > upgrading all my production servers? Recompile your 7.0.2 without --enable-multibyte option. -- Tatsuo Ishii
[GENERAL] Psql Question
- Hello
- I had previous experience with Access and MySQL.
-Situation
- I am trying to create the equvilant of the following which is a mysql
command.
- Queston
- But I cannot figure out how to do this is postgresql
"mysql -u root -p mydb < mydb.dump"
- I was trying to create a test database using the following commands using a
very cliche example . This command works on mySQL and should be part of the
ANSI SQL standard
mydb=# INSERT INTO Customer (Customer_ID,Customer_Name,Customer_Address,Customer_Email)
mydb-# VALUES ('1','Danny Ho','99 Second Ave, Kingswood','[EMAIL PROTECTED]'),
mydb-# ('2','Randal Handel','54 Oxford Road, Cambridge','[EMAIL PROTECTED]')
mydb-# ;
-and I get the following errors :
ERROR: parser: parse error at or near ","
Looking forwrd to your feedback.,
dannyh
[EMAIL PROTECTED]
Re: [GENERAL] Psql Question
Danny wrote:
> - Hello
> - I had previous experience with Access and MySQL.
>
> -Situation
>
> - I am trying to create the equvilant of the following which is a mysql
> command.
>
> - Queston
> - But I cannot figure out how to do this is postgresql
>
> "mysql -u root -p mydb < mydb.dump"
>
I think:
psql -u somebody -d template1 < yourdb.dump
would work.
>
> - I was trying to create a test database using the following commands using a
> very cliche example . This command works on mySQL and should be part of the
> ANSI SQL standard
>
> mydb=# INSERT INTO Customer
>(Customer_ID,Customer_Name,Customer_Address,Customer_Email)
> mydb-# VALUES ('1','Danny Ho','99 Second Ave, Kingswood','[EMAIL PROTECTED]'),
> mydb-# ('2','Randal Handel','54 Oxford Road, Cambridge','[EMAIL PROTECTED]')
> mydb-# ;
>
you can't insert two values at the same time, you would have to use two INSERT.
>
> -and I get the following errors :
>
> ERROR: parser: parse error at or near ","
>
> Looking forwrd to your feedback.,
>
> dannyh
>
> [EMAIL PROTECTED]
[GENERAL] Permissions on databases (not on tables)
Colleagues. Could you please tell me how I can prevent a user from creating tables in a database belonging to another user? I login as user1, create a database, then logout, connect to the database as user2 and create tables in it. Is this behavior by design? And how can I prevent this from happening, i.e. restrict user rights per database? Thanks for any input. -- Victor Sudakov, VAS4-RIPE, VAS47-RIPN 2:5005/149@fidonet http://vas.tomsk.ru/
Re[2]: [GENERAL] WTF is going on with PG_VERSION?
Greetings. Monday, September 18, 2000, 10:38:37 PM, you wrote: >> óÅÎ 18 15:53:06 arbat logger: FATAL 1: File '/var/lib/pgsql/PG_VERSION' does not >exist or no read permission. >> >> Well, '/var/lib/pgsql/PG_VERSION' does exist, it has read permission >> for user 'postgres' (I made it world readable, in fact, after I >> discovered this). >> >> Now, two questions: >> 1) Who the hell needs to read this file? >> 2) Why can't he do it? THH> What version fo PostgreSQL are you running? I forgot the most important part... PostgreSQL 7.0.2 And it runs on Linux 2.2.17 SMP (The box has two Intel Pentiums II) -- Yours, Alexey V. Borzov
Re[2]: [GENERAL] WTF is going on with PG_VERSION?
Maybe it was moved for PostGres v7 (I'm still using 6.5.3 because it works
and I'm too lazy to upgrade. :-) but in older versions the PG_VERSION file
was in the data directory (ie, /usr/local/pgsql/data/ ) Try checking what
you are using for a data dir ("locate pg_database" should tell you what dir
it is) and move PG_VERSION in there. Of course, your data dir could be
/var/lib/pgsql, I dunno what evils RedHat does to the default Postgres
install path.
At 01:49 AM 9/19/00, Alexey V. Borzov wrote:
>Greetings.
>
>Monday, September 18, 2000, 10:38:37 PM, you wrote:
> >> óÅÎ 18 15:53:06 arbat logger: FATAL 1: File
> '/var/lib/pgsql/PG_VERSION' does not exist or no read permission.
> >>
> >> Well, '/var/lib/pgsql/PG_VERSION' does exist, it has read permission
> >> for user 'postgres' (I made it world readable, in fact, after I
> >> discovered this).
> >>
> >> Now, two questions:
> >> 1) Who the hell needs to read this file?
> >> 2) Why can't he do it?
>
>THH> What version fo PostgreSQL are you running?
>
>I forgot the most important part...
>PostgreSQL 7.0.2
>And it runs on Linux 2.2.17 SMP (The box has two Intel Pentiums II)
>
>--
>Yours, Alexey V. Borzov
