Re: [GENERAL] PL/java?
... > Java stored procedures are the #1 most-desired-by-me feature for PostgreSQL. > Oracle and Sybase are examples of databases that have this feature already. ... How does java manage its resources when it is an embedded component? When it is standalone, the Sun JDK seems to want to have a fixed maximum amount of memory, as an example. I'd hate to have a nice 10MB PostgreSQL backend calling a 256MB jvm to execute a simple small program. The other embedded languages we have start small and can grow dynamically. But I don't know what is possible in this regard for Java... - Thomas ---(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] PostgreSQL On the Mac? OS9 or OSX?
i've got it installed and running on osx on an imac :) james - Original Message - From: "Peter Eisentraut" <[EMAIL PROTECTED]> To: "Israel Evans" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, August 31, 2001 1:17 PM Subject: Re: PostgreSQL On the Mac? OS9 or OSX? > Israel Evans writes: > > > In looking over the documentation, I haven't seen any evidence of PostgreSQL > > working on the Mac. Is this the case, or am I missing something. > > You're missing something. ;-) > > 7.1 works on MacOS X. See the installation instructions. > > -- > Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Regarding Vacuumdb
Shaun Thomas wrote: > > On Tue, 28 Aug 2001, Bhuvaneswari wrote: > > > hi, > > I am getting the following error while doing vacuumdb, > > > > ERROR: mdopen: couldn't open test1: No such file or directory > > vacuumdb: database vacuum failed on db1. > > We got this error a lot in 6.5. Usually it means your table has somehow > been corrupted, and postgres doesn't want anything to do with it. It'll > show up, and you can even select from it, but doing so will crash the > back-end, and you can't run a vacuum or pg_dump on that database > successfully. Yes. This error was caused in older versions by doing something like: BEGIN; DROP TABLE test1; ABORT; The 6.5 version of PostgreSQL would remove the underlying file from the file system when DROP TABLE was executed, not when the transaction performed a commit, so the file was gone, but the entries in the system cataloge remained. This is fixed in newer versions. Hope that helps, Mike Mascari [EMAIL PROTECTED] ---(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] Newbie ...Function error (Stored Procedure)?
I think you need to double the single quotes around the sequence name:
(''seq1'').
Statements inside function definitions go through an extra level of parsing,
which strips off one set of single quotes.
- Original Message -
From: "Ron S" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Monday, August 27, 2001 2:34 PM
Subject: [GENERAL] Newbie ...Function error (Stored Procedure)?
> I have a sequence called seq1.
>
> In psql I am trying to create a function which simply calls this
> sequence with the nextval() function.
>
> CREATE FUNCTION testid()
> RETURNS INTEGER
> AS 'SELECT NEXTVAL('seq1');'
> LANGUAGE 'SQL';
>
>
> I get the following error
> ERROR: parser: parser error at or near "seq1"
>
> I can call nextval('seq1') by itself with now error.
>
> What am I doing wrong? I can't for the life of me figure this
> seemingly simple
> error out ;)
>
> Postgresql 7.0.3 on Mandrake 8.0
>
> Thanks,
> Ron
>
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [GENERAL] Deployment of PostgreSQL Applications
> > 2) More importantly, is it possible to prevent a customer from peeking into > > said database once it is deployed on their machine? ROT13 it, then threaten them with the DMCA. (Yes, that was a joke.) steve ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Regarding Vacuumdb
Before doing anything so drastic, you may want to check the archives for recent messages on a similar error. IIRC, this problem can be caused by rolling back a "drop table" command. The effect is that the table structure in the database remains, but the physical file is gone. The fix is to create a file with the correct name in the data directory for that database, then drop the table. If you are running version 7.1, the trick will be to figure out the correct name for that file. You can find discussion on that in the archives too. - Original Message - From: "Shaun Thomas" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, August 31, 2001 11:48 AM Subject: Re: [GENERAL] Regarding Vacuumdb > On Tue, 28 Aug 2001, Bhuvaneswari wrote: > > > hi, > > I am getting the following error while doing vacuumdb, > > > > ERROR: mdopen: couldn't open test1: No such file or directory > > vacuumdb: database vacuum failed on db1. > > We got this error a lot in 6.5. Usually it means your table has somehow > been corrupted, and postgres doesn't want anything to do with it. It'll > show up, and you can even select from it, but doing so will crash the > back-end, and you can't run a vacuum or pg_dump on that database > successfully. > > You'll have to do a table-by-table pg_dump, destroy the DB, and reimport > everything. You'll have to rebuild the corrupted table from scratch, > since you might not be able to dump it. > > Either way, it's a lot of work. Just be careful. > > -- > +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ > | Shaun M. ThomasINN Database Programmer | > | Phone: (309) 743-0812 Fax : (309) 743-0830| > | Email: [EMAIL PROTECTED]AIM : trifthen | > | Web : hamster.lee.net | > | | > | "Most of our lives are about proving something, either to | > | ourselves or to someone else." | > | -- Anonymous | > +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PL/java?
On Thu, 30 Aug 2001, Marshall Spight wrote: > This was a major issue in 1996. It's been solved for several years now, but > the perception of Java having a speed problem remains. > > Java stored procedures are the #1 most-desired-by-me feature for PostgreSQL. > Oracle and Sybase are examples of databases that have this feature already. > (Strangely, Microsoft's database doesn't have it :-) Its not a perception. Java is still a dog. Again, see my mail message of few days ago regarding faking PL/java using pl/perl ;) -alex ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] PgAdmin question - superuser
Hi - I set up a user and database using psql. Then when I added the DSN connection for PgAdmin and tried it out with my new user id, it told me I had to log in as superuser. So, I did. Then I disconnected and logged in again as the new user I had created. The second time, it worked. Anybody know what happens behind the scenes that makes you log in as superuser the first time? Is there some kind of initialization going on? -- Susan Lane DPN, Incorporated 4631 Spring Mountain Road Las Vegas, NV 89102 Email [EMAIL PROTECTED] Ph. (702) 873-3282 Fax (702) 873-3913 http://www.dpn.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Deployment of PostgreSQL Applications
On Fri, Aug 31, 2001 at 08:59:53AM -0400, Mike Arace wrote: > 2 questions: I'll let someone else handle the first one. > 2) More importantly, is it possible to prevent a customer from peeking into > said database once it is deployed on their machine? A large part of what > makes my application proprietary is the data model in the database, and it'd > be tough to maintain a competative edge when everyone can see exactly how I > do things in the database by logging into their postgres account, adding > some users and changing permissions on their machine. I really need to make > sure the database is bulletproof before I can begin deployment. It's not really possible to hide a database schema. Once you've logged in, all the information is there describing all the table and fields. This data is used by the database itself to process your queries. You could try to prevent logins into the database but if someone has root on that machine that's really not hard to get around. At worst they could read the database files directly. Seriously though, I'm wondering about your situation, who owns the data in the database? If it's your customer then shouldn't they have access to it? You talk about the data model. Is it really such a special data model that nobody else has thought of it before? P.S. Fortunatly, you can't patent database schemas last time I checked so at least the world is safe from that. -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > It would be nice if someone came up with a certification system that > actually separated those who can barely regurgitate what they crammed over > the last few weeks from those who command secret ninja networking powers. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] getting the oid for a new tuple in a BEFORE trigger
> we need to control database changes within BEFORE triggers. > There is no problem with triggers called by update, but there is > a problem with triggers called by insert. > > We strongly need to know the oid of a newly inserted tuple. > In this case, we use tg_newtuple of the TriggerData structure > passed to thetrigger function, and its t_data -> t_oid will > have the value '0'. > > Using BEFORE and AFTER triggers would make our lives much harder. > > Is there any way (even hack) to get the oid the newly > inserted tuple will receive? Just set t_data->t_oid = newoid() - this is what backend does in heapam.c:heap_insert(). Vadim ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] PostgreSQL On the Mac? OS9 or OSX?
Israel Evans writes: > In looking over the documentation, I haven't seen any evidence of PostgreSQL > working on the Mac. Is this the case, or am I missing something. You're missing something. ;-) 7.1 works on MacOS X. See the installation instructions. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Deployment of PostgreSQL Applications
On Fri, 31 Aug 2001, Mike Arace wrote: > 1) Is it legal to bundle Postgresql with another commercial application, > assuming the database will have to be significantly reconfigured and tuned? > (at the application level, not the source code level) I read over the > licenses I could find on the site and they seemed to imply that the answer > was yes, but I'd like to reaffirm that. Yes. > 2) More importantly, is it possible to prevent a customer from peeking into > said database once it is deployed on their machine? A large part of what > makes my application proprietary is the data model in the database, and it'd > be tough to maintain a competative edge when everyone can see exactly how I > do things in the database by logging into their postgres account, adding > some users and changing permissions on their machine. I really need to make > sure the database is bulletproof before I can begin deployment. In general, answer is no. You could try to fake it by not giving customer password for the database, but they could always poke around your app's files, or boot postgres single-user. If you change postgres code that it wouldn't boot singleuser, they can download postgres and recompile it, removing that restriction. Its 'security-through-obscurity'. You can prevent them from doing certain things, but the fact of the matter is, they have the physical access to the machine, and thus can read raw data on disk to find out what you are doing. -alex ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Deployment of PostgreSQL Applications
On Fri, Aug 31, 2001 at 08:59:53AM -0400, Mike Arace <[EMAIL PROTECTED]> wrote: > > 2) More importantly, is it possible to prevent a customer from peeking into > said database once it is deployed on their machine? A large part of what > makes my application proprietary is the data model in the database, and it'd > be tough to maintain a competative edge when everyone can see exactly how I > do things in the database by logging into their postgres account, adding > some users and changing permissions on their machine. I really need to make > sure the database is bulletproof before I can begin deployment. I don't think that the above is going to be technically possible if the code is running on machines controlled by your users. My guess is that either the database has to be run on your machines or you need some contract that legally prevents your customers from telling anyone about your data model. ---(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] Create table syntax
On Tue, Aug 28, 2001 at 06:52:36AM -, satish rao wrote: > Hi > > Entered below is the SQL create table syntax: > > CREATE TABLE lists ( > [listid] [int] IDENTITY (1, 1) NOT NULL , > [listname] [varchar] (200) NULL , > [listcreator] [varchar] (200) NULL , > [listdesc] [text] NULL) > > > We need to know that this syntax will work properly in > postgresql while creating the table and the > autoincrement option for the column(listid). If this > syntax is not correct for postgresql, we need the > correct syntax. Have you looked at the documentation? There is plenty on the website and psql has a brief summary when you type \h CREATE TABLE Unfortunatly my postgres server isn't booting right now (version mismatch) so I can't paste the result here... -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > It would be nice if someone came up with a certification system that > actually separated those who can barely regurgitate what they crammed over > the last few weeks from those who command secret ninja networking powers. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Deployment of PostgreSQL Applications
"Mike Arace" <[EMAIL PROTECTED]> writes: > 1) Is it legal to bundle Postgresql with another commercial application, > assuming the database will have to be significantly reconfigured and tuned? > (at the application level, not the source code level) I read over the > licenses I could find on the site and they seemed to imply that the answer > was yes, but I'd like to reaffirm that. Yes, the PG license is BSD, so you can do that. IANAL, but there shouldn't be any problem. > 2) More importantly, is it possible to prevent a customer from peeking into > said database once it is deployed on their machine? A large part of what > makes my application proprietary is the data model in the database, and it'd > be tough to maintain a competative edge when everyone can see exactly how I > do things in the database by logging into their postgres account, adding > some users and changing permissions on their machine. I really need to make > sure the database is bulletproof before I can begin deployment. Fat chance. Give it up. Think about it. It's *their* machine. They have root. The PG file format is, if not documented, reverse-engineerable from freely available sources. But they don't even need to do that, since they can just 'su' to the Postgres user and do whatever. Even with Oracle or another proprietary DB I don't think you could do what you want to do, unless you sell them the box, keep root on it yourself, never let them touch it, and make them go through a web/XML interface for everything, in which case PG will do just as well. If you want to prevent them peeking, use legal/contractual means, because technical ones won't work. Have fun. -Doug -- Free Dmitry Sklyarov! http://www.freesklyarov.org/ We will return to our regularly scheduled signature shortly. ---(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] urgently wanted - plpgsql.so for pg 6.4.2
"Lee Crampton" <[EMAIL PROTECTED]> writes: > If anyone could supply me with a plpgsql.so for postgres 6.4.2 I would be > very grateful indeed. > I need it for my planned triggers to work and have tried a later version of > the lib but it doesn't work. Ummm, I hate to break it to you, but if there's a reason you're still running 6.4.2, it had better be a really good one... ;) I started with 6.5.3 and it was pretty buggy/limited compared to current versions. -Doug -- Free Dmitry Sklyarov! http://www.freesklyarov.org/ We will return to our regularly scheduled signature shortly. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Regarding Vacuumdb
On Tue, 28 Aug 2001, Bhuvaneswari wrote: > hi, > I am getting the following error while doing vacuumdb, > > ERROR: mdopen: couldn't open test1: No such file or directory > vacuumdb: database vacuum failed on db1. We got this error a lot in 6.5. Usually it means your table has somehow been corrupted, and postgres doesn't want anything to do with it. It'll show up, and you can even select from it, but doing so will crash the back-end, and you can't run a vacuum or pg_dump on that database successfully. You'll have to do a table-by-table pg_dump, destroy the DB, and reimport everything. You'll have to rebuild the corrupted table from scratch, since you might not be able to dump it. Either way, it's a lot of work. Just be careful. -- +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ | Shaun M. ThomasINN Database Programmer | | Phone: (309) 743-0812 Fax : (309) 743-0830| | Email: [EMAIL PROTECTED]AIM : trifthen | | Web : hamster.lee.net | | | | "Most of our lives are about proving something, either to | | ourselves or to someone else." | | -- Anonymous | +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ ---(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] PostgreSQL On the Mac? OS9 or OSX?
Israel Evans <[EMAIL PROTECTED]> writes: > In looking over the documentation, I haven't seen any evidence of PostgreSQL > working on the Mac. Is this the case, or am I missing something. Has > anyone heard of it ever being used on a mac? It runs on OS X just fine from what I've heard, thought I haven't used it on that platform. I doubt it will ever run on "Classic" MacOS. -Doug -- Free Dmitry Sklyarov! http://www.freesklyarov.org/ We will return to our regularly scheduled signature shortly. ---(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] Indexes and Timestamp Comparisons
Sequential scans are always going to be faster than using the index if a large number of rows are going to be returned. Consider this: if you are doing regular DELETEs, will there be that many rows selected for deletion each time? Probably not, so Postgres will most likely use the index for those regular DELETEs. Take this into account, and pick some new, more realistic "WHERE" clauses for testing, and hopefully that will be the case. - Andrew > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]] On Behalf Of Josh Voils > Sent: Tuesday, 28 August 2001 4:41 AM > To: [EMAIL PROTECTED] > Subject: [GENERAL] Indexes and Timestamp Comparisons > > > I have a table called session: > Table "session" > Attribute | Type | Modifier > +--+-- > sessionid | character(32)| not null > usernumber | integer | not null > timestamp | timestamp with time zone | not null > > I have two indices on this table: moo, a btree on timestamp, > and session_pkey, the implicit unique btree on sessionid. > > I need to periodically eliminate timed-out entries from this > table. This is where my problem is. I created a test table > that has a million entries in it. I try to select the valid > session ids and it uses an index scan with moo; I try to > select the invalid sessionids and it uses a sequential scan > which is considerably slower. I was also playing around with > some other timestamps to compare values and found that just > by changing the > to a < I would change whether it used a seq > scan or an index scan (it wasn't consistent though as to > which scan it used for which boolean. Any suggestions on > what I can do? I vacuum analyzed the table right before I > performed these explains: > > This is a where for invalid sessionids. This is the one I > actually need to be able to run. main=# explain delete from > session where timestamp < '2001-08-27 12:26:03-05'::timestamp; > NOTICE: QUERY PLAN: > > Seq Scan on session (cost=0.00..22815.21 rows=999257 width=6) > > EXPLAIN > This is a where for valid sessionids. > main=# explain delete from session where timestamp > > '2001-08-27 12:26:03-05'::timestamp; > NOTICE: QUERY PLAN: > > Index Scan using moo on session (cost=0.00..3653.36 > rows=1000 width=6) > > EXPLAIN > > These are a couple of random wheres. If it's of any > consequence, this timestamp is before all of the timestamps > in the table. main=# explain delete from session where > timestamp < '2000-08-27 12:26:03-05'::timestamp; > NOTICE: QUERY PLAN: > > Index Scan using moo on session (cost=0.00..3653.36 > rows=1000 width=6) > > EXPLAIN > main=# explain delete from session where timestamp > > '2000-08-27 12:26:03-05'::timestamp; > NOTICE: QUERY PLAN: > > Seq Scan on session (cost=0.00..22815.21 rows=999257 width=6) > > EXPLAIN > > > Any help would be appreciated, > > Josh Voils > > > ---(end of > broadcast)--- > TIP 1: subscribe and unsubscribe commands go to > [EMAIL PROTECTED] > ---(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] PostgreSQL On the Mac? OS9 or OSX?
Indeed, on OSX. http://www.pgmac.com Israel Evans wrote: > > In looking over the documentation, I haven't seen any evidence of PostgreSQL > working on the Mac. Is this the case, or am I missing something. Has > anyone heard of it ever being used on a mac? > > Thanks, > > ~Israel~ > > ---(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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] !! Newbie question!!!! connecting to multiple databases
On Fri, 24 Aug 2001, uncleleo wrote: > I am attemping to create multiple databases with Postgresql ver. 7.0.3 > running on Mandrake 8.0 rpm. The tool that I am using is Pgadmin ver 7.1.0. > > Can someone tell me how I can connect to different databases in a single > select statement? Such as, I have a database named "Customer" and another > named "Products". I wish to Select from table A in the Customer database > and table A in Products database. I know that its possible in SQL Server and > other databases. You currently don't... See archives for lots of discussion of this recently (mostly part of the mysql/pgsql thread I think). ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] speed of communication and pgsql development
I'm having a hard time not chuckling on this end as this response continues to be received and then I think about the subject. Geoff -Original Message- From: Alex Knight [mailto:[EMAIL PROTECTED]] Sent: Sunday, August 26, 2001 4:08 PM To: Gowey, Geoffrey; [EMAIL PROTECTED] Subject: Re: [GENERAL] speed of communication and pgsql development There is an IRC channel already on EFnet... Visit #postgresql. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Gowey, Geoffrey Sent: Saturday, August 25, 2001 8:35 PM To: '[EMAIL PROTECTED]' Subject: [GENERAL] speed of communication and pgsql development Not to whine or anything, but I really do think that pgsql development seriously could be helped by usage of an official irc channel. Whether it be hosted on pgsql's official servers or on a 3rd party network such as undernet or openprojects.net I leave as a matter of discussion, but the mailing list really seems to have severe lag (which is painful when developing). Maybe continuously logging the irc channel and making the log searchable online could complement the mailing list? Anyway, just a thought. Geoff ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Deployment of PostgreSQL Applications
> 2) More importantly, is it possible to prevent a customer from peeking into > said database once it is deployed on their machine? A large part of what > makes my application proprietary is the data model in the database, and it'd > be tough to maintain a competative edge when everyone can see exactly how I > do things in the database by logging into their postgres account, adding > some users and changing permissions on their machine. I really need to make > sure the database is bulletproof before I can begin deployment. If the people that have your application have physical access (or even remote superuser access) to the machine on which the database resides then there is little you could do to prevent a knowledgeable person from getting anything he/she wanted from the database, circumventing the PostgreSQL security measures by reading the information right off the disk... I don't know how hard or easy it would be to get meaningful information this way but it's always going to be possible when people have superuser/physical access to the machine. -Mitch ---(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] Variable case database names
[EMAIL PROTECTED] writes: > How can i create variable case data bases ? Double quote the database name: psql -c 'create database "MiXeD";' -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Getting data out of database with a bad WAL file
> I've got a database where I've lost the WAL files. Since PG > won't start with > bad WAL files, is there anyway I can get the data out of the > actual database > files? contrib/pg_resetxlog. Please read README. Could you tell us how did you lose WAL files? Vadim ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] market share
Does anyone know of what market share Postgresql is in the RDBM market including Mysql along with Oracle, msql, etc.? Thanks Matt ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] handling results of pl/pgsql functions
Hi everybody , I' d like to know if there's a way to store the result of a pl/pgsql function in a file (like the \w command for the query buffer ). Thanks . ___ Do You Yahoo!? -- Un e-mail gratuit @yahoo.fr ! Yahoo! Courrier : http://fr.mail.yahoo.com ---(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] Too many open files in system FATAL2
On Thu, 30 Aug 2001, Christian MEUNIER wrote: > got the following happened yesterday: > > postmaster: StreamConnection: accept: Too many open files in system > postmaster: StreamConnection: accept: Too many open files in system > postmaster: StreamConnection: accept: Too many open files in system > 2001-08-30 03:04:27 FATAL 2: InitOpen(logfile 3 seg 199) failed: Too many > open files in system > Server process (pid 21508) exited with status 512 at Thu Aug 30 03:04:27 > 2001 > Terminating any active server processes... Most unix systems have a pre-set limit for the number of open file handles over every running application. If you're running a lot of applications on your server along with postgres, they may be consuming vital system resources (file handles) that postgres wants. Or, your database may just be making enough connections that it's consuming all open file handles. Whatever OS you're using, check the manual to see how to add more file handles. This may involve recompiling the kernel. Your other problem might be a deadlock. If postgres gets deadlocked in a transaction, or has a lock during a vacuum, all subsequent connections will connect, try a query and then wait indefinitely in an idle state. This keeps up until there are possibly hundreds (if you allow that many) postgres connections tying up more and more file handles until there are none left. In any case, I'd check the other apps first. Then, see if the kernel is compiled with an adequate amount of file handles. Then, check through your application for deadlock conditions and vacuums during transactions. (don't do that, by the way.) If you have a high-traffic DB with lots of inserts, updates, and deletes, your indexes might be disgustingly out of sync and turning your DB into a slow memory, cpu, and file-handle hogging dog. Postgres has a reindex command, run that on your DB and see if the problem goes away. -- +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ | Shaun M. ThomasINN Database Programmer | | Phone: (309) 743-0812 Fax : (309) 743-0830| | Email: [EMAIL PROTECTED]AIM : trifthen | | Web : hamster.lee.net | | | | "Most of our lives are about proving something, either to | | ourselves or to someone else." | | -- Anonymous | +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ ---(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] Locate on Max() and Group By
On Wed, 29 Aug 2001, A. Snelders wrote: > I have the next problem: > > I open an table with > > select dnkey, Max(dvnaam) as dvnaam2, Max(dvpersoonnr) as dvpersoonnr from > tbmedewerker > group by dnkey > > When I want to locate like > > TblTbPGSMedew.Locate( 'dvnaam2', 'Hallo', [ ]); > > then 'Hallo' isn't find Is 'Hallo' the maximum value for the group its in? You may want to turn up debugging to level 2 (I think that's the level) so the queries sent get written to the log to see what's being sent when you do the Locate(). ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Is it possible to default all number data to numeric type?
My company had a old program with written by Powerbuilder. It work fine in MSSQL 6.5. Now, my boss required to use another SQL server. Someone told me PostgreSQL is totally free and powerful. I installed it in a Linux machine (RedHat 7 + PostgreSQL 7.1.2), and transfer the old data from MSSQL 6.5 server. It work fine. However, when I run the old program. When I try to update some record with numeric type field, it show error as below. Unable to identify an operator '=' for types 'numeric' and 'float8'... You will have to retype this query using an explicit cast. The point is the software house closed, I can't find any programmer. >_< So, I want to know is it possible to set the numeric type to default?? Or any easy way to fix it up? P.S. My English is bad, pls. forgive me. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] 2 tables, joins and same name...
Hello, Here is 2 tables: airport - airport_id name code city_id destination --- destination_id dest_name ... airport_dep_id // using airport.airport_id (departure) airport_arr_id // using airport.airport_id has well (arrival) I have 2 columns in the second table that uses the same name column in the first table... I dont know how to formulate my SQL query... I want to select the destinations in the destination table with not the ID of each airport but their names. I can do a join with one but with the second one, I get no results... And this is confusing! select dest.dest_name, air.name as airport1, air.name as airport2 from destination, airport air where dest.airport_dep_id_id=air.airport_id and dest.airport_arr_id=air.airport_id; This is not good... Any help? Thanks! -- Marc Andre Paquin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Getting data out of database with a bad WAL file
Hi, I've got a database where I've lost the WAL files. Since PG won't start with bad WAL files, is there anyway I can get the data out of the actual database files? Thanks GB -- GB Clark II | Roaming FreeBSD Admin [EMAIL PROTECTED] | General Geek CTHULU for President - Why choose the lesser of two evils? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Error in trying to dump any database
Hi, I recently had a similar problem (7.1.2) and I suspect you are suffering the same. It turned out to (probably) be a compiler optimzation problem. I could do select int4(4::int8/4::int8); and it would work on a machine compiled under gcc 2.95.2 but failed with "int8 conversion to int4 is out of range" on a box comiled under 2.7.2.1. My problem was with int84() and its offending code is almost identical to dtoi4() which is this little piece of goods here. if ((num < INT_MIN) || (num > INT_MAX)) elog(ERROR, "dtoi4: integer out of range"); If I were you I'd change this to if (num < INT_MIN) elog(ERROR, "dtoi4: integer out of range - too small"); if (num > INT_MAX) elog(ERROR, "dtoi4: integer out of range - too big"); recompile and the (hopefully) watch the problem go away. On Thu, 30 Aug 2001, Martin Weinberg wrote: > I have a 7.1.2 system which gives the following error on any pg_dump: > > DumpComment: SELECT failed: 'ERROR: dtoi4: integer out of range > '. > > This happens with any database. E.g. I made a very tiny database > with a single table and two records and the same error and message > is obtained. Colin ---(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] PL/java?
>> -Original Message- >> From: Dr. Evil [mailto:[EMAIL PROTECTED]] >> Sent: Saturday, August 25, 2001 7:38 PM >> To: [EMAIL PROTECTED] >> Subject: [GENERAL] PL/java? >> >> >> What do you think of having java as a procedural language available in >> PG? It seems like java has many advantages. >> >> I'm just wondering if people have thoughts or ideas on this, and if >> someone is actually working on it, that would be cool. >""Gowey, Geoffrey"" <[EMAIL PROTECTED]> wrote in message E15F4B031E17D5118B18009027F67927DAC0@SERVER">news:E15F4B031E17D5118B18009027F67927DAC0@SERVER... > probably a bad idea. From what I've heard the speed of your java program is > wholely dependent on the speed of your vm (and most aren't that quick). > Although it would be nice to have just to say we have it and mysql doesn't > (then again mysql doesn't have a whole lot of things that pgsql already > has). > > Geoff This was a major issue in 1996. It's been solved for several years now, but the perception of Java having a speed problem remains. Java stored procedures are the #1 most-desired-by-me feature for PostgreSQL. Oracle and Sybase are examples of databases that have this feature already. (Strangely, Microsoft's database doesn't have it :-) Marshall Spight ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] plpgsql function parameter questions
these 2 questions are actually unrelated... 1. what version of pg do i need to go to in order to eliminate the '16 parameter maximum' restraint? 2. can i pass an array of integers to a function? can i return an array of integers? if so, what would the function definition look like? thanks greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Questions about unicode.
Hi all, I had create a database that encoded as unicode. And then, I put some data into the database by two different machine(win2000), but using different character set used, Traditional & Simplified Chinese. But I cannot read all chinese in right format. like, on traditional windows 2000. I can read traditional chinese only. How can I read both traditional & simplified chinese at one pc? Corn ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Newbie ...Function error (Stored Procedure)?
I have a sequence called seq1.
In psql I am trying to create a function which simply calls this
sequence with the nextval() function.
CREATE FUNCTION testid()
RETURNS INTEGER
AS 'SELECT NEXTVAL('seq1');'
LANGUAGE 'SQL';
I get the following error
ERROR: parser: parser error at or near "seq1"
I can call nextval('seq1') by itself with now error.
What am I doing wrong? I can't for the life of me figure this
seemingly simple
error out ;)
Postgresql 7.0.3 on Mandrake 8.0
Thanks,
Ron
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] PostgreSQL On the Mac? OS9 or OSX?
In looking over the documentation, I haven't seen any evidence of PostgreSQL working on the Mac. Is this the case, or am I missing something. Has anyone heard of it ever being used on a mac? Thanks, ~Israel~ ---(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] Compaq iPAQ and Postgresql
Hi, we would anyway use a microdrive. We have quite large DICOM images so only a few would fit into RAMdisk/Flash on the iPAQ. We could use DICOMDIR then although it's not very performant. Maybe this situtation changes with 32MB of FlashROM and 64MB of RAM as proposed in future iPAQ releases. Tilman begin:vcard n:Schweitzer;Tilman tel;work:06221-43 60 67 x-mozilla-html:FALSE org:Steinbeis Transferzentrum Mediznische Informatik, Heidelberg;Software Development adr:;; version:2.1 email;internet:[EMAIL PROTECTED] title:cand. Dipl.-Inform. Med x-mozilla-cpt:;12096 fn:Tilman Schweitzer end:vcard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Indexes and Timestamp Comparisons
I have a table called session: Table "session" Attribute | Type | Modifier +--+-- sessionid | character(32)| not null usernumber | integer | not null timestamp | timestamp with time zone | not null I have two indices on this table: moo, a btree on timestamp, and session_pkey, the implicit unique btree on sessionid. I need to periodically eliminate timed-out entries from this table. This is where my problem is. I created a test table that has a million entries in it. I try to select the valid session ids and it uses an index scan with moo; I try to select the invalid sessionids and it uses a sequential scan which is considerably slower. I was also playing around with some other timestamps to compare values and found that just by changing the > to a < I would change whether it used a seq scan or an index scan (it wasn't consistent though as to which scan it used for which boolean. Any suggestions on what I can do? I vacuum analyzed the table right before I performed these explains: This is a where for invalid sessionids. This is the one I actually need to be able to run. main=# explain delete from session where timestamp < '2001-08-27 12:26:03-05'::timestamp; NOTICE: QUERY PLAN: Seq Scan on session (cost=0.00..22815.21 rows=999257 width=6) EXPLAIN This is a where for valid sessionids. main=# explain delete from session where timestamp > '2001-08-27 12:26:03-05'::timestamp; NOTICE: QUERY PLAN: Index Scan using moo on session (cost=0.00..3653.36 rows=1000 width=6) EXPLAIN These are a couple of random wheres. If it's of any consequence, this timestamp is before all of the timestamps in the table. main=# explain delete from session where timestamp < '2000-08-27 12:26:03-05'::timestamp; NOTICE: QUERY PLAN: Index Scan using moo on session (cost=0.00..3653.36 rows=1000 width=6) EXPLAIN main=# explain delete from session where timestamp > '2000-08-27 12:26:03-05'::timestamp; NOTICE: QUERY PLAN: Seq Scan on session (cost=0.00..22815.21 rows=999257 width=6) EXPLAIN Any help would be appreciated, Josh Voils ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Regarding Vacuumdb
hi, I am getting the following error while doing vacuumdb, ERROR: mdopen: couldn't open test1: No such file or directory vacuumdb: database vacuum failed on db1. Here 'db1' is the database and 'test1' is a table. When, displaying the structure of the table 'test1', it comes correctly. But I can't drop the table. What could be wrong? Any help. Thanks shan. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Variable case database names
Whenever i create a database with command : psql -h HostName -p PortName -c "create database DATABASENAME " template1 The database is created with small case . How can i create variable case data bases ? regards AMBIKA ---(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] !! Newbie question!!!! connecting to multiple databases
I am attemping to create multiple databases with Postgresql ver. 7.0.3 running on Mandrake 8.0 rpm. The tool that I am using is Pgadmin ver 7.1.0. Can someone tell me how I can connect to different databases in a single select statement? Such as, I have a database named "Customer" and another named "Products". I wish to Select from table A in the Customer database and table A in Products database. I know that its possible in SQL Server and other databases. If anyone can I help I would appreciate it. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Error on postgresql driver (or pg_hba)
I'm getting the following error from my application log file. I'm using Postgres 7.1.2 running on HP-UX 11.0. These errors are results from our Java servlet programs using the postgresql driver: "Something unusual has occured to cause the driver to fail. Please report this exception: java.sql.SQLException: Missing or erroneous pg_hba.conf file, see postmaster log for details" When it happens the connection is broken as can be seen from the following messages in my postmaster logs: "pq_recvbuf: unexpected EOF on client connection" "Missing or erroneous pg_hba.conf file, see postmaster log for details find_hba_entry: Unable to open authentication config file "/opt/postgresql-7.1.2 /data/pg_hba.conf": File table overflow" An extra note, we use JRun 3.0 which I'm quite sure has a built in postgresql driver so this could be the problem but I have not looked into this possibility in depth. Everything else seems to be working fine i.e. psql, dumps etc., and also the db continues functioning as normal after the error occurs. Of course our application would fail at the time it happens but goes on as normal upon subsequent retries but would occur again from time to time. I'm hoping to get some tips on how to find the root of the problem, or point me to somewhere with more info regarding the above error. Thanks in advance...will post this to hackers within 24 hrs :) Ron Looi Consultant ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] speed of communication and pgsql development
There is an IRC channel already on EFnet... Visit #postgresql. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Gowey, Geoffrey Sent: Saturday, August 25, 2001 8:35 PM To: '[EMAIL PROTECTED]' Subject: [GENERAL] speed of communication and pgsql development Not to whine or anything, but I really do think that pgsql development seriously could be helped by usage of an official irc channel. Whether it be hosted on pgsql's official servers or on a 3rd party network such as undernet or openprojects.net I leave as a matter of discussion, but the mailing list really seems to have severe lag (which is painful when developing). Maybe continuously logging the irc channel and making the log searchable online could complement the mailing list? Anyway, just a thought. Geoff ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Apache authentication
I have a problem with apache authentication. I recently upgrade to postgresql v7.1, upgrading debian distribution (to woody, testing distribution) and i discovered that the pakage for apache authentication is no longer there. (may be they suggest to use PAM instead) This is really a problem for me for i use it in many sites! I tried to use old module (for version 6.5 of db) does not work! Does anybody knows where can i found a module for apache authentication with postgresql 7.1? Does it work recompiling old module with new libraries? thanks in advance ... -- Giorgio -
[GENERAL] Deployment of PostgreSQL Applications
Hi everyone, I'm working on a (sorry) closed source application which consists of Java servlets, beans, and client applications talking to a postgresql database. Now, lets say I'm interested in bundling this all together and selling it to customers. 2 questions: 1) Is it legal to bundle Postgresql with another commercial application, assuming the database will have to be significantly reconfigured and tuned? (at the application level, not the source code level) I read over the licenses I could find on the site and they seemed to imply that the answer was yes, but I'd like to reaffirm that. 2) More importantly, is it possible to prevent a customer from peeking into said database once it is deployed on their machine? A large part of what makes my application proprietary is the data model in the database, and it'd be tough to maintain a competative edge when everyone can see exactly how I do things in the database by logging into their postgres account, adding some users and changing permissions on their machine. I really need to make sure the database is bulletproof before I can begin deployment. I' very new to postgresql and unix system administration in general, never having secured a system or played with permissions much in the past. Any advice would be greatly appreciated. Thanks, Mike _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] m4 macro for pgsql
Hi! Where I can find m4 macro for pgsql for auto* ? Code reviews are like sex, just anyone can do it, but skill and training can make you a lot better at it." - LJ Thomas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Create table syntax
Hi Entered below is the SQL create table syntax: CREATE TABLE lists ( [listid] [int] IDENTITY (1, 1) NOT NULL , [listname] [varchar] (200) NULL , [listcreator] [varchar] (200) NULL , [listdesc] [text] NULL) We need to know that this syntax will work properly in postgresql while creating the table and the autoincrement option for the column(listid). If this syntax is not correct for postgresql, we need the correct syntax. Hoping for a positive response from ur side. Regards Satish Rao ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] SELECT FOR UPDATE
I just finished reading Bruce M's book, so this thread confuses me, esp. Jan's posts. I take full heed of the need for application level user/thread management, but I was interested in using a parallel set-up in PG (however redundant that might be). Now that Jan has discounted "SELECT...FOR UPDATE," is the best alternative using a central locking table (perhaps in conjunction with LISTEN & NOTIFY)? Ironically, anyone who suggested using application level transactions would be torn apart at any of the places I've worked at--but that seems to be the gist of this thread. I cannot see a way to avoid deadlocks without an application level transaction component, since the central locking table idea would similarily lock the record forever if the first transaction failed to COMMIT or ROLLBACK. What is the saying: To the beginner, there are many options. To the wise, there are few. ---(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] Wanted: Postgres 6.5.3 dll
Hi, For reasons beyond my control I really need a binary version of Postgres 6.5.3. for WinNT. Could anyone please tell me where I can find it? I'd compile it myself, but I have a lousy modem connection and downloading the sources would take forever. Any help will be appreciated. TIA ;-) -- Jacek Lampart [EMAIL PROTECTED] ICQ: 8101024 The Matrix has you... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] SELECT FOR UPDATE
> It seems to me that SELECT ... FOR UPDATE is not the way to go if > it is possible that the selected record may be held for any length > of time. But transactions are supposed to occur very quickly. > For instance, say you are storing web pages in the database, and you > want a number of developers to be able to get pages from the database > look at them, and possibly make changes and update the record. In this case, I would use webDAV, not a RDBMS, unless I needed relations between the documents that I couldn't get by simply using a directory structure. > So, the lock table would need a time-out field, and might also > include a queue of people waiting for the record. Yes, this is what confuses me most about Jan's post. The lock table functionally resides between the RDMBS and the application/user, and in doing so it defeats the purpose of an RDBMS. What should I use triggers, rules, and listen/notify for then? I guess LISTEN/NOTIFY could be used in conjunction with the lock table. > This is just how I was thinking of it, and again I think it will > depend on the application. At some point, you're not making a > database, you're creating a cvs. Though a hybrid could be good Exactly, your not making a database, and as for the hybrid... This is why XML is such overhyped shit right now, nobody likes/trusts RDBMS's. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Locate on Max() and Group By
I have the next problem: I open an table with select dnkey, Max(dvnaam) as dvnaam2, Max(dvpersoonnr) as dvpersoonnr from tbmedewerker group by dnkey When I want to locate like TblTbPGSMedew.Locate( 'dvnaam2', 'Hallo', [ ]); then 'Hallo' isn't find but when I open the table without Max() en group By like select dnkey, dvnaam as dvnaam2 from tbmedewerker then 'Hallo' is find. How can I fixed this problem? I working with PostgreSQL 7.1 and Delphi 5 ---(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] ERROR: Function 'format_type(oid, int4)' does not exist
I am converting my PostgreSQL database to 7.1.2. I dumped my
database
out of the old server, then dumped it into the new one. Everything
takes
okay, but my Indexes are not working. When I do a "\d" it lists the
Name,
Type, and Owner, but when I try and do a \d on a specific table, I get
this
error:
customer=# \d pods
ERROR: Function 'format_type(oid, int4)' does not exist
Unable to identify a function that satisfies the given argument
types
You may need to add explicit typecasts
Here are the creation lines:
customer=# CREATE TABLE "pods" ("pod_id" int4,"group_id"
int4,"description"
character(30));
CREATE
customer=# CREATE UNIQUE INDEX "pods_pod_id_key" on "pods" using btree
"pod_id" "int4_ops" );
CREATE
and I can view the index I just created:
customer=# \di pods_pod_id_key
List of relations
Name | Type | Owner
-+---+---
pods_pod_id_key | index | root
(1 row)
Any ideas?
Thanks!
Ben Udkow
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Newbie ...Function error (Stored Procedure)?
You have to escape the ' inside of your function definition: CREATE FUNCTION testid() RETURNS INTEGER AS 'SELECT nextval(\'seq1\');' LANGUAGE 'SQL'; The quotationmark in front of seq1 terminated the literal string that should contain your SQL statement and the parser was confused to find an 's' behind the literal string. Arne. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] query help
For one specific label: select TableB.label from TableA, TableB where TableA.idA=TableB.idA and TableA.name='test2'; To get a list of name-label pairs: select TableA.name, TableB.label from TableA, TableB where TableA.idA=TableB.idA order by TableA.name, TableB.label; "G.L. Grobe" <[EMAIL PROTECTED]> on 08/30/2001 07:45:20 PM To: [EMAIL PROTECTED] cc:(bcc: Wesley Sheldahl/Lex/Lexmark) Subject: [GENERAL] query help Hi all, How would I get TableB.label if all I know about is TableA.name? I was thinking about a query on TableA first, then within the while (rs.next()), I'd do another query using the result set from A on B, but I don't like that much, and it seems kind of slow. Any help much appreciated! TableA idA | name | class - 11test1 1 12test2 1 13test3 2 14test4 2 TableB idB | idA | label 1 11 nameA 2 12 nameB 3 13 nameC 4 14 nameD ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(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] PL/java?
I keep hearing all this talk about Java being slow, and how compiled Java is nearly as slow as interpreted languages... If Java was _that_ slow, do you think it would be powering a majority of the Enterprise level sites out there? Java is really more than just hype. Surely it isn't nearly as fast as native optimized C/C++. But there are numerous advantages to the language. If you can afford to learn another language, and get some time to put it to practical use, I suggest that you take a closer look at Java... or IMHO, atleast don't knock it until you have substancial reasoning. -Kevin -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Gowey, Geoffrey Sent: Saturday, August 25, 2001 5:09 PM To: 'Dr. Evil'; [EMAIL PROTECTED] Subject: RE: [GENERAL] PL/java? probably a bad idea. From what I've heard the speed of your java program is wholely dependent on the speed of your vm (and most aren't that quick). Although it would be nice to have just to say we have it and mysql doesn't (then again mysql doesn't have a whole lot of things that pgsql already has). Geoff -Original Message- From: Dr. Evil [mailto:[EMAIL PROTECTED]] Sent: Saturday, August 25, 2001 7:38 PM To: [EMAIL PROTECTED] Subject: [GENERAL] PL/java? What do you think of having java as a procedural language available in PG? It seems like java has many advantages. I'm just wondering if people have thoughts or ideas on this, and if someone is actually working on it, that would be cool. ---(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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Interaction Record (Journal Table?)
I was thinking that for the application I'm
working on, it'd be nice to have a journal
(as an XML fragment) which describes the
interactions within the database...
3493
Clark
Evans
Cameron
I was wondering if anyone has done
something similar? If so, what
approach did you use and is it
published as a reuseable module?
If not... how would you go about
approaching this? I suppose it could
all fit in a "journal" table using
arrays?
JOURNAL has 6 columns:
action, target_table, where_columns, where_values, columns, values
INSERT INTO JOURNAL VALUES ('insert','person'
,NULL,NULL,
['id','given','family'],[3493,'Clark','Evans']);
INSERT INTO JOURNAL VALUES ('update','person',
['id'], [3439],
['middle'],['Cameron']);
INSERT INTO JOURNAL VALUES ('delete','person',
['id'],[3439],
NULL,NULL);
I guess this would be better in the spirit of
PostgreSQL... in this way one can easily make
the dump of the SQL above...
Thoughts?
Clark
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: MySQL's (false?) claims... (was: Re: [GENERAL] PL/java?)
yeah, by the way, doing dump/restore not such a bad thing, as mysql may say. supporting old stuff is sometimes horrible. look at micro$oft, which carries all the sh.t through its OSs... -- Denis A. Doroshenko [GPRS engineer] .-._|_ | [Omnitel Ltd., T.Sevcenkos st. 25, Vilnius, Lithuania] | | _ _ _ .| _ | [Phone: +370 9863486 E-mail: [EMAIL PROTECTED]] |_|| | || |||(/_|_ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: MySQL's (false?) claims... (was: Re: [GENERAL] PL/java?)
how that can come, that table locking is faster than versioning? from how i understand versioning it can't be slower. and saying that "possible access problems" with table can be avoided be specially designing application. God! i must design specially for mysql, being locking whole tables. this page shows that they are low-end even in the mind. they didn't implemet constraints/subselects/views/triggers/full joins, but they've got packed bases and compressed client/server protocol! wow. must be cool. personally, when i tried 7.0.2, with triggers/constraints/views and other cool stuff (transactions!) i said to myself "yep, that's the db you need". and now, whatever they say, when i hear somebody doing billing system (!) on mysql, i just shrug my shoulders. in russian it called "everybody goes insane his own way". btw, what's related to in-memory quick db, what prevents you from creating memory filesystem and put postgres dbs there? in *bsd it's not a problem, on linux i believe too... On Sun, Aug 26, 2001 at 02:29:55PM +1000, Justin Clift wrote: > We can always ask them to change things. The thing which strike me as > > Sean Chittenden wrote: > > > Has anyone seen this page on Mysql.org comparing PostgreSQL to MySQL: > > > > > > http://www.mysql.com/doc/M/y/MySQL-PostgreSQL_features.html > > > > Yeah, I've had a few developers show it to me... the best part -- Denis A. Doroshenko [GPRS engineer] .-._|_ | [Omnitel Ltd., T.Sevcenkos st. 25, Vilnius, Lithuania] | | _ _ _ .| _ | [Phone: +370 9863486 E-mail: [EMAIL PROTECTED]] |_|| | || |||(/_|_ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] PL/java?
> I'm just wondering if people have thoughts or ideas on this, and if > someone is actually working on it, that would be cool. Why would that be cool? Because it's an OO language? If that's the criteria for cool, check out pl/Ruby. It's a pure OO language (java isn't) and is a joy to work with, but YMMV. -sc -- Sean Chittenden PGP signature ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] What's wrong about this sql statement?
Corn wrote:
There is a comma missing. The Statement must be
CREATE TABLE usersright (
userid INTEGER NOT NULL,
rightid INTEGER NOT NULL,
allow BOOLEAN NOT NULL DEFAULT FALSE,
^^^
CONSTRAINT pk_usersright PRIMARY KEY (userid, rightid)
);
Tableconstraints mus be seperated by comma from coulmn definiftion.
Arne.
>
> I am newbie. Try to create a table like this.
>
> CREATE TABLE usersright (
> userid INTEGER NOT NULL,
> rightid INTEGER NOT NULL,
> allow BOOLEAN NOT NULL DEFAULT FALSE
> CONSTRAINT pk_usersright PRIMARY KEY (userid, rightid)
> );
>
> But I got the following error message:
>
> ERROR: parser: parse error at or near "("
>
> I am following the syntax of SQL statement reference from postgresql web
> site.
>
> please help.
>
> thx.
>
> corn
---(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
