[GENERAL] Using As with Inheritance?
I have a parallel inheritance going on, so I was wondering if there was a way to re-name a derived column? This would make my design clearer. - CREATE TABLE B ( NAME VARCHAR(10) ); CREATE TABLE C ( ... ) INHERITS(B); CREATE TABLE X ( A VARCHAR(10), B VARCHAR(10), CONSTRAINT FOREIGN KEY (B) REFERENCES B(OID) ); CREATE TABLE Y ( B AS C, /* Syntatic Sugar */ D VARCHAR(10), CONSTRAINT FOREIGN KEY (C) REFERENCES C(OID) ) INHERITS(X) Here, I've added the syntax "AS" to show that column A in table X, is called B in the derived table Y. Thank you for your thoughts. :) Clark Evans
[GENERAL] A mistake generates strange result
Just for PgSQL's development group think about
I made a mistake typing a query that generates a strange result (Very strange).
The query: select text('12345678'::float8);
It returns a date in datetime format !!
If you use: select ('12345678'::float8)::text;everything runs well.
[GENERAL] Check constraint memory problem
Dear Thomas,
I began with ver.6.4 beta. What do I have to do ? Come back to 6.3 ?
However, I tested more and the problem appear when you use "check" clause of "create
table".
Could you reproduce the situation with your PostGres 6.3 ? See the procedures below.
create table TBL (FLD1 int4, FLD2 char check (FLD2 in ('A','B')));
\copy TBL from tbl.txt (a file with any number and the letter A);
update TBL set FLD2='B';
Result of copy and update command using a tbl.txt file with 100.000 rows in RedHat 5.2
and PgSQL 6.4.2
Max memory used during execution: incredible 77 Meg.
Without check clause postmaster uses just 2.6 Meg.
I think there is anything wrong.
Does anybody get the same result ?
Thanks,
Ricardo Coelho.
- Mensagem original -
De: Thomas Reinke [SMTP:[EMAIL PROTECTED]]
Enviada em: Sexta-feira, 5 de Fevereiro de 1999 11:29
Para: Ricardo J.C.Coelho; [EMAIL PROTECTED]
Assunto:Re: [GENERAL] Postmaster memory problem
I regularly do this with tables in access of 1.5 million rows.
E.g. update table set field1='',field2='',field3=''. I have
never run into memory problems. Mind you, I'm using RedHat 4
still, with PostGres 6.3, 128Meg Ram, 90 Meg swap.
"Ricardo J.C.Coelho" wrote:
>
> Hi,
>
> Does anybody use a single query affecting many rows (100,000 or more) ?
>
> I have a table with 500,000 rows. If I update all rows with one query (For
> example: update table set field='A';), postmaster will allocate the memory
> of the computer (including swap area), until "palloc memory exhauted". The
> same thing happens with COPY command. To load this table I splited the text
> file in many smaller files.
>
> What did I do wrong during Pgsql compilation ? I'm using RedHat 5.2 (Intel)
> and Pgsql 6.4.2
>
> Is there a way to limit the amount of memory used by postmaster ? I tried
> ulimit (unix command) and postmaster option but it aborted.
>
> Thanks,
>
> Ricardo Coelho.
--
Thomas ReinkeTel: (416) 460-7021
Director of Technology Fax: (416) 598-2319
E-Soft Inc. http://www.e-softinc.com
[GENERAL] palloc with user's functions
Hi, I create a C function: create function FUNC (text) returns text as '/path/func.so' language 'C'; As described in manual, I allocate space to text return: ret=(text *) palloc (datasize+VARHDRSZ); The function works fine with small tables, but some problem occurs during server pfree with big tables. When I use: select FUNC(INT4_FIELD::text) from TBL; Server goes down with this message: PortalHeapMemoryFree: 0x8 not in alloc set. Thanks in advance for your help. Ricardo Coelho.
[GENERAL] ODMG.ORG
Just a general note. How close are PostgreSQL's object extensions to those at the ODMG? How hard would it be to write complant interface? Anyone working on it? See: http://www.odmg.org/ Clark
RE: [GENERAL] No MAX function
It depends of data type - unfortunately max was not defined for ALL appropriate types. Through testo=> select max(bypref) from inetspec; max --- 24 where bypref is int2. On Sat, 6 Feb 1999, dustin sallings wrote: > Date: Sat, 6 Feb 1999 13:10:56 -0800 (PST) > From: dustin sallings <[EMAIL PROTECTED]> > To: Jonny Hinojosa <[EMAIL PROTECTED]> > Cc: PostGreSQL Mailing List <[EMAIL PROTECTED]> > Subject: RE: [GENERAL] No MAX function > > On Sat, 6 Feb 1999, Jonny Hinojosa wrote: > > // cdmwhere=> SELECT * FROM traplog WHERE millisid= > //(SELECT MAX(millisid) FROM traplog > //WHERE esn LIKE '0f:11:19:82:54:35'); > // ERROR: No such function 'max' with the specified attributes > // cdmwhere=> > > What's a millisid? If it's something obscure, there may not have > been a max() defined for it. > > // > // -Original Message- > // From: dustin sallings [mailto:[EMAIL PROTECTED]] > // Sent: Saturday, February 06, 1999 2:21 PM > // To: Jonny Hinojosa > // Cc: PostGreSQL Mailing List > // Subject: RE: [GENERAL] No MAX function > // > // > // On Sat, 6 Feb 1999, Jonny Hinojosa wrote: > // > // select * from events > //where event_id=(select max(event_id) from events); > // > //That works in my event database. > // > // // SET QUERY_LIMIT TO restricts the number of rows returned. I want to > // // determine the largest value in a column. Sorry if my question was > // unclear, > // // I am new to %SQL. > // // > // // Jonny > // // > // // -Original Message- > // // From: Neil Burrows [mailto:[EMAIL PROTECTED]] > // // Sent: Saturday, February 06, 1999 1:51 PM > // // To: Jonny Hinojosa > // // Cc: [EMAIL PROTECTED] > // // Subject: RE: [GENERAL] No MAX function > // // > // // > // // Hi, > // // > // // I believe there is a patch in "contrib" directory that does this, but if > // you > // // have Postgres 6.4 then have a look at the > // // > // // SET QUERY_LIMIT TO > // // > // // command. > // // > // // Regards, > // // > // // ---[ Neil > // urrows ]- > // // E-mail: [EMAIL PROTECTED] ** This e-mail was > // ** > // // Web : http://www.remo.demon.co.uk/** Made in Scotland > // ** > // // ---< PGP Key available from http://www.remo.demon.co.uk/pgp/ > // > > // // > // // > // // > // // > -Original Message- > // // > From: [EMAIL PROTECTED] > // // > [mailto:[EMAIL PROTECTED]]On Behalf Of Jonny Hinojosa > // // > Sent: 06 February 1999 19:13 > // // > To: PostGreSQL Mailing List > // // > Subject: [GENERAL] No MAX function > // // > > // // > > // // > Does anyone have a work around for the lack of a MAX function ? I need > // to > // // > find the last record WHERE a LIKE 'x' AND b LIKE 'y'. > // // > > // // > Jonny Hinojosa > // // > TCA Internet > // // > [EMAIL PROTECTED] > // // > 409.693.8885 > // // > > // // > > // // > // // > // // > // > // -- > // Principal Member Technical Staff, beyond.comThe world is watching > // America, > // pub 1024/3CAE01D5 1994/11/03 Dustin Sallings <[EMAIL PROTECTED]> > // |Key fingerprint = 87 02 57 08 02 D0 DA D6 C8 0F 3E 65 51 98 D8 BE > // L__ and America is watching TV. > // __ > // > // > // > > -- > Principal Member Technical Staff, beyond.comThe world is watching America, > pub 1024/3CAE01D5 1994/11/03 Dustin Sallings <[EMAIL PROTECTED]> > |Key fingerprint = 87 02 57 08 02 D0 DA D6 C8 0F 3E 65 51 98 D8 BE > L__ and America is watching TV. __ > > > Aleksei Roudnev, Network Operations Center, Relcom, Moscow (+7 095) 194-19-95 (Network Operations Center Hot Line),(+7 095) 230-41-41, N 13729 (pager) (+7 095) 196-72-12 (Support), (+7 095) 194-33-28 (Fax)
[GENERAL] Redo-log
Do you plan the transaction log support in 6.5 release, and could you give us a brief description on ?Will it allow to recover until the last committed transaction in case of disk crash ?Thanks a lot for your answer and for all the things you do to make Postgres the best.RegardsJérôme Doucerain
[GENERAL] Re: Client Installation
Jeff Gerhart <[EMAIL PROTECTED]> writes: | machine) without installing the entire postgres environment. I | thought I'd seen a place where somebody had broken the client and | backend into seperate RPMs, but I can't find where I saw that | discussed. Once, I've done this for SuSE Linux. Sorry, I'm not able to check the location (I'm ill at home), but it should be found somewhere at ftp://ftp.suse.com/pub/SuSE-Linux/suse/ap1/postgres.rpm (the engine) and pg_ifa.rpm (the interfaces = client). pg_dbase.rpm contains the initial database. The source are in .../zq1. I'm sure, my schema could be improved. -- Karl Eichwalder
[GENERAL] Re: [INTERFACES] Re: connecting: unix socket? Yes. TCPIP port? No.-i? Yes.
On Sun, 7 Feb 1999, Bob VonMoss wrote: > Tom Lane wrote: > > > Bob VonMoss <[EMAIL PROTECTED]> writes: > > > [ can connect via unix socket, but not via TCP ] > > > > > The administrator says this is how postmaster is invoked: > > > /usr/local/pgsql/bin/postmaster -S -i -D /usr/local/pgsql/data -p 5432 > > > > OK, that eliminates the "forgot -i" gotcha. > > > > James Thompson is almost certainly right that the problem is that > > Postgres' pg_hba.conf file is not set up to allow connections from > > whichever IP address you are connecting from. > > > > We heard about a similar problem recently which turned out to be > > due to use of "virtual server" IP addresses. > > The pgsql administrator put a line in pg_hba.conf that looks like this, > where my_db is substituted for my database name: > > host my_db 0.0.0.0 0.0.0.0 ident sameuser > > I'm still getting the same 'User authentication failed' messages. Same thing > from JDBC. Do I need access to the 'template1' table also? no. Is the machine you are running the java app or psql on running ident? > Here's a transcript of a session from the command -- Peter T Mount [EMAIL PROTECTED] Main Homepage: http://www.retep.org.uk PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres Java PDF Generator: http://www.retep.org.uk/pdf
[GENERAL] Re: connecting: unix socket? Yes. TCPIP port? No. -i? Yes.
Tom Lane wrote: > Bob VonMoss <[EMAIL PROTECTED]> writes: > > [ can connect via unix socket, but not via TCP ] > > > The administrator says this is how postmaster is invoked: > > /usr/local/pgsql/bin/postmaster -S -i -D /usr/local/pgsql/data -p 5432 > > OK, that eliminates the "forgot -i" gotcha. > > James Thompson is almost certainly right that the problem is that > Postgres' pg_hba.conf file is not set up to allow connections from > whichever IP address you are connecting from. > > We heard about a similar problem recently which turned out to be > due to use of "virtual server" IP addresses. The pgsql administrator put a line in pg_hba.conf that looks like this, where my_db is substituted for my database name: host my_db 0.0.0.0 0.0.0.0 ident sameuser I'm still getting the same 'User authentication failed' messages. Same thing from JDBC. Do I need access to the 'template1' table also? Here's a transcript of a session from the command line on the same machine (with host, database and login id substituted): bash$ psql my_db Welcome to the POSTGRESQL interactive sql monitor: Please read the file COPYRIGHT for copyright terms of POSTGRESQL type \? for help on slash commands type \q to quit type \g or terminate with semicolon to execute query You are currently connected to the database: my_db ubf99=> \z Couldn't find any tables! ubf99=> \q bash$ psql -h my-isp.com -p 5432 -d my_db -u Username: login-id Password: Connection to database 'my_db' failed. User authentication failedbash$ bash$ bash$ psql -h my-isp.com -d my_db Connection to database 'my_db' failed. User authentication failedbash$ bash$ psql -h my-isp.com -u my_db Username: login-id Password: Connection to database 'my_db' failed. User authentication failedbash$ -- Bob VonMoss mailto:[EMAIL PROTECTED] from Chicago, IL
