[GENERAL] Re: Report Writer for PostgreSQL
Hey, > Does anyone have experience with report writers? It's not something I > know much about yet. Crystal Reports (www.seagate.com) is quite good, can do any sort of report you're after. You can use an ODBC driver for accessing the database, so accessing pgsql wouldn't be a problem.. Oops just read the last comment, it is a bit expensive. Maybe not then :) > Original Message > Subject: Re: [ANNOUNCE] New PostgreSQL Review at epinions.com > Date: Thu, 26 Apr 2001 21:19:08 -0400 (EDT) > From: Patrick Lanphier <[EMAIL PROTECTED]> > To: Justin Clift <[EMAIL PROTECTED]> > > Well it needs the capability format data on many different graph, > capable > of generating HTML, PDF, and RTF formats. The server will be running on > Linux but the design platform can be whatever. The problem I had with > one > report writer was the data from the database was present one way and it > was not capable on rotating the data for the graph and I wasn't about to > do this for the report writer. Is there somebody I should contact that > you know about a report writer? > > Patrick Lanphier > The Artemis Group > http://www.artemisgroup.com > > On Fri, 27 Apr 2001, Justin Clift wrote: > > No problem Patrick. :-) > > > > Two questions : > > > > a) Which operating system(s) does it need to run on? > > > > b) What features does it need to have? > > > > Honestly, I haven't done much with report writing, so I'm not going to > > be the best person to ask. BUT if you do a quick subscribe to the > > [EMAIL PROTECTED] mailing list and ask there, many capable > > people are around. :-) > > > > (You subscribe by sending "subscribe" as a message to > > [EMAIL PROTECTED] Unsubscribing later on is the > > same, but sending "unsubscribe" ) > > > > :-) > > > > Regards and best wishes, > > > > Justin Clift > > > > Patrick Lanphier wrote: > > > Sorry to grab your email address and ask you this. But I have been > > > looking for an inexpensive report writer that is feature rich. What > > > are your thoughts? > > > > > > Patrick Lanphier > > > The Artemis Group > > > http://www.artemisgroup.com -- Chris Smith http://www.squiz.net ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Report Writer for PostgreSQL
Hi guys, Does anyone have experience with report writers? It's not something I know much about yet. Regards and best wishes, Justin Clift Original Message Subject: Re: [ANNOUNCE] New PostgreSQL Review at epinions.com Date: Thu, 26 Apr 2001 21:19:08 -0400 (EDT) From: Patrick Lanphier <[EMAIL PROTECTED]> To: Justin Clift <[EMAIL PROTECTED]> Well it needs the capability format data on many different graph, capable of generating HTML, PDF, and RTF formats. The server will be running on Linux but the design platform can be whatever. The problem I had with one report writer was the data from the database was present one way and it was not capable on rotating the data for the graph and I wasn't about to do this for the report writer. Is there somebody I should contact that you know about a report writer? Patrick Lanphier The Artemis Group http://www.artemisgroup.com On Fri, 27 Apr 2001, Justin Clift wrote: > No problem Patrick. :-) > > Two questions : > > a) Which operating system(s) does it need to run on? > > b) What features does it need to have? > > Honestly, I haven't done much with report writing, so I'm not going to > be the best person to ask. BUT if you do a quick subscribe to the > [EMAIL PROTECTED] mailing list and ask there, many capable > people are around. :-) > > (You subscribe by sending "subscribe" as a message to > [EMAIL PROTECTED] Unsubscribing later on is the > same, but sending "unsubscribe" ) > > :-) > > Regards and best wishes, > > Justin Clift > > Patrick Lanphier wrote: > > > > Sorry to grab your email address and ask you this. But I have been > > looking for an inexpensive report writer that is feature rich. What are > > your thoughts? > > > > Patrick Lanphier > > The Artemis Group > > http://www.artemisgroup.com ---(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] JDBC speed question.
"Clayton Vernon" <[EMAIL PROTECTED]> writes: > Thanks, but can I specify each port? I've only seen the one documented > command option "-p" which I assume was for the TCP/IP port. Well, Unix sockets don't have a "port". The port number you specify is appended to the name of the socket in the filesystem (eg '/tmp/.s.PGSQL.5432') so postmasters running on different ports won't try to create the same socket. What exactly are you trying to do? -Doug -- The rain man gave me two cures; he said jump right in, The first was Texas medicine--the second was just railroad gin, And like a fool I mixed them, and it strangled up my mind, Now people just get uglier, and I got no sense of time... --Dylan ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] JDBC and Accents
Hello, I have some problems to display the accents in my db, using the JDBC and postgres 7.1. Apparently, all accents are replaced by a '?'. Thanks . Loic Courtois Netpartage ---(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] JDBC speed question.
"Clayton Vernon" <[EMAIL PROTECTED]> writes: > Related question: how do you run postgreSQL to simultaneously support a Unix > socket and TCP/IP? Just add '-i' to the postmaster startup options. The Unix socket will still be available. -Doug -- The rain man gave me two cures; he said jump right in, The first was Texas medicine--the second was just railroad gin, And like a fool I mixed them, and it strangled up my mind, Now people just get uglier, and I got no sense of time... --Dylan ---(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] RE: help with serial type
Try this command at the psql command line:
CREATE SEQUENCE atable_id_seq;
ALTER TABLE atable ALTER COLUMN id SET DEFAULT
nextval('"atable_id_seq"'::text);
Jeff
-Original Message-
How can I make the insert
command automatically update the 'id' to the next highest number?
---(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] JDBC speed question.
"John Oakes" <[EMAIL PROTECTED]> writes: > Anyone have any ideas how to speed up performance using the JDBC driver? A > query that takes me 20 seconds to execute from command line takes 1 full > minute with the jdbc driver. Thanks. Since you don't guve any details of your query or network setup, it's hard to help much. What could account for the difference, assuming that the query is exactly the same in both cases, is that the JDBC driver connects through TCP/IP (even when on the same host) whereas 'psql' will by default connect through a Unix socket. On most systems, Unix sockets are considerably faster than even local TCP sockets. If the JDBC client is running on a different machine, the difference becomes even more pronounced. This is assuming that your query is returning a great deal of data, so the network pipe is a factor in the execution time. If it isn't, then it's unclear how to help you without more details on your table layouts and the query itself. -Doug -- The rain man gave me two cures; he said jump right in, The first was Texas medicine--the second was just railroad gin, And like a fool I mixed them, and it strangled up my mind, Now people just get uglier, and I got no sense of time... --Dylan ---(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] Problem connecting to postgres
"solo" <[EMAIL PROTECTED]> writes: > If i use the -h option to specify the hostname it always fails to connect. > [solo@zoot /root]$ psql -h localhost > Connection to database 'solo' failed. > connectDB() -- unknown hostname: localhost > My localhost is configured properly > [root@zoot /root]# ping -c 1 localhost > PING localhost.dnsalias.net (127.0.0.1) from 127.0.0.1 : 56(84) bytes of > data. > 64 bytes from localhost (127.0.0.1): icmp_seq=0 ttl=255 time=0.3 ms Hm. It would seem that your psql is linked with a broken resolver library (or at least a different one than ping is using --- it might be looking at /etc/hosts instead of DNS, or vice versa). Since you haven't told us anything about your platform or how you built/obtained psql, it's impossible to say more than that. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] creating constants in postgres
is there in postgres a way to create a constant like CURRENT_DATE for general use? -tfo ---(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] Re: Struggling to change default data location
"Geoff Caplan" wrote: >Peter, thanks for your patience with a newbie. I think my stuckness stems >from 2 issues: > >1) Starting environment > >> The environment initdb runs in when it runs at system startup is probably >> not the one you set up. Check the /etc/init.d/postgresql file for >> details. > >This is certainly true, as my settings are overridden. I have had a look at >this file, but I am still not clear what environment it is reading when it >sets $PGDATA. Can you clarify where this environment >config file should be (on RedHat)? I can't speak for RedHat, but this is a general description of what happens at boot time, which may help you to work out what is happening: after the kernel starts running it starts a program called init, which is responsible for setting up the multi-user environment and all services, either directly or indeirectly. At one point in the process, the system changes into multi-user mode and runs the scripts for the default run-level. These scripts are usually linked to files in /etc/init.d/. All this is done with the permissions and identity of the Unix superuser, so it is necessary for the postgresql startup script to arrange to become the postgresql superuser to start the postmaster (which root is not allowed to do). The command to change identity is su, and it makes a difference whether the script uses `su postgres' or `su - postgres'; the latter should set up the postgres login environment, but the former will not. >2) Passing parameters to postmaster during starup > >Also, the init.d script uses pg_ctl to start the postmaster. I need to pass >the -i parameter using the "postmaster" utility. (As I understand it, in >past releases of these >RPMs -i was set as a default). At what point in the startup sequence can you >pass "postmaster" utility params to the postmaster process? Another way to do this is to set options in postgresql.conf; have a line in it that says: TCPIP_SOCKET = yes almost all options can be set there. See Administrator's Guide (7.1 version) section 3.4 -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Submit yourselves therefore to God. Resist the devil, and he will flee from you." James 4:7 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] crypt(table.field) ?
On Thu, Apr 26, 2001 at 02:01:46PM -0500, will trillich wrote: > On Thu, Apr 26, 2001 at 05:20:53PM +0200, Peter Eisentraut wrote: > > will trillich writes: > > > > > i know "password" can be used in creating/altering user > > > information (as used via GRANT and REVOKE) but is there any > > > facility within postgres to CRYPT() a value? > > > > See contrib/pgcrypto for hashing functions. > > I've got 7.0.3potato on my debian system, and i've also done ... > Care to explain -- in terms a Debian newbie might grok -- > what "contrib/pgcrypto" means? First contrib/pgcrypto is 7.1-only. It is supposed to be a place for cryptography-related functions. At the moment it contains only hashing and ascii-conversion functions: digest(), encode(), decode(). Now I have released my newer code as separate release (they were not fit for 7.1-in-freeze) and it contains more stuff: crypt(password, salt) - like the crypt(3) in UN*X-like systems for password crypting - DES and MD5-based crypt is supported. gen_salt(type) for above crypt() as generating salts with only SQL is pain. hmac(key, hash_type) is a implementation of RFC2104 "Hashed Message Authentication Code". Sorta passworded-hash. encrypt(data, key, type) with decrypt() - access to raw ciphers with little bit more. They should be used only when you know what you are doing. In the next release they will be renamed to raw_encrypt()/raw_decrypt() and much better encrypt()/decrypt() will be provided based on OpenPGP (RFC2440) - I am still developing this. Also pgcrypto-0.3 should work with both 7.0 and 7.1. -- marko ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Re: Re: Need for newbie friendly docs (was Newbie struggling...)
On Thu, 26 Apr 2001, Clayton Vernon wrote: > Joel- > > In all fairness, there aren't any good HTML-based Unix tutorials. I've > looked for them. In particular, Sun is worthless here, curious since their > Java tutorial is (IMO) really well done. > > I'm hoping PostgreSQL is MORE stable than Oracle in our Solaris environment. > The massive CPU/disk footprint of Oracle generates reliability errors in our > databases which I hope can be avoided in a leaner package. I remember struggling for days to install Oracle 8i on a 192MB laptop (don't ask why... fscking client requirement). Yeeks. > This marvelous mailing list really gives me confidence. Yep. Overall, I think PG offers *great* online support. Some possible places to start looking: 1) Does anyone have a copy of the O'Reilly book "Unix for Oracle DBAs Pocket Reference" (http://www.oreilly.com/catalog/unixoracledbapr/) This might be the kind of information that would be helpful. 2) Eric Raymond has "Unix and Internet Fundamentals" HOWTO at http://linuxdocs.org/HOWTOs/Unix-and-Internet-Fundamentals-HOWTO/index.html 3) >From DOS/Windows to Linux HOWTO http://linuxdocs.org/HOWTOs/DOS-Win-to-Linux-HOWTO.html 4) The Linux Reading List HOWTO http://linuxdocs.org/HOWTOs/Reading-List-HOWTO/index.html -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Re: [HACKERS] Re: unanswered: Schema Issue
yes, but my tables have long names, i've already done as suggested by you. why not have a schema table created for us to extract these info. >From: Joel Burton <[EMAIL PROTECTED]> >To: "V. M." <[EMAIL PROTECTED]> >CC: [EMAIL PROTECTED], [EMAIL PROTECTED] >Subject: Re: [HACKERS] Re: unanswered: Schema Issue >Date: Thu, 26 Apr 2001 15:32:47 -0400 (EDT) > >On Thu, 26 Apr 2001, V. M. wrote: > >(moving this conversation back to pgsql-general, followups to there) > > > perhaps adding t.tgargs to your view enable me to extract parameters > > that are the related fields > >At SCW, we use a naming convention for RI triggers, to allow >us to easily extract that, and deal with error messages. > >We use: > >CREATE TABLE p (id INT); > >CREATE TABLE c (id INT CONSTRAINT c__ref_id REFERENCES p); > >This allows us at a glance to see in error messages what field of what >table we were referencing. In an Access front end, we can trap this >error message to a nice statement like "You're trying to change a value in >the table "c", using information in table "p", "id", but...") > >If you don't have this, yes, you can look at in >the tgargs, but, given that its a bytea field, it's hard to >programmatically dig anything out of it. > >HTH, >-- >Joel Burton <[EMAIL PROTECTED]> >Director of Information Systems, Support Center of Washington > _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Why Size Of Data Backed Up Varies Significantly In SQL 6.5?
On Thu, 26 Apr 2001, Wendy wrote: > I backed up a database at night and noted the size to be about over 300MB. Backuped with pg_dump, or dumped the raw database files ? In the latter case you want to stop the PostgreSQL server first. > The following morning, I again backed up the same database and found out the > size to be less than 100MB. There was no massive deletes by users during > that morning. If you dumped the raw database, this difference can be caused by VACUUM being run automatically at night to free deleted items. NB: personnally I recommend backuping using pg_dump or pg_dumpall. And testing that restoration works. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Re: help with serial type
Ahhh, but I didn't use a double quote identifier. This statement worked fine for me: CREATE TABLE atable ( title VARCHAR(20), name VARCHAR(20), id SERIAL PRIMARY KEY, date DATE); Greg - Original Message - From: "Joel Burton" <[EMAIL PROTECTED]> To: "Gregory Wood" <[EMAIL PROTECTED]> Cc: "Poul L. Christiansen" <[EMAIL PROTECTED]>; "PostgreSQL-General" <[EMAIL PROTECTED]> Sent: Thursday, April 26, 2001 2:45 PM Subject: Re: help with serial type > On Thu, 26 Apr 2001, Gregory Wood wrote: > > > > I don't know if you can name a column "date" because I think it's a > > > reserved word. > > > > Oddly enough, it *does* work (at least on my version of 7.1), although I > > would recommend against doing it if for no other reason than it's confusing. > > If you wrap them in double-quotes, you can use most reserved words as > system identifiers. But I wouldn't -- some cheesy client implementation > might choke on them, and better to not find that out later. > > -- > Joel Burton <[EMAIL PROTECTED]> > Director of Information Systems, Support Center of Washington > > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Re: crypt(table.field) ?
will trillich <[EMAIL PROTECTED]> wrote: >On Thu, Apr 26, 2001 at 05:20:53PM +0200, Peter Eisentraut wrote: >> See contrib/pgcrypto for hashing functions. >Care to explain -- in terms a Debian newbie might grok -- what >"contrib/pgcrypto" means? Peter is referring to a directory in the PostgreSQL sources, not to a part of a binary package. "apt-get source postgresql" and look around. HTH, Ray -- Don't think of yourself as an organic pain collector racing toward oblivion. Dogbert ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Re: Re: Need for newbie friendly docs (was Newbie struggling...)
On Fri, 27 Apr 2001, Justin Clift wrote: > Newbies have interesting ideas sometimes too. After all, they've spent > their time learning about something OTHER than Unix. :-) something... other... than... unix ? Justin, I'm not clear on what you mean. Can you give us an example? ;-) But really: sure! Oracle, ferinstance, realizes that many people run Unix *because* they want to run Oracle in a stable server environment. People may be making the same decision about PostgreSQL. We shouldn't have to write this, though... if people could contribute the great 'basics of Unix you need to know to be a decent DBA' stuff that's already on the web, we'd have plenty. -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Re: Postgres Bug (ALTER TABLE problem)
On Thu, 26 Apr 2001, Boulat Khakimov wrote: > Hi, > > I've discovered a bug in Postgres. When you rename > a table, the corresponding triggers for that table > are not updated. Yep. Use ALTER TABLE ADD CONSTRAINT to add 'em back in. More info can be found in the Ref Int tutorial I just submitted at techdocs.postgresql.org. HTH, -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(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] crypt(table.field) ?
On Thu, Apr 26, 2001 at 05:20:53PM +0200, Peter Eisentraut wrote: > will trillich writes: > > > i know "password" can be used in creating/altering user > > information (as used via GRANT and REVOKE) but is there any > > facility within postgres to CRYPT() a value? > > See contrib/pgcrypto for hashing functions. I've got 7.0.3potato on my debian system, and i've also done apt-get install postgresql-contrib which looks like it's got lots of meat to it, but dpkg -L postgresql-contrib | grep crypt shows nada. Care to explain -- in terms a Debian newbie might grok -- what "contrib/pgcrypto" means? -- don't visit this page. it's bad for you. take my expert word for it. http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html [EMAIL PROTECTED] http://sourceforge.net/projects/newbiedoc -- we need your brain! http://www.dontUthink.com/ -- your brain needs us! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Problem connecting to postgres
Hi, I'm having a problem when i try to connect to the database. If i use the -h option to specify the hostname it always fails to connect. [solo@zoot /root]$ psql -h localhost Connection to database 'solo' failed. connectDB() -- unknown hostname: localhost My localhost is configured properly [root@zoot /root]# ping -c 1 localhost PING localhost.dnsalias.net (127.0.0.1) from 127.0.0.1 : 56(84) bytes of data. 64 bytes from localhost (127.0.0.1): icmp_seq=0 ttl=255 time=0.3 ms --- localhost.dnsalias.net ping statistics --- 1 packets transmitted, 1 packets received, 0% packet loss round-trip min/avg/max = 0.3/0.3/0.3 ms Any ideas? Kind Regards ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Re: help with serial type
On Thu, 26 Apr 2001, Gregory Wood wrote: > > I don't know if you can name a column "date" because I think it's a > > reserved word. > > Oddly enough, it *does* work (at least on my version of 7.1), although I > would recommend against doing it if for no other reason than it's confusing. If you wrap them in double-quotes, you can use most reserved words as system identifiers. But I wouldn't -- some cheesy client implementation might choke on them, and better to not find that out later. -- Joel Burton <[EMAIL PROTECTED]> Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Re: help with serial type
> I don't know if you can name a column "date" because I think it's a > reserved word. Oddly enough, it *does* work (at least on my version of 7.1), although I would recommend against doing it if for no other reason than it's confusing. Greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Re: help with serial type
> > I'm surprised that works at all... the name of the table is "atable",
isn't
> > it? Try this:
> >
> > INSERT INTO atable (title,name,date) VALUES ('SQL3','Toy',date('now'));
> >
> > Greg
>
> Thanks for the help. I wrote the command out wrong in the post, I did try
the
> one that you wrote out, and it didn't update. I used:
>
> INSERT INTO atable (title,name,date) VALUES ('SQL3','Toy',date('now'));
> Still not sure how to fix it.
I just did this on 7.1 (PostgreSQL 7.1 on i386-unknown-freebsd4.2, compiled
by GCC 2.95.2) and it worked fine:
-
CREATE TABLE atable (
title VARCHAR(20),
name VARCHAR(20),
id SERIAL PRIMARY KEY
);
ALTER TABLE ADD COLUMN date DATE;
INSERT INTO atable (title,name,date) VALUES ('SQL3','Toy',date('now'));
INSERT INTO atable (title,name,date) VALUES ('SQL3','Toy2',date('now'));
-
After that I tried SELECT currval('atable_id_seq'); and got the correct
value (2).
> Is it possible to rearrange columns? I looked
> in the tutorial, but didn't find anything useful.
In relational database design, the order of the columns should not matter,
only the data layout of the database (which columns are in which table). To
the best of my knowledge, the only way to reorder the columns physically
would be to recreate the table... you can do a SELECT id,title,name,date
INTO btable FROM atable, and then rename that first table I suppose. Or
rename the table first and then do the SELECT INTO.
---(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] Postgres Bug (ALTER TABLE problem)
Hi,
I've discovered a bug in Postgres. When you rename
a table, the corresponding triggers for that table
are not updated.
For example:
CREATE TABLE tblParent (
ID SERIAL NOT NULL,
Name text,
PRIMARY KEY (ID)
);
CREATE TABLE tblChild (
ID int4 NOT NULL,
email text,
FOREIGN KEY (ID) REFERENCES tblParent ON DELETE NO ACTION ON UPDATE
CASCADE
);
-
-- Create temporary table to transfer data from old table structure
-- into new one.
-- ALTER TABLE tblChild ADD COLUM is not used because it doesnt allow
things
-- like check (fieldname in...) when new columns are added
--
CREATE TABLE tblChildTemp (
ID int4 NOT NULL,
email text,
Billed char check (Billed in ('Y','N')) DEFAULT 'N' NOT NULL,
FOREIGN KEY (ID) REFERENCES tblParent ON DELETE NO ACTION ON UPDATE
CASCADE
);
INSERT INTO tblChildTemp(ID,email)
SELECT ID,email FROM tblChild;
DROP table tblChild;
ALTER TABLE tblChildTemp RENAME TO tblChild;
---
-- Here is where the problem starts
UPDATE tblParent SET name='Mary';
ERROR: RI constraint cannot find table tblchildtemp
If I do "SELECT * FROM pg_trigger";
tgrelid | tgname| tgfoid | tgtype | tgenabled |
tgisconstraint | tgconstrname | tgconstrrelid | tgdeferrable |
tginitdeferred | tgnargs | tgattr |
tgargs
-+-+++---++--+---+--++-++---
1260 | pg_sync_pg_pwd | 12 | 29 | t |
f | | 0 | f
| f | 0 ||
349149 | RI_ConstraintTrigger_349162 | 1644 | 21 | t |
t | |349105 | f
| f | 6 ||
\000tblchildtemp\000tblparent\000UNSPECIFIED\000id\000id\000
349105 | RI_ConstraintTrigger_349164 | 1654 | 9 | t |
t | |349149 | f
| f | 6 ||
\000tblchildtemp\000tblparent\000UNSPECIFIED\000id\000id\000
349105 | RI_ConstraintTrigger_349166 | 1647 | 17 | t |
t | |349149 | f
| f | 6 ||
\000tblchildtemp\000tblparent\000UNSPECIFIED\000id\000id\000
(4 rows)
I can see that the triggers were not updated, they are still using
tblchildtemp,
altho it got renamed.
Does anyone know a way to fix that problem?
Any Feedback would be appreciated...
Regards,
Boulat Khakimov
--
What goes around, comes around
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
[GENERAL] RE: NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ?
Got it... It seams those triggers were created to handle the types I used for the columns: int2 and bytea... I realy should check out the types section in the postgres docu :)) > -Original Message- > From: Gregory Wood [mailto:[EMAIL PROTECTED]] > Sent: Thursday, April 26, 2001 7:59 PM > To: Christian Marschalek > Cc: PostgreSQL-General > Subject: Re: NOTICE: CREATE TABLE will create implicit > trigger(s) for FOREIGN KEY check(s) ? > > > > Can anyone tell me what this notice means? > > > > NOTICE: CREATE TABLE will create implicit trigger(s) for > FOREIGN KEY > > check(s) ? > > It means that PostgreSQL will automatically create triggers > to perform your foreign key checks :) > > Seriously though, PostgreSQL has to have some mechanism to > check that your foreign key values exist (or not), and it > does that by using a feature already built into PostgreSQL: > triggers. Nothing you really need to worry about unless you > are doing something fancy (for instance, disabling all > triggers on either table to accomplish some action... in such > a case your foreign key relationship might be broken). > > Greg > > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
RE: [GENERAL] Data Export from PostGreSQL
I got good results in a test case, just using a standard PostgreSQL dump file created with INSERT statements (pg_dump -d). All it needed was a little editing to change some of the data types in the table definition, e.g. from text and varchar to varchar2 (if your Oracle table is set up, you won't even have to do that: just do a pg_dump -a -d). The only other thing I needed to do was edit dates into the form that Oracle likes: DD-MMM-YY. I believe that this default can be changed though, I just couldn't find a reference quickly enough to suit. This approach was necessary because I was testing a development version of Oracle (the freely downloadable one), which doesn't include the import/export tools, so I had to use the sqlplus interface. If you have the import tools, you can just import from a delimited text file, as can be created using COPY: read the documentation for your Oracle installation. > -Original Message- > From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] > Sent: Thursday, April 26, 2001 10:13 AM > To: [EMAIL PROTECTED] > Subject: [GENERAL] Data Export from PostGreSQL > > > For the purposes of a demo I have to export data from a PostGresSQL system > to Oracle > (I know, I know not my choice) > > Is there an easy way of dumping all the data in a format Oracle can > understand? > > Any help appreciated, > > Thanks, > Martin C. > > > -- > > NOTICE: The information contained in this electronic mail transmission is > intended by Convergys Corporation for the use of the named individual or > entity to which it is directed and may contain information that is > privileged or otherwise confidential. If you have received this > electronic > mail transmission in error, please delete it from your system without > copying or forwarding it, and notify the sender of the error by reply > email > or by telephone (collect), so that the sender's address records can be > corrected. > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] JDBC problem & 7.1
Hello,
I have the following java source code:
This one work with pg 7.0.3 , but not with 7.1, with the following error
message:
java.lang.NullPointerException
at org.postgresql.jdbc2.ResultSet.next(ResultSet.java:116)
at ouah.stats.Stats.go(Stats.java:242)
at daily.main(daily.java:54)
///BEGIN
///:
Statement statement;
ResultSet results, results2;
try {
statement = conn.createStatement();
if(statement.execute("SELECT ...")) {
results = statement.getResultSet();
while(results.next()) {
System.out.println(results.getInt("nb"));
if(statement.execute("SELECT ... WHERE no = " +
results.getString("no") ... ")) {
results2 = statement.getResultSet();
while(results2.next()) {
System.out.println("\t" +
results2.getInt("nb"));
}
results2.close();
}
}
results.close();
statement.close();
}
} catch (java.sql.SQLException e) { e.printStackTrace();}
///END//
/:
This one work with pg 7.1
///BEGIN
///:
Statement statement, s2;
ResultSet results, results2;
try {
statement = conn.createStatement();
s2 = conn.createStatement();
if(statement.execute("SELECT ...")) {
results = statement.getResultSet();
while(results.next()) {
System.out.println(results.getInt("nb"));
if(s2.execute("SELECT ... WHERE no = " +
results.getString("no") ... ")) {
results2 = s2.getResultSet();
while(results2.next()) {
System.out.println("\t" +
results2.getInt("nb"));
}
results2.close();
}
}
results.close();
statement.close();
s2.close();
}
} catch (java.sql.SQLException e) { e.printStackTrace();}
///END//
/:
Please, could I have advice?
Thanks!!
Loic Courtois
Netpartage
---(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] Re: Struggling to change default data location
Geoff Caplan writes: > > The environment initdb runs in when it runs at system startup is probably > > not the one you set up. Check the /etc/init.d/postgresql file for > > details. > > This is certainly true, as my settings are overridden. I have had a look at > this file, but I am still not clear what environment it is reading when it > sets $PGDATA. Can you clarify where this environment > config file should be (on RedHat)? There is no config file; the location is wired in fairly deeply. You're probably better off mounting your data volume at the appropriate place. > Also, the init.d script uses pg_ctl to start the postmaster. I need to pass > the -i parameter using the "postmaster" utility. (As I understand it, in > past releases of these > RPMs -i was set as a default). At what point in the startup sequence can you > pass "postmaster" utility params to the postmaster process? You put options in the postgresql.conf file (under /var/lib/pgsql/data or whereever PGDATA ends up...). -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ?
Can anyone tell me what this notice means? NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ? greetings ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Trigger won't accept function (Please Help)
I've created a plpgsql function that takes 2 args as follows:
CREATE FUNCTION insert_default(text,text) RETURNS OPAQUE AS 'BEGIN new.$2 =
new.$1; RETURN new; END;' LANGUAGE 'plpgsql';
When I try to reference this function when creating a trigger:
CREATE TRIGGER "iep_district_insert" BEFORE INSERT ON "iep_district" FOR
EACH ROW EXECUTE PROCEDURE insert_default('id_author','id_author_last_mod');
I get this error: PostgreSQL said: ERROR: CreateTrigger: function
insert_default() does not exist
Questions:
1. I can ONLY reference functions in triggers that have NO args, any
function (like above example) that takes 1 or more args always gets the does
not exist error.
2. I'm new to postgres, perhaps there's a better way have the default value
of a field be that of another field??
Thanks for any help,
Micah
--
Micah Woods
Woods/IT LLC
2214 Waite Ave.
Kalamazoo, MI 49008
v: 616 349-1175
f: 877 349-4929 (toll free)
e: [EMAIL PROTECTED]
w: http://www.woods-it.com
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [GENERAL] NOTICE: CREATE TABLE will create implicit trigger(s)for FOREIGN KEY check(s) ?
On Thu, 26 Apr 2001, Christian Marschalek wrote: > Can anyone tell me what this notice means? > > NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY > check(s) ? The foreign key constraint checks are handled by triggers on the pk and fk table. The warning just lets you know that it's creating those triggers behind your back so to speak. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Re: NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ?
> Can anyone tell me what this notice means? > > NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY > check(s) ? It means that PostgreSQL will automatically create triggers to perform your foreign key checks :) Seriously though, PostgreSQL has to have some mechanism to check that your foreign key values exist (or not), and it does that by using a feature already built into PostgreSQL: triggers. Nothing you really need to worry about unless you are doing something fancy (for instance, disabling all triggers on either table to accomplish some action... in such a case your foreign key relationship might be broken). Greg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] using POSTGRES in a network
hiroko wrote: > we're now developping a system on Linux, using PostgreSQL.and the > question this time is abouthow to connect to the DBserver through the > network (ex,ethernet) from clients.especially on CODINGS. we found a > lot of info about the way of connecting between DBserver on Linux and > Windouws user.but few about the Linux-Linux DBconnection, which we do > need.is anybody knows about this? or have anyone tried it before? Oh, yeah! The first thing you'll want to do is to edit the pg_hba.conf file on the server to permit connections from the Linux clients in question. Since you're already allowing Windows clients to connect via network, you're probably aware of that. Connecting to the database server via the command line is relatively easy. The following example is based on version 6.5.3, but the commands should be similar for more recent versions: psql -h -u "server" is the name of the server you wish to connect to. It can be either an IP address (like 192.168.1.10), an entry in the client's /etc/hosts file, or available via DNS (we use DNS). "database" is the name of the specific database you want to connect to, like "template1" or what have you. The above command, once submitted, will request a user ID and password to complete the connection. Once the connection is established it's just like running an interactive session on your localhost. > when you connect to the remote DBserver from Windows,what you need is > to set the IP of the server in "hosts"fileand to set your IP as DBuser > to the server machine.and you can access through the command-line, > using "psql"but if you wannna do this on the programs??can you do > the same thing using the function such as "db_connect" or > PGDBfunctions???we also need the info about this. we do appreciate for > your reply . thanks. hiroko You can connect to the server from within several programming "environments" (PHP, Perl, C, and C++ come to mind). The relevant C and C++ libraries are provided with the PostgreSQL distributions. You can learn more about those by studying the Programmer's Guide. The Perl modules are available from CPAN. Check out http://www.perl.com and follow the links. For more information about PHP connectivity, check out http://www.php.net. Hope this helps. -- John Burski I.T. Manager 911 Emergency Products 25 Sixth Avenue North St. Cloud, MN 56303 (320) 656 0076 www.911ep.com ++ + How's your cheese holding out? + ++ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Re: Need for newbie friendly docs (was Newbie struggling...)
Oliver Elphick > There is an enormous amount of background knowledge assumed when > you document an application, and this is necessary, or else every document > would become a Windows-like spoon-feeder, which would spend so much > time on basic stuff that it would never cover the real meat. > > I'm not sure that it is either possible or desirable for PostgreSQL to > attempt to satisfy a newbie's need for basic training in Unix. > Well, I have spent a lot of time writing instructional material, and I think it is a question of the right balance. You obviously have to assume the basics, like file management. An issue like the one I got stuck on, on the other hand, is not so basic (not covered in the thousands of pages of Unix documentation I consulted) and could have been explained with a single line example. In many ways it is a matter of mindset - when experienced people are writing it is difficult for them to visualise the roadblocks that will catch out those with less knowledge. More beginner friendly docs don't need to be much more verbose - it's about testing them and pinpointing the points of difficulty. The docs have room for many pages on how to do a SELECT, which is covered in detail in every SQL primer, so there is surely no reason why setup should not be covered a bit more clearly. Postgres is not so hard to use, but it is a bit of a pig to administer, and the docs are part of the problem. But I do understand that top quality docs require specific skills and resources which it is perhaps unreasonable to expect from an open source project. It will probably take a commercial effort from GreatBridge or a book to improve things. GreatBridge have made a start. I hope they continue to test and develop their docs, and don't regard the job as done... Geoff Caplan ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Classes of returned rows
> Use the "tableoid" pseudo-column. Ah! Awesome. A little playing with pg_class, and we have table/class names too. PostgreSQL has cool new things to find every day... Thanks Tom! - Jeff -- Web development with PHP is like injecting pure rust with a high-pressure hose. For pain relief. ---(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] crypt(table.field) ?
On Thu, Apr 26, 2001 at 09:15:45AM -0500, will trillich wrote: > i know "password" can be used in creating/altering user > information (as used via GRANT and REVOKE) but is there any > facility within postgres to CRYPT() a value? At the moment no. You should patch your PostgreSQL source for that. There is a patch in techdocs site which imports system crypt to SQL level and there is my pgcrypto package which does this and more... http://www.l-t.ee/marko/pgsql/pgcrypto-0.3.tar.gz -- marko ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] help with serial type
Jason writes:
> Hi , I'm a postgreSQL newbie. I have a table called "atable" that has
> the columns:
> title varchar(20)
> name varchar(20)
> id serial
> if I do:
> INSERT INTO TABLE atable VALUES('SQL1','Jason')
> the 'id' gets updated with a new number automatically. I then later
> added a new column called 'date'. Now if I do an insert with:
> INSERT INTO TABLE atable VALUES('SQL2','Toy','',date('now'))
> the id will update the first time to '0',
This is not really valid. What you are telling PostgreSQL is to insert a
value of '' (empty string) into the id column. This gets converted to 0
(zero) by the implicit type converter. The serial type only generates a
sequence number if you do not override it explicitly with a different
value. So what you want is something like this:
INSERT INTO TABLE atable (title, name, date_field)
VALUES ('SQL2', 'Toy', current_date);
--
Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter
---(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 ?'s
"Clayton Vernon" <[EMAIL PROTECTED]> writes: > 1) Do you have to specify in advance the full number of processes, or does = > it dynamically manage them ala Apache? You have to set an upper limit on the max number of server processes. This is mainly to prevent Postgres from taking over your system ;-). Hopefully you can set it high enough to not be a problem in practice. > 2) If your site was busy, will requests queue up civilly or will they typic= > ally bomb right away if they can't find an idle process. Connections will be refused if the server process limit is reached. > 5) This seemingly full-fledged password overhead is worrisome. Is this tedi= > ous to manage in practice? Can the same (quasi-generic) user be reading the= > database from many simultaneous processes? There are several different options for authentication methods --- probably you can find one that matches your combination of security and simplicity concerns. Yes, the same userid can be used for multiple connections at once. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] I am now Linux and PostgreSQL user, have a question
I am new user of linux. and I am interested in Postgresql recently I download ver7.1 and setup I do it step by step according some docunment in www.Linuxfab.cx the docunment I read is in below link http://linuxfab.cx/indexBookData.php?BID=5&G1=5&G2=2&G3=0&G4=0&PAGEID=1 The problem I got is as below: P.S. the super user "postgres" is added by myself [root@vtl /root]# su - postgres [postgres@vtl postgres]$ /usr/local/pgsql/bin/initdb -D /usr/loca/pgsql/data This database system will be initialized with username "postgres". This user will own all the data files and must also own the server process. Creating directory /usr/loca/pgsql/data mkdir: cannot create directory `/usr/loca/pgsql/data': No such file or directory initdb failed. Removing temp file /tmp/initdb.11893. [postgres@vtl postgres]$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data This database system will be initialized with username "postgres". This user will own all the data files and must also own the server process. Fixing permissions on existing directory /usr/local/pgsql/data Creating directory /usr/local/pgsql/data/base Creating directory /usr/local/pgsql/data/global Creating directory /usr/local/pgsql/data/pg_xlog Creating template1 database in /usr/local/pgsql/data/base/1 DEBUG: database system was shut down at 2001-04-25 10:37:03 HKT DEBUG: CheckPoint record at (0, 8) DEBUG: Redo record at (0, 8); Undo record at (0, 8); Shutdown TRUE DEBUG: NextTransactionId: 514; NextOid: 16384 DEBUG: database system is in production state Creating global relations in /usr/local/pgsql/data/global DEBUG: database system was shut down at 2001-04-25 10:37:10 HKT DEBUG: CheckPoint record at (0, 108) DEBUG: Redo record at (0, 108); Undo record at (0, 0); Shutdown TRUE DEBUG: NextTransactionId: 514; NextOid: 17199 DEBUG: database system is in production state Initializing pg_shadow. Enabling unlimited row width for system tables. Creating system views. Loading pg_description. Setting lastsysoid. Vacuuming database. Copying template1 to template0. Success. You can now start the database server using: /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data [postgres@vtl postgres]$ /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data \DEBUG: database system was shut down at 2001-04-25 10:48:00 HKT DEBUG: CheckPoint record at (0, 1522124) DEBUG: Redo record at (0, 1522124); Undo record at (0, 0); Shutdown TRUE DEBUG: NextTransactionId: 615; NextOid: 18720 DEBUG: database system is in production state the process stoped here, not more action. Can anybody tell me what's wrong? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Seral field value after INSERT !
On Wed, Apr 25, 2001 at 09:32:07AM +0200, Berényi Gábor wrote:
> I have been inserting records into a table using the SQL insert statement.
> One of the field types is serial, and I have been
> trying to figure out how to get the value that was assigned in the field as
> a result of the insert. The serial typed field is the only one guaranteed
> to be unique, so I can't really do a search, and there are several people
> adding data at once, so I can't reliable guess. Can anyone help?
Perfect question (partly because i know the answer). "currval()"!
create table mytab (
id serial,
t text,
v varchar(93),
f float8,
...etc...
);
\d mytab
Table "mytab"
Attribute | Type | Modifier
---+-+---
id| integer | not null default nextval('mytab_id_seq'::text)
...etc...
The 'sequence' is named
underscore underscore "seq"
so in this case (as you can see from \d above) it's
mytab_id_seq
here's how to use it--
insert into mytab (t,v,f) values (
'some text just for fun',
'variable character string here',
22.0/7.0
);
--we don't specify a value for "id" since the
--'default' value will take care of it for us
select currval('mytab_id_seq');
Now here's the not-quite-what-you-at-first-expect part:
Until you run "nextval" (i.e. 'bump' your sequence counter by
inserting a new row of data) you will not be able to see the
"currval" at all. In fact, you'll get an error.
This seemed odd at first -- but there's no value in knowing what
another user's "currval" might be at the moment, right? You're
only concerned about your own, and you don't really have one to
use until you insert a new row. THEN you can use that value to
create rows in other tables that link to your new 'tuple'.
> Thanks for all help !
>
> ps : Sorry my bad english.
Your english is better than that of most american college
graduates. (Not that we're proud, but you should be.)
--
[EMAIL PROTECTED]
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[GENERAL] crypt(table.field) ?
i know "password" can be used in creating/altering user information (as used via GRANT and REVOKE) but is there any facility within postgres to CRYPT() a value? create rule new_folk as on insert to view_folk do instead insert into folk_table (created,login,password) values (current_timestamp,new.login,CRYPT(new.password)) ; or must this be done (say, in perl) before postgres sees it? -- [EMAIL PROTECTED] http://sourceforge.net/projects/newbiedoc -- we need your brain! http://www.dontUthink.com/ -- your brain needs us! ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Struggling to change default data location
Hi folks Having a nightmare changing the default data location in 7.1 I uninstalled and re-installed 7.1 on RedHat 6.2 from the rpms with a clean system. With the help of this list, I have set $PGDATA in my shell config file and exported the value. Postgres utilities such as initdb now recognise $PGDATA and I have setup a system at my desired location. initdb does not write a postmaster.opt file at the new location. On system startup, it does write the .opt file to /var/lib/pgsql/data. Editing this file with the new datapath does not change the behaviour below. At this stage - here is what I get... $ /usr/bin/pg_ctl -D /www/dbdata -l logfile start postmaster successfully started $ pg_ctl status pg_ctl: postmaster or postgres is not running $ ps ax | grep postmaster 582 ?S 0:00 /usr/bin/postmaster -D /var/lib/pgsql/data Please note: - postmaster is running, but pg_ctl can't find it - postmaster has started with the default datapath and is ignoring $PGDATA I get the same result if I launch automatically during startup. Can anyone please help me make some sense of this? I am losing the will to live... Geoff Caplan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Re: CREATE TABLE AS... syntax?
"Dr. Evil" wrote: > > I'm trying to use CREATE TABLE AS under 7.03. There are no > examples in the guide, so I tried a few things: > > CREATE TABLE foo (test INT4) AS SELECT number FROM account; > > and > > CREATE TABLE foo (test INT4) AS number FROM account; > > and both of them give ERROR: parser: parse error at or near "as". > Any tips on how to use this? I think you want: CREATE TABLE foo AS SELECT number AS test FROM account; Calvin -- Calvin Dodge Certified Linux Bigot (tm) http://www.caldodge.fpcc.net ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: IBM to buy Informix
Probably to rip out some really good guts that it does have and put them into DB2. On Tue, 24 Apr 2001 23:32:33 + (UTC), [EMAIL PROTECTED] (Joseph Shraibman) wrote: >Martín Marqués wrote: >> >> On Mar 24 Abr 2001 18:04, Bruce Momjian wrote: >> > IBM to buy Informix: >> > >> >http://news.cnet.com/news/0-1003-200-5705678.html?tag=st.it.9500.lthd >> >> The rumour was there about a month ago. The problem was that the people of >> Informix didn't know how to sell there product, so Oracle and Micro$oft were >> all over them. >> Looks like the romour was right! :-) > >They couldn't sell it because it was a crappy product. I have no idea >why IBM would be willing to plunk down a bil for Informix. > >-- >Joseph Shraibman >[EMAIL PROTECTED] >Increase signal to noise ratio. http://www.targabot.com > >---(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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Re: newbie ?'s
3) I've been told PostgreSQL databases must be periodically "rebuilt" so to
speak, ("vacuumed"). Is this easy to do on-the-fly? Rapid? Or, does the db
need to go down awhile?
Not so much rebuilt, but just cleaned up. My understanding is that deleted
records (and outdated copies of UPDATEd records) are removed, and statistics
are compiled that helps the planner determine the best way to fill your
queries. It's pretty easy to do on the fly... VACUUM; or better yet VACUUM
ANALYZE; from the SQL prompt, or 'vacuumdb' from the command line.
This can be done on a running database, but I'd recommend setting up a cron
job to do it when the database is not very busy (i.e. 4AM or some other
off-hour). The operational time depends on the size of your database; our
relatively small databases (no more than 60K records in a given table) only
take a few minutes. Not quite rapid, but not terribly painful either.
4) I can't find anything yet in the docs on on-the-fly backups of the db.
Can you simply copy the directory, or will this not have integrity? What is
the best strategy to study for backing up of databases that may be in use
7x24?
You'll want to look for the pg_dump utility. There's plenty of documentation
on this (including a nice man page), so I won't go into detail. But you can
run this on a cron job as well. pg_dump should back up the entire database,
including the schema, although it has options to dump data or schema only.
It even does it in a nice text format, so you can even go in with your
favorite text editor and modify the schema or do whatever you want. When you
need to restore, just pipe the pg_dump'ed file into psql and your have your
database. Like VACUUM, you can run pg_dump at any time from the command
line.
I'll let those wiser and more knowledgable answer the other questions...
Greg
---(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] help with serial type
Hi , I'm a postgreSQL newbie. I have a table called "atable" that has
the columns:
title varchar(20)
name varchar(20)
id serial
if I do:
INSERT INTO TABLE atable VALUES('SQL1','Jason')
the 'id' gets updated with a new number automatically. I then later
added a new column called 'date'. Now if I do an insert with:
INSERT INTO TABLE atable VALUES('SQL2','Toy','',date('now'))
the id will update the first time to '0', but using this command again:
INSERT INTO TABLE atable VALUES('SQL3','Toy','',date('now'))
it won't let me update because there are duplicate 'id's.
I also tried
INSERT INTO TABLE a(title, name, date) VALUES('SQL3','Toy',date('now'))
but it won't automatically update 'id' also. How can I make the insert
command automatically update the 'id' to the next highest number? or
how can I rearrange the columns so that 'id' is the last column and
'date' comes before 'id' so that way 'id' will automatically update.
thanks in advance.
Jason Toy
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Consulta
En ORACLE, puedo pasar parametros a un query de la siguiente manera: select campo1, campo2 from tabla1 where campo3=&variable En Postgres como se hace? Gracias.
[GENERAL] error compiling 7.1 on Solaris 8 x86 (long)
When i try to compile postgresql on solaris 8/ x86 i get following: make -C tcop all make[3]: Entering directory `/export/home/soft/source/postgresql/7.1/postgresql-7.1/src/backend/tcop' gcc -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../src/includ e -c -o dest.o dest.c gcc -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../src/includ e -c -o fastpath.o fastpath.c gcc -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../src/includ e -c -o postgres.o postgres.c gcc -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../src/includ e -c -o pquery.o pquery.c gcc -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../src/includ e -c -o utility.o utility.c /usr/ccs/bin/ld -r -o SUBSYS.o dest.o fastpath.o postgres.o pquery.o utility.o make[3]: Leaving directory `/export/home/soft/source/postgresql/7.1/postgresql-7.1/src/backend/tcop' make -C utils all make[3]: Entering directory `/export/home/soft/source/postgresql/7.1/postgresql-7.1/src/backend/utils' gcc -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../src/includ e -c -o fmgrtab.o fmgrtab.c make -C adt SUBSYS.o make[4]: Entering directory `/export/home/soft/source/postgresql/7.1/postgresql-7.1/src/backend/utils/ad t' gcc -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/inc lude -c -o acl.o acl.c gcc -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/inc lude -c -o arrayfuncs.o arrayfuncs.c gcc -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/inc lude -c -o arrayutils.o arrayutils.c gcc -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/inc lude -c -o bool.o bool.c gcc -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/inc lude -c -o cash.o cash.c cash.c: In function `cash_div_flt8': cash.c:475: warning: implicit declaration of function `rint' gcc -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/inc lude -c -o char.o char.c gcc -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/inc lude -c -o date.o date.c date.c: In function `timestamp_date': date.c:276: warning: implicit declaration of function `isnan' date.c: In function `interval_time': date.c:734: warning: implicit declaration of function `ceil' date.c:734: warning: implicit declaration of function `floor' date.c:734: warning: implicit declaration of function `rint' gcc -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/inc lude -c -o datetime.o datetime.c datetime.c: In function `DecodeDateDelta': datetime.c:1886: warning: implicit declaration of function `ceil' datetime.c:1886: warning: implicit declaration of function `floor' datetime.c:1886: warning: implicit declaration of function `rint' datetime.c: In function `EncodeTimeSpan': datetime.c:2291: warning: type mismatch in implicit declaration for built-in function `fabs' gcc -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/inc lude -c -o datum.o datum.c gcc -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/inc lude -c -o float.o float.c float.c: In function `CheckFloat4Val': float.c:149: warning: type mismatch in implicit declaration for built-in function `fabs' float.c: In function `float8in': float.c:253: `HUGE_VAL' undeclared (first use in this function) float.c:253: (Each undeclared identifier is reported only once float.c:253: for each function it appears in.) float.c: In function `float8out': float.c:279: warning: implicit declaration of function `isnan' float.c: In function `dtrunc': float.c:944: warning: implicit declaration of function `floor' float.c: In function `dsqrt': float.c:964: warning: type mismatch in implicit declaration for built-in function `sqrt' float.c: In function `dpow': float.c:1000: warning: implicit declaration of function `pow' float.c: In function `dexp': float.c:1028: warning: implicit declaration of function `exp' float.c: In function `dlog1': float.c:1056: warning: implicit declaration of function `log' float.c: In function `dlog10': float.c:1077: warning: implicit declaration of function `log10' float.c: In function `dacos': float.c:1094: warning: implicit declaration of function `acos' float.c: In function `dasin': float.c:1117: warning: implicit declaration of function `asin' float.c: In function `datan': float.c:1140: warning: implicit declaration of function `atan' float.c: In function `datan2': float.c:1164: warning: implicit declaration of function `atan2' float.c: In function `dcos': float.c:1187: warning: type mismatch in implicit declaration for built-in function `cos' float.c: In function `dcot': float.c:1210: warning: implicit declaration of function `tan' float.c: In function `dsin': float.c:1234: warning: type mismatch in implicit declaration for built-in function `sin' make[4]: *** [float.o] Error 1 make[4]: Leaving directory `/export/home/soft/source/postgresql/7.1/postgresql-7.1/src/backend/utils/ad t' make[3]: *** [adt-r
[GENERAL] Inheritance in 7.1
Is there any way around having to own the table you are inheriting from in PostgreSQL 7.1? -- Alastair D'Silva (mob: 0413 485 733) Networking Consultant New Millennium Networking (web: http://www.newmillennium.net.au) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Classes of returned rows
Hi all, Is there a way of determining the original class of a row when querying inherited tables? Consider: CREATE TABLE users ( uid int4 SERIAL PRIMARY KEY, email varchar(60) ); CREATE TABLE clients ( surname varchar(30) [etc] ) INHERITS (users); CREATE TABLE suppliers ( surname varchar(30) [etc] ) INHERITS (users); Then, when I execute "SELECT * FROM users", I'd like to know which classes each row belongs to, ie. suppliers, clients or users. Hopefully, as easy as "SELECT oid, * FROM users" is! I'm using 7.1 already, and it kicks arse. Many thanks to pgsql-hackers for a wonderful piece of software I use every day. Thanks. :) - Jeff -- "Can we have a special TELSABUG category, and everything gets dropped to fix them first?" - Telsa Gwynne ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] function return multiple value
Dear: I wanna create a function that return a multiple rows in to a single row. example: CREATE FUNCTION GETNAME() RETURNS SETOF VARCHAR AS 'SELECT NAME FROM TEST;' LANGUAGE 'SQL'; when i call this function it return: -- peter susan john but I wonder is it possible to make it to return something like - petersusanjohn I have to use this result to print in quick report. THANK YOU VERY MUCH Harry Yau ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] state locked row (PLEASE HELP ME)
Is there a way to check if a specific row is locked ! (URGENT !) Thank you !!! Theo [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] general storage question with SQL
Hi, I'm a newbie with SQL in general. I am building a site with PHP and postrgreSQL that will have articles. In the postgreSQL db, should I store the actual article inside it, or just the path of the article on my machine? How about with images? What are the benefits and or bad points of storing in the database or just the path? Thank you. Jason ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Seral field value after INSERT !
Hi all ! I have been inserting records into a table using the SQL insert statement. One of the field types is serial, and I have been trying to figure out how to get the value that was assigned in the field as a result of the insert. The serial typed field is the only one guaranteed to be unique, so I can't really do a search, and there are several people adding data at once, so I can't reliable guess. Can anyone help? Thanks for all help ! ps : Sorry my bad english. --- Gabor Berenyi ---(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] indices are crashed after installation of rpm
hello all,
we are running PostgreSQL 7.0.2 on Solaris 2.6,/x86 compiled by gcc 2.8.1.
For installation Postgres on 70 Solaris-2.6 server we built packages
(SVR4-style).
The first time we installed Postgres everything was ok. But after a new
installation of the package ( - same Postgres version - just the starting
script changed) all indices where damaged (this is reproducible).
In the installation script the postmaster is stopped and started. Is it
possible the stopping and starting is crashing the indices,
because sometimes just stopping and starting the postmaster ist crashing the
indices (not reproducible)?
Has someone any idea what's wrong, do you need more informations?
greetings,
Peter
this ist the starting/stopping file S99postgres:
#!/bin/sh
umask 077
killdaemon()
{
PIDs=`ps -u postgres | sed '/PID/d;s/ \{1,\}/ /g' | cut -d" " -f2 |
sort -rn`
[ -n "$PIDs" ] && kill $PIDs
[ -f /tmp/.s.PGSQL.5432 ] && rm -f /tmp/.s.PGSQL.5432
echo "postmaster stopped"
}
startdaemon()
{
su postgres -c /export/home/postgres/start_postgres
echo "postmaster started"
su postgres -c /export/home/postgres/chk_user \
&& su postgres -c /export/home/postgres/create_user
}
case "$1" in
'start')
startdaemon
;;
'stop')
killdaemon
;;
'restart')
killdaemon
startdaemon
;;
*)
echo "Usage: $0 { start | stop | restart }"
;;
esac
exit 0
++
and start_postgres:
PATH=/opt/local/bin:$PATH:/opt/local/DWH/bin:.
LD_LIBRARY_PATH=/usr/openwin/lib:/usr/dt/lib:/opt/local/DWH/lib
export PATH LD_LIBRARY_PATH
PGLIB=/DWH/lib
PGDATA=$HOME/data
export PGLIB PGDATA
postmaster -i -D /export/home/postgres/data >> /tmp/postgres.log
--
Bezirksfinanzdirektion Muenchen
Vermessungsabteilung
...
Peter Keller: Tel: (+49) 089-2190-2594
Vermessungsrat : Fax: (+49) 089-2190-2459
Alexandrastr. 3 : mailto:[EMAIL PROTECTED]
80538 Muenchen : web: http://www.bayern.de/vermessung
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Re: Newbie struggling to set $PGDATA
Geoff Caplan wrote: > Please help out a Linx/Postgres newbie. > > I simply want to set the $PGDATA environmental variable, but can't > figure out how. The docs assume you already know... > > I have tried setting it in my bash /etc/profile configuration file, > and it shows up ok if I "echo $PGDATA" in the shell. But none of the > postgres utilities such as "initdb" seem to be able to find it. > > What don't I understand? I have already checked the docs/GreatBridge > manual/faqs/archive, so I would very much appreciate some help. > > Geoff Caplan > Look in your /etc/profile file if /etc/profile.local is called if so you can add to profile.local or otherwise create a new profile.local with the following lines: PATH=$PATH:/usr/local/pgsql/bin export PATH MANPATH=$MANPATH:/usr/local/pgsql/man export MANPATH LD_LIBRARY_PATH=/usr/local/pgsql/lib export LD_LIBRARY_PATH PGLIB=/usr/local/pgsql/lib export PGLIB PGDATA=/var/lib/pgsql export PGDATA (depending on your own path settings of course) If /etc/profile.local is not used, add the lines to /etc/profile. HTH, Nils Zonneveld ---(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] Re: How to configure iodbc access to local postgres db?
Told you it was something stupid. Turns out I was using the wrong Postgres ODBC driver. My MDK 7.2 installation had 2 things that looked like the driver. /usr/lib/libpsqlodbc.so and /usr/lib/libodbcpsql.so The correct one seems to be libodbcpsql I had tried both, and originally using libodbcpsql caused a segmentation violation, so I had been concentrating my efforts on the other one. Well, I ended up following the setup instructions in the unixODBC package documentation to get it working under that package. Once it worked there, I was able to get it working with libiodbc (which my application was using). Hint- it wants you to specify a UserName in .odbc.ini (you'd think it would default to the 'current user'). Thanks for listening, Rob ---(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] general storage question
Hi, I'm a newbie with SQL in general. I am building a site with PHP and postrgreSQL that will have articles. In the postgreSQL db, should I store the actual article inside it, or just the pat hof the article on my machine? How about with images? What are the benefits and or bad points of storing in the database or just the path? Thank you. Jason ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Problem with postgreSQL (number of backends)
Hi, i have problems with PostgreSQL 7.0.3 on a RedHat 6.2 System. Very often postgresql post me the following warning and error message. Warning: Unable to connect to PostgreSQL server: Sorry, too many clients already in /home/server/... PostgreSQL said: Unable to connect to server It seems to be a problem with buffers and backends. So i have restart the postmaster with the parameters "/usr/bin/postmaster -N 256 -B 512 " ... (i put these parameters in the file "postmaster.opts" in the postgresql data folder).But it seems to have no effect. The problem still exist. I need help thx Konstantin ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] PostgreSQL - PHP insert deleted from database immediately?
Hi, I'm trying to insert data from a php-page. The connection is open and
select-functions work perfectly. The problem is that an insert will - as far
as I know - insert the data and then immediately erase it.
The insert statement executed from php is:
$otsikko = "Insert testi";
$teksti = "Koeteksti 1";
$sqllause = "INSERT INTO jutut (otsikko,teksti) VALUES ('";
$sqllause = $sqllause . $otsikko . "','" . $teksti . "');";
ECHO $sqllause;
$tulos = pg_exec ($db,$sqllause);
if (!$tulos) {
echo "Sql-insert error.\n";
exit;
}
This results are the following in the web-browser:
INSERT INTO jutut (otsikko,teksti) VALUES ('Insert testi','Koeteksti
1');
Which when copied into the psql-console indeed does the insert correctly.
The reason I know the PHP-insert is done, but deleted is derived from the
fact that an automatically incremented index in the table is incremented
when the php-script is executed.
I get no error messages.
Could someone please help?
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] newbie ?'s
First off, what a wonderful mail list! I've been
listening in for a couple of days now, and it is marvelous the community you
people have.
I'm new to this software, so I won't waste much of
your time, but I'm curious as to the architecture of the server-side daemon,
etc.
1) Do you have to specify in advance the full
number of processes, or does it dynamically manage them ala Apache?
2) If your site was busy, will requests queue
up civilly or will they typically bomb right away if they can't find an
idle process.
3) I've been told PostgreSQL databases must be
periodically "rebuilt" so to speak, ("vacuumed"). Is this easy to do on-the-fly?
Rapid? Or, does the db need to go down awhile?
4) I can't find anything yet in the docs on
on-the-fly backups of the db. Can you simply copy the directory, or will this
not have integrity? What is the best strategy to study for backing up of
databases that may be in use 7x24?
5) This seemingly full-fledged password overhead is
worrisome. Is this tedious to manage in practice? Can the same (quasi-generic)
user be reading the database from many simultaneous processes?
Clayton Vernon
Houston, Texas
Re: [GENERAL] last comma inside "CREATE TABLE ()" statements
On Sunday 22 April 2001 10:21, you wrote: > however, this seems like a reasonable idea that would not introduce > any major problems. I have no objections, if someone wants to submit > a grammar patch. > > regards, tom lane Please don't. IMHO This would be an unnecessary 'extension' to postgresql that would allow scipts and a syntax that will generate errors when used on other DB's. We have moved all our projects from an Oracle 8 environment to postgress and really appreciate the flexibility, and quality without the undue complexity of running Oracle. We were able to do this fairly easily (about 1 day for half a dozen databases/applications each with about 5gig data) because our schema scripts ran without error the first time. By permitting sloppy syntax 'portability' could become 'no-so-portable' . Yes we could just make sure that we avoid sloppy syntax but it is great to have the parser say 'hey that was wrong, fix it and I won't complain again'. I'm more than happy to fix it now and know that I can use it later without hassle. Just my 2 (okay maybe 3) cents. Mike. === Mike Finn Tactical Executive Systems [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Problem importing auto-increment fields in MS Access to PostgreSQL
Hi there
I have a relatively simple problem :
I have a field named "id" that has type "Auto-number" in an Access database table.
When I export the database via ODBC to a PostgreSQL table, the "auto- number" property of this field is lost (!) :
CREATE TABLE "names"("id" int4,"name" varchar(50),"tel_no" varchar(50))
I also access the PostgreSQL database via a web-based GUI (phpPgAdmin from GreatBridge), and when I add new rows to it, the id field does not auto- increment.
This is my system :
odbc driver version (on Win machine) : 7.01.00.04 Insight Distrib. (PSQLODBC.DLL)
odbc driver version (on Red Hat machine) : postgresql-odbc-7.0.2- 17.rpm
postgre database version : postgresql-7.0.2.rpm
error messages on screen : none.
the commlog file : see attached file (psqlodbc.log)
Suggestions, workarounds and prayers would be appreciated !
love
rich handler
--- End of forwarded message ---
Rich Handler
OgilvyInteractive
18 Roeland St, Gardens, 8001, Cape Town
Fon 021 467 1402
Fax 021 467 1401
http://www.oi.co.za/
The following section of this message contains a file attachment
prepared for transmission using the Internet MIME message format.
If you are using Pegasus Mail, or any another MIME-compliant system,
you should be able to save it or view it from within your mailer.
If you cannot, please ask your system administrator for assistance.
File information ---
File: psqlodbc.log
Date: 25 Apr 2001, 13:08
Size: 3729 bytes.
Type: Unknown
psqlodbc.log
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Can't get lock for vacuum
Hi ! Last day, when I did a vacuum, postgres crashes. Now It works fine, but I can't do VACUMM, the error message is something like "Can't get lock. Is another Vacuum running ?" What can I do to reactivate the vacuum ? thank you. EuroVox 4, place Félix Eboue 75583 Paris Cedex 12 Tel : 01 44 67 05 05 Fax : 01 44 67 05 19 Web : http://www.eurovox.fr ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Consulta
En ORACLE, puedo pasar parametros a un query de la siguiente manera: select campo1, campo2 from tabla1 where campo3=&variable En Postgres como se hace? Gracias.
[GENERAL] configure on a P4?
I'm using Slackware 7.1 on VMWare with Postgresql 7.1 When i do a ./configure it stops with this message: checking host system type... Invalid configuration 'i?86-pc-linux-gnu': machine 'i?86-pc' not recognized Any ideas on this and how to solve it? THanks ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Problem with postgreSQL (number of backends)
Hi, i have problems with PostgreSQL 7.0.3 on a RedHat 6.2 System. Very often postgresql post me the following warning and error message. Warning: Unable to connect to PostgreSQL server: Sorry, too many clients already in /home/server/... PostgreSQL said: Unable to connect to server It seems to be a problem with buffers and backends. So i have restart the postmaster with the parameters "/usr/bin/postmaster -N 256 -B 512 " ... (i put these parameters in the file "postmaster.opts" in the postgresql data folder).But it seems to have no effect. The problem still exist. I need help thx Konstantin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Re: debug logs: query: rollback
Bob Parkinson <[EMAIL PROTECTED]> wrote: >query: rollback > >does this mean that a transaction has failed? Indeed. >I'm not seeing any other error messages A rollback need not be the result of the backend raising an error. It can also be triggered deliberately by your code that talks to the backend. >(mind you, I need to check my scripts and see what they are loggingas >errors). And check that their transaction behaviour is what you intended. E.g. for Perl's DBI, note what DBI(3pm) says about this: :The transaction behavior of the `disconnect' method is, sadly, :undefined. Some database systems (such as Oracle and Ingres) will :automatically commit any out standing changes, but others (such as :Informix) will rollback any outstanding changes. Applications not using :`AutoCommit' should explicitly call `commit' or `rollback' before calling :`disconnect'. HTH, Ray -- No longer will we mistake music for a noun, as its containers have tempted us to do for a century. We will realize once more that music is a verb, a relationship, a constantly evolving life form. John Perry Barlow in http://technocrat.net/958163435/index_html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
RE: [GENERAL] CREATE TABLE AS... syntax?
i think its: CREATE TABLE foo AS SELECT number FROM account; tamsin > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Dr. Evil > Sent: 25 April 2001 23:46 > To: [EMAIL PROTECTED] > Subject: [GENERAL] CREATE TABLE AS... syntax? > > > > I'm trying to use CREATE TABLE AS under 7.03. There are no > examples in the guide, so I tried a few things: > > CREATE TABLE foo (test INT4) AS SELECT number FROM account; > > and > > CREATE TABLE foo (test INT4) AS number FROM account; > > and both of them give ERROR: parser: parse error at or near "as". > Any tips on how to use this? > > Thanks > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Problem importing auto-increment fields in MS Access to Postgresql
Hi there
I have a relatively simple problem :
I have a field named "id" that has type "Auto-number" in an Access database table.
I know there is no 'auto_number' type in PostgreSQL. I know also, that SERIAL and SEQUENCE are workarounds for this problem, but I'm not sure how to make a field a SERIAL once its already created in the table.
I have tried making the DEFAULT value of field "id" set to SERIAL, but I get this error :
PostgreSQL said: ERROR: Attribute 'serial' not found Your query: ALTER TABLE "names" ALTER "id" SET DEFAULT SERIAL
This is my system :
odbc driver version (on Win machine) : 7.01.00.04 Insight Distrib. (PSQLODBC.DLL)
odbc driver version (on Red Hat machine) : postgresql-odbc-7.0.2-17.rpm
postgre database version : postgresql-7.0.2.rpm
error messages on screen : none.
the commlog file : see attached file (psqlodbc.log)
Suggestions, workarounds and prayers would be appreciated !
love
rich handler
The following section of this message contains a file attachment
prepared for transmission using the Internet MIME message format.
If you are using Pegasus Mail, or any another MIME-compliant system,
you should be able to save it or view it from within your mailer.
If you cannot, please ask your system administrator for assistance.
File information ---
File: psqlodbc.log
Date: 25 Apr 2001, 13:08
Size: 3729 bytes.
Type: Unknown
psqlodbc.log
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Detecting locks
Sebastian Bossung writes: > how do you find out if a row is locked _before_ using SELECT ... FOR UPADTE > on it. The SELECT will wait for the lock to be removed (if there was one), > making the user think the app crashed. There is currently no way to detect locks. You could implement a timeout in your application after which you send a query cancel. A waiting lock still distinguishes it from a crashed program, ISTM. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(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] Re: Need for newbie friendly docs (was Newbie struggling...)
Geoff Caplan wrote: [I assume you meant this for the list, not just me] >With 7.1 Postgres has the potential to enter the mainstream and >compete with MySQL for popularity. But the framework of the docs was >laid at a time when I guess it was safe to assume that users would >have advanced *nix skills. > >I recently had to ask the list for help to set the $PGDATA variable. A >number of kind people responded with the answer. > >I had consulted the Postges and Great Bridge docs, the archive, >Bruce's book and three weighty volumes on *nix, and could not find a >word in any of them that explains how to do this. A single line in the >docs could have saved this user a couple of frustrating hours. I >suspect that this is a pretty typical newbie experience with Postgres >admin. > >Of course, this great list is an important resource. But until the >development team somehow finds the energy to review the docs and make >them more newbie friendly, I suspect that Postgres will remain a >platform for the gurus. This is a pity, because 7.1 really rocks and >deserves to be more widely used. > >Or perhaps we will have to wait for a killer newbie book like the Paul >DuBois book on MySQL - Bruce's book is helpful, but much too thin on >the admin issues. And because I suspect that Bruce was behind much of >the docs, it tends to share the same blind spots. > >Just the perspective of someone who is having to teach themselves >*nix/client-server the hard way. Your question about setting PGDATA was really about setting environmental variables in a shell; it is only incidental to PostgreSQL. There is an enormous amount of background knowledge assumed when you document an application, and this is necessary, or else every document would become a Windows-like spoon-feeder, which would spend so much time on basic stuff that it would never cover the real meat. I'm not sure that it is either possible or desirable for PostgreSQL to attempt to satisfy a newbie's need for basic training in Unix. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Submit yourselves therefore to God. Resist the devil, and he will flee from you." James 4:7 ---(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] debug logs: query: rollback
(With 7.0.3, going to 1.1 RSN'ish.) I've just stared looking at logs and I'm getting lines in the log: query: rollback does this mean that a transaction has failed? I'm not seeing any other error messages (mind you, I need to check my scripts and see what they are loggingas errors). usr/local/pgsql/bin/postmaster: reaping dead processes... /usr/local/pgsql/bin/postmaster: CleanupProc: pid 13874 exited with status 0 CommitTransactionCommand StartTransactionCommand query: rollback ProcessUtility: rollback CommitTransactionCommand StartTransactionCommand query: begin ProcessUtility: begin CommitTransactionCommand StartTransactionCommand query: rollback ProcessUtility: rollback CommitTransactionCommand proc_exit(0) shmem_exit(0) exit(0) Cheers Bob Parkinson [EMAIL PROTECTED] -- Technical Manager: Biome http://biome.ac.uk/ Greenfield Medical Library, Queens Medical Centre, Nottingham. 0115 9249924 x 42059 -- We are stardust ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Run a create object script ???
On Thu, 26 Apr 2001 [EMAIL PROTECTED] wrote: > I have written all the script in a db.txt file, how to call the script from the > file??? > In Oracle one can issue $ @filename and the job get done, whats the relative > option in POSTGRES ??? \i filename use \? for a complete list. -- Tod McQuillin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Re: Performance: sql functions v. plpgsql v. plperl
On Wed, Apr 25, 2001 at 05:03:51PM -0400, Tom Lane wrote: > Joel Burton <[EMAIL PROTECTED]> writes: > > couldn't the parse tree be cached from this for each backend? > > Yes, if someone wanted to work on it ... It needs global query plan cache and integrate it to SQL function handler. The usable cache is available at my ftp, but I haven't time to continue on this. Volunteers? :-) Karel ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] Run a create object script ???
Hi, I have written all the script in a db.txt file, how to call the script from the file??? In Oracle one can issue $ @filename and the job get done, whats the relative option in POSTGRES ??? DArshan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
