Re: [GENERAL] Query with multiples operators BETWEEN
Renaud Tthonnart <[EMAIL PROTECTED]> writes: > Nested Loop (cost=0.00..56.67 rows=3 width=20) > -> Seq Scan on observation o (cost=0.00..30.00 rows=1 width=4) > -> Seq Scan on exposure_epic e (cost=0.00..22.50 rows=333 width=16) Do a VACUUM ANALYZE, for starters. These results look like the planner is working with the initial default estimates for a never-vacuumed table. > I haven't create any index Indexes might be a good idea too. http://www.postgresql.org/devel-corner/docs/postgres/indices.html has a good intro to the basics. regards, tom lane
Re: [GENERAL] is this expected or am i on crack?
On Sun, 18 Feb 2001, Chris Humphries wrote: > Table "facts" > Attribute | Type | Modifier > -+--+-- > keyword | varchar(80) | > description | varchar(255) | > url | varchar(255) | > > select keyword from facts as f1 > where 1 <> (select count(*) from facts as f2 where f1.keyword = f2.keyword) > order by keyword; > > it took about 65 minutes to complete. i know that it is doing alot of work, > but it there a way that it could be sped up, like something i could configure > or something that i could do to make it faster? Hm, this will probably run the subquery for every row in the table. Try: select keyword, count(keyword) from facts group by keyword having 1 <> count(keyword) order by count(keyword) desc Is this any faster? -- Tod McQuillin
Re: [GENERAL] Building SPI programs
Aristide Aragon <[EMAIL PROTECTED]> writes: > On Thu, Feb 22, 2001 at 07:49:56PM -0800, Ian Lance Taylor wrote: > > Aristide Aragon <[EMAIL PROTECTED]> writes: > > > > > I have to do some programming in C or C++ and PostgreSQL. > > SPI is only for stored procedures which are dynamically linked into > > the Postgres backend. > > > > Ordinary code doesn't do that. It uses some one of the other > > mechanisms. > > I don't understand.. So I shouldn't use SPI? If I should, how would I comple a >program, if not, what can I use? Can you help me? You didn't really say what you wanted to do, so I don't know whether you should use SPI or not. If you don't have a clue what I was talking about in my previous message, then you should not use SPI. You should probably use the libpq interface. Ian
Re: [GENERAL] Building SPI programs
Aristide Aragon <[EMAIL PROTECTED]> writes: > I have to do some programming in C or C++ and PostgreSQL. > Having not ever done this before, I opened the documentation (in my machine, >/usr/local/pgsql/doc) and read about the options I had. SPI, libpq, libpq++ and ecpg. >I don't want, if possible, an embeded language, or anything that requires a >preprocessor. So, I thought to start by trying the first, SPI, which also seemed to >be simple. SPI is only for stored procedures which are dynamically linked into the Postgres backend. Ordinary code doesn't do that. It uses some one of the other mechanisms. Ian > /tmp/ccPIp24d.o(.text+0x7): undefined reference to `SPI_connect' > /tmp/ccPIp24d.o(.text+0x2b): undefined reference to `SPI_exec' > /tmp/ccPIp24d.o(.text+0x33): undefined reference to `SPI_finish' When your program is dynamically linked in, those functions will be satisfied by definitions in the Postgres backend. > So I supposed I needed to link against some library. With grep I searched >/usr/local/pgsql/lib for SPI_, and I found no .a's matching. > I found that /usr/local/pgsql/lib/plpgsql.so and /usr/local/pgsql/lib/pltcl.so had >"SPI_", however none worked. Those are examples of backend procedures which are dynamically linked in, in those cases to define a language. Ian
Re: [GENERAL] Building SPI programs
On Thu, Feb 22, 2001 at 07:49:56PM -0800, Ian Lance Taylor wrote: > Aristide Aragon <[EMAIL PROTECTED]> writes: > > > I have to do some programming in C or C++ and PostgreSQL. > SPI is only for stored procedures which are dynamically linked into > the Postgres backend. > > Ordinary code doesn't do that. It uses some one of the other > mechanisms. > > Ian I don't understand.. So I shouldn't use SPI? If I should, how would I comple a program, if not, what can I use? Can you help me? Thanks in advance Aristide > > /tmp/ccPIp24d.o(.text+0x7): undefined reference to `SPI_connect' > > /tmp/ccPIp24d.o(.text+0x2b): undefined reference to `SPI_exec' > > /tmp/ccPIp24d.o(.text+0x33): undefined reference to `SPI_finish' > > When your program is dynamically linked in, those functions will be > satisfied by definitions in the Postgres backend. > > > So I supposed I needed to link against some library. With grep I searched >/usr/local/pgsql/lib for SPI_, and I found no .a's matching. > > I found that /usr/local/pgsql/lib/plpgsql.so and /usr/local/pgsql/lib/pltcl.so had >"SPI_", however none worked. > > Those are examples of backend procedures which are dynamically linked > in, in those cases to define a language. > > Ian
Re: [GENERAL] problem while compiling user c functions in 7.1beta4
hubert depesz lubaczewski <[EMAIL PROTECTED]> writes: > sorry. my fault. i was wrong because the files were not installed in working > directory. strange. error in makefile's? No, an extremely deliberate change, which was discussed at length in the mailing lists. The default install now installs only client-side header files, no server-side files. If you want to compile server-side code, either point your -I path at pgsql/src/include or do "make install-all-headers". regards, tom lane
Re: [GENERAL] Problems when dumping a database
Tressens Lionel <[EMAIL PROTECTED]> writes: > Le 20.02.01 à 13:18, "Tom Lane" écrivait : > )Tressens Lionel <[EMAIL PROTECTED]> writes: > )> My pgsql DBMS works great except that when I want to dump a database, > )> pg_dump says that database template1 doesn't exist (actually it does !) > )> and the dump is aborted... > ) > )Curious. Can you connect to template1 by hand (eg "psql template1")? > Yes I can. But something strange is that I can use the tables of the > template1 database : > template1=> select * from pg_shadow; (this will work) > But I cannot list them : > template1=> \d > Couldn't find any tables, sequences or indices! Hm. Not finding any user tables in template1 is normal behavior --- try \dS to view the system tables. However, I now have a theory, because psql has reported this condition as 'No relations found.' since 7.0 or before. Evidently you are invoking an old (6.5 or before) psql. What version is your database server, and is it possible you're calling a psql of an older version? And, even more to the point, a pg_dump also of an older version? > SELECT oid FROM pg_database WHERE datname = 'template1' > But this SELECT returns two tuples ! I have two identical tuples in > pg_database for template1. > Perhaps deleting one will help me, but which one (equal ?). This is definitely uncool, but I'm not sure if it's related to the pg_dump problem or not. You could try deleting the one of larger OID (the correct OID for template1 is about 17000 depending on what version you're running) and then do a pg_database vacuum. regards, tom lane
Re: [GENERAL] Questions to lists / translations
On Sat, Feb 17, 2001 at 10:12:27AM +0100, Emmanuel Charpentier wrote: > A good "knowledge base" for PostgreSQL was the mailing list archive. > However, it started having serious problems abck in november, and I > haven't checked it since. They're also archived on GeoCrawler: http://www.geocrawler.com/lists/3/Databases/ All the lists are there (-general, -novice, -hackers, etc), although I don't know how far back the archive goes. HTH, Neil -- Neil Conway <[EMAIL PROTECTED]> Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc Encrypted mail welcomed Vegetarians do not love animals... they hate plants.
[GENERAL] Re: Date types in where clause of PreparedStatement
Using a DateTime value in a WHERE clause is not a good idea for the reason that DateTimes are usually represented by a floating point value in the database itself. And since floating point numbers are prone to rounding errors, they don't make for a good unique identifier. If you have to use dates in your WHERE clauses, try doing a BETWEEN. Greg - Original Message - From: "Ian deSouza" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, February 18, 2001 11:17 AM Subject: Date types in where clause of PreparedStatement > Can anybody show any reason why the code using a prepared statement with a > where clause using a date won't find any records on PostgreSQL but WILL work > using Access and the jdbc-odbc bridge? > > ie. > > SQL: select > OrderID,CustomerID,EmployeeID,OrderDate,RequiredDate,ShippedDate,ShipVia,Fre > ight,ShipName,ShipAddress,ShipCity,ShipRegion,ShipPostalCode,ShipCountry > from Orders where OrderDate = ? > Setting value: OrderDate 1996-07-05 00:00:00.0 > > It does not cause an exception, but fails to retrieve anything with > PostgreSQL yet works and retrieves the right record with Access. > > I further verified that a record does exist with the PostreSQL database. > > > >
[GENERAL] sort order problem
hi! could somebody tell me where is the problem? i have postgres 7.0.3-4 comming with unstable debian distribution (locales 2.2.1-1, libc6 2.2.1-1) i am unable to force postgres to sort in correct order when I have national characters (polish) I tried differents locales settings (i thik that LANG=pl_PL, LC_CTYPE=iso_8859_2 worked previously) on the backend side and from the clients side. the result is always the same. could somebady help me? thx!
[GENERAL] Building SPI programs
Hello I have to do some programming in C or C++ and PostgreSQL. Having not ever done this before, I opened the documentation (in my machine, /usr/local/pgsql/doc) and read about the options I had. SPI, libpq, libpq++ and ecpg. I don't want, if possible, an embeded language, or anything that requires a preprocessor. So, I thought to start by trying the first, SPI, which also seemed to be simple. My problem comes when compiling, since the documentation doesn't say where the include files are, nor what libraries to compile against. So, I found out about the include dirs required (amazingly, one was in the source directory tree, and was not copied to usr/local/pgsql/include when I installed PGSQL), and ended with cc -I/busa/tmp/postgresql-7.0.2/src/include/ -I/usr/local/pgsql/include test.c -o test With this command line, I got /tmp/ccPIp24d.o: In function `main': /tmp/ccPIp24d.o(.text+0x7): undefined reference to `SPI_connect' /tmp/ccPIp24d.o(.text+0x2b): undefined reference to `SPI_exec' /tmp/ccPIp24d.o(.text+0x33): undefined reference to `SPI_finish' So I supposed I needed to link against some library. With grep I searched /usr/local/pgsql/lib for SPI_, and I found no .a's matching. I found that /usr/local/pgsql/lib/plpgsql.so and /usr/local/pgsql/lib/pltcl.so had "SPI_", however none worked. So, can somebody help me compile a C program with SPI? Thanks Aristide
[GENERAL] IPC Shared Memory
Hi when i run postmaster i got the following error and postmaser doesn't start, FATAL 1: InitProcGlobal: IpcSemaphoreCreate failed IpcSemaphoreCreate: semget failed (No space left on device) key=014, num=16, permission=600 This type of error is usually caused by an improper shared memory or System V IPC semaphore configuration. For more information, see the FAQ and platform-specific FAQ's in the source directory pgsql/doc or on our i made query to admin and the max shared mem setting is already set to the max possible * * IPC Shared Memory * 4294967295 max shared memory segment size (SHMMAX) 1 min shared memory segment size (SHMMIN) 100 shared memory identifiers (SHMMNI) 10 max attached shm segments per process (SHMSEG) I tried -N and -B but didn't have luck. Could anyone help me on this? This problem appeared after admin changed their config i believe, could that be it? Thanks Katsu
[GENERAL] is this expected or am i on crack?
hello all, first off, some information: = = the table: Table "facts" Attribute | Type | Modifier -+--+-- keyword | varchar(80) | description | varchar(255) | url | varchar(255) | the sql: select keyword from facts as f1 where 1 <> (select count(*) from facts as f2 where f1.keyword = f2.keyword) order by keyword; my system: Linux kernel 2.2.x p166 32M ram 200M+ swap = = it took about 65 minutes to complete. i know that it is doing alot of work, but it there a way that it could be sped up, like something i could configure or something that i could do to make it faster? just looking to see if the time it took to take this is to be expected? postmaster was using from 78-98% of the cpu for the whole time. in hind site i wish i would have made keyword not null primary key... *sigh*, learn from mistakes i guess. now have to fix the duplicates and move all the rows into a new table with keyword as primary key... thanks for any help, and developers: thanks for developing a real non-toy database for the masses, -chris humphries
[GENERAL] Re: How to use gethostbyname()
On Fri, 16 Feb 2001, Tom Lane wrote: > <[EMAIL PROTECTED]> writes: > > In psql, gethostbyname() is defined as taking a text parameter and return an > > inet type value or reference. > > Huh? There's no such function in the standard Postgres distribution. If you do a '\df' at prompt, you will be able to see I am not lying. Phillip Pan ---
Re: [GENERAL] install problem.
Hi Wayne Ward wrote: > hello, > I finished installing pg but have two question. > 1.In the install doc it makes reference to a section called "Regression > Test". I can't seem to > find this. So, I have done this test yet. > /src/test/regress. Best begin:vcard n:Vasudevan;Thyagarajan tel;fax:650 210 3801 tel;home:510 445 0123 tel;work:650 210 3793 x-mozilla-html:FALSE adr:;; version:2.1 email;internet:[EMAIL PROTECTED] fn:Thyagarajan Vasudevan end:vcard
[GENERAL] Re: creating assertions in functions
hmm.. actually the problem i encountered didn't require me to try the impossible below! -leon Leon Sol Levy <[EMAIL PROTECTED]> wrote: : hi. i was wondering how to go about creating an assertion to be used within a : pl/sql function. : do i put : : create assertion : check ... : : before or after "begin"? : : thanks : -- : -leon (in the dungeon of EUII @ UC Davis) -- -leon (in the dungeon of EUII @ UC Davis)
[GENERAL] doh! 13000+ rows
doh, from my last post, the table had 13000+ rows -chris humphries
[GENERAL] Date types in where clause of PreparedStatement
Can anybody show any reason why the code using a prepared statement with a where clause using a date won't find any records on PostgreSQL but WILL work using Access and the jdbc-odbc bridge? ie. SQL: select OrderID,CustomerID,EmployeeID,OrderDate,RequiredDate,ShippedDate,ShipVia,Fre ight,ShipName,ShipAddress,ShipCity,ShipRegion,ShipPostalCode,ShipCountry from Orders where OrderDate = ? Setting value: OrderDate 1996-07-05 00:00:00.0 It does not cause an exception, but fails to retrieve anything with PostgreSQL yet works and retrieves the right record with Access. I further verified that a record does exist with the PostreSQL database.
[GENERAL] OIDs in triggers
Hi folks, Here's what I have: create table contact ( contact_id serial, ... primary key (contact_id) ); create table customers ( customer_id serial, shipping_contact_id int4, billing_contact_id int4, ... primary key (customer_id) ); Well, I want to write a trigger on CUSTOMERS that will insert 2 records into table CONTACT and link it to fields shipping_contact_id and billing_contact_id in CUSTOMERS. But how, if I won't know oids of inserted rows ? I may remember sequence's nextval in variable and use it in both insert and update statements, but during many insert in contact table sequence's nextval may be used by concurrent transactions, so one of them will be rolled back. I prefer to do it with PL/pgSQL, anyway I can't use C. Any ideas ? Thanks, Max Rudensky.
[GENERAL] Questions to lists / translations
Christopher Sawtell wrote: > [ ... High number iof questions to the list, although of good level ... ] > This is a universal problem. It is that once a particular package reaches > that critical mass it is completely impossible for a small team of > developers to both help the user community _and_ to develop anything at > all. I've seen this in both the free and the comercial software worlds. > > The commercial world tries to solve it by having "Knowledge Base" > machinery of some kind or another. My own exp. is that it simply does not > work. A good "knowledge base" for PostgreSQL was the mailing list archive. However, it started having serious problems abck in november, and I haven't checked it since. > It might help to install ht://dig so that the online documentation can be > searched easily. If people think that that would be a good idea then I'd > be happy to make that contribution. This engine could also usefully be aimed at the mailing lists archive ! However, a *very* useful feature would be a "human made" indexing. Quite a load of work ... It might also be useful to have on the lists people with a good knowledge of PostgreSQL (at least recent versions) dedicated to answering these questions. This would ease the load on developpers proprio dictu. This way, you would have a kind of a three-tiered help system : - Search engine on the docs, the FAQs and the lists archive ; - "Answer" volunteers for hard-but-answerable-from-the-specs questions ; - Developpers for questions requiring a good knowledge of PostgreSQL internals. > Another point is that PostgreSQL is widely used by people who have learnt > English at school. English is a proper horror of a language & it must be > extremely difficult to understand the docs. if you didn't learn English on > your Mother's knee. I learnt French at school, but I would really _hate_ > to have to understand PostgreSQL from French docs. I'm suggesting that now > that the critical mass of users is nearly upon us that a serious > translation effort be made. Unfortunately I am not sufficiently able in > any foreign language to help with translation, but could perhaps attempt > to make the language of the documentation somewhat easier to understand. Agreed. But keep in mind that a tralnslation is a *huge* work, especially in a domain such as computers where most competent people are used to more-or-less "think in English" about their domain of expertise. Furthermore, PostgreSQL docs are a hell of a moving target ... Therefore, such a project needs a *lot* of coordination. Any thoughs ? Emmanuel Charpentier -- Emmanuel Charpentier[EMAIL PROTECTED] [EMAIL PROTECTED]
[GENERAL] Function reference
Is there a Postgresql function reference somewhere? I can't find it in the documentation and I'm not getting far without it. -- Bruce
[GENERAL] database backup...
Hi, Our old(7.0.2) postgres base directory is, say /user3/local/pgsql and new(7.0.3) postgres base directory is /usr/local/pgsql. As the doc suggested I tried to backup the whole data(7.0.2), but it was unsuccessful. Since the normal restoration is almost over, I was thinking about the other possibilities. Is it ok just by copying the directory, say /user3/local/pgsql/data to /usr/local/pgsql/data and base/ directory also ? Or tar-it the whole old data directory and untar it to new? I haven't done anything to new database yet, except compile and initializing. Thanks. Jinsoo
Re: [GENERAL] CGI error
On Thu, Feb 22, 2001 at 07:52:54PM -0500, Jeff wrote:
> I'm getting this when access the cgi:
What HTTP daemon are you using?
> The specified CGI application misbehaved by not returning a complete set of
> HTTP headers. The headers it did return are:
Which headers did it return? You seem to have forgotten to paste something.
> printf("Content-Type: %s\n\n", contentType);
The HTTP standard specifies that lines should be ended by a CRLF pair -
i.e. "\r\n". Usually your HTTP daemon is smart enough to correct it (or
the browser does), but maybe that's the problem.
This doesn't seem related to Postgres. Try getting a simple CGI script
to work (i.e. printing "hello world") -- the same problem should occur,
AFAICT.
HTH,
Neil
--
Neil Conway <[EMAIL PROTECTED]>
Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc
Encrypted mail welcomed
If one really is a superior person, the fact is likely to leak out
without too much assistance.
-- John Andrew Homes
[GENERAL] install problem.
hello, I finished installing pg but have two question. 1.In the install doc it makes reference to a section called "Regression Test". I can't seem to find this. So, I have done this test yet. When I try : > createdb testdb I get the following error; pg_encoding_to_char. I didn't see any errors when I installed. So can someone tell me hoe to correct this? I have version 7.0.3 TIA Wayne
[GENERAL] CGI error
I'm getting this when access the cgi:
The specified CGI application misbehaved by not returning a complete set of
HTTP headers. The headers it did return are:
Here's the cgi code:
int main()
{
printf("Content-Type: %s\n\n", contentType);
conn = PQconnectdb("dbname=template1");
if (PQstatus(conn) == CONNECTION_BAD)
{
printf("Connection to database failed.\n");
printf("%s", PQerrorMessage(conn));
return(1);
}
strcpy(query_string,
"SELECT * FROM cities");
res = PQexec(conn, query_string);
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
printf("SELECT query failed.\n");
PQclear(res);
PQfinish(conn);
return(1);
}
for (i = 0; i < PQntuples(res); i++)
printf("%s\n", PQgetvalue(res, i, 0));
PQclear(res);
PQfinish(conn);
return(0);
}
Re: [GENERAL] International support
> I'm currently working a project that is intended to handle Japanese > character sets - and now I'm told ideally iMode too. :) The iMode isn't > such an issue at the moment - but the article below has spooked me a > little. At an early point in the project we tested if putting some input > into a web form, which ultimately was handled by php then stored in > postgres would return fully intact - and it did. This left me comfortable > that PHP and Postgres don't seem to care what language they're storing in > fields or variables. I'm 'guessing' that this is because the data, whether > its English or Japanese is being stored in binary (or something > else?). No. You are just lucky, I guess. If data submitted by PHP is encoded in EUC, it's ok, since EUC does not conflict with ASCII. However, it is encoded in SJIS, you are going into big problem. The second byte of SJIS *sometimes* conflict with ASCII meta characters such as "\", and this will make the parser of PostgreSQL crazy. Of courese the i18n version of PHP will help (it does the conversion SJIS <--> EUC), but be ware that some characters in SJIS (such as User define characters especially used in i-mode) are not well supported in it. > Of > course I wouldn't be able to sort the data or do anything else that would > require PHP/Postgres to be able to interpret the data. That would depend on how you define "sort". Just doing a normal sort as you are alredy do it with ASCII, you could get more or less resonable results, I guess. But if your client requires more "high level sorts" such as "sorting by YOMIGANA (Japanese pronounciation)" you need to do something... probably you need to define an extract field in your table. > However if I compile > Postgres with locals support for the character set/language in question - > then postgres will be able to sort Japanese. Is this right? No. locale support is useless for Japanese, just slows down PostgreSQL. Turn it off. >Have I got this all right so far? I have attempted to do my research on >this - but finding a real beginners guide to international web development >has been a trick. And the best sources I have found on this topic generally >are specific to Oracle. Any links would be appreciated. Try: ftp://ftp.ora.com/pub/examples/nutshell/ujip/doc/cjk.inf > For the postgres folks, these developers went with MySQL - I've chosen > Postgres. Is there anything MySQL does that Postgres doesn't in terms of > language support that I should be aware of? I believe PostgreSQL's language support is much better than MySQL's especially for Japanese. PostgreSQL can handle both EUC/SJIS on the fly (and even Unicode for 7.1!), and has the ability to do an automatic encoding conversion between them. Moreover, PostgreSQL has many "multibyte aware" functions including regular expression search, which MySQL cannot do, I think. > >PHP's Japanese challenge > >Since r-newbold.com is in Japanese only, Studio Omame made sure to utilize > >PHP's Japanese character set conversion functions. However, this proved to > >be a challenge. > > Is this available for v4 of PHP yet? No. -- Tatsuo Ishii
Re: [GENERAL] Pooling Connections with libpq
* Shaw Terwilliger <[EMAIL PROTECTED]> [010222 15:49] wrote: > Alfred Perlstein wrote: > > As a general safety precaution I would close a connection after a > > timeout or N uses. > > My application is running on the same host as PostgreSQL, so connection > timeouts should be rare (I guess this would only happen if a backend died). > Re-opening the connection after N uses sounds like a reasonable precaution > though. I meant a user defined timeout, ie, a connection can only be recycled 100, 200 or 500 times (your choice) but can't be kept open for longer than 5 minutes. This is just for paranioa such that _if_ (there isn't as far as i know) there's an issue with a backend leaking memory, you don't get nailed by it. > (Hopefully my PGP signatures are correct this time.) PGP isn't really needed when having a general discussion .:) -- -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]]
Re: [GENERAL] Pooling Connections with libpq
Alfred Perlstein wrote: > As a general safety precaution I would close a connection after a > timeout or N uses. My application is running on the same host as PostgreSQL, so connection timeouts should be rare (I guess this would only happen if a backend died). Re-opening the connection after N uses sounds like a reasonable precaution though. (Hopefully my PGP signatures are correct this time.) -- Shaw Terwilliger <[EMAIL PROTECTED]> SourceGear Corporation 217.356.0105 x 641 PGP signature
[GENERAL] Upcoming PostgreSQL events
I wanted to update everyone on upcoming PostgreSQL events. First, the O'Reilly Conference is scheduled for July 23-27, 2001 and will have two days of PostgreSQL presentations. I will announce titles and speakers as soon as the list is finalized. (http://conferences.oreilly.com/oscon2001/) FYI, I have received information from O'Reilly that they will do a much better job in requesting presentations for the next O'Reilly conference. We only received a few weeks notice for this one. Second, there will be some PostgreSQL people at LinuxWorld in San Francisco, August 27 - 30, 2001. (http://www.linuxworldexpo.com/papers.html) Third, another Open Source Database Summit is being scheduled for around September, 2001. A committee is being formed to help plan speakers and topics. I expect PostgreSQL to be well represented. I will announce more information as it becomes available. (http://www.osdn.com/conf/osd/conf_index.shtml) The Open Source Database Summit also said they did not give us enough warning for the 2000 summit so they are going to do better announcing this one. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] Printing PostgreSQL reports
On 22 Feb 2001, at 12:49, Jeff MacDonald wrote: > I think CURSORS would be the correct way to do it.. > > On Tue, 20 Feb 2001, Richard Ehrlich wrote: > > > I can post info to PostgreSQL from a webform via JSP, and I can post > > reports from PostgreSQL to a webpage. Can anyone tell me how I might format > > a PostgreSQL report to a web page so that it will print discrete, sequenced > > pages? > > Here the problem is in that "discrete" keyword, that doesn't fare well at all with HTML. You can't put anything that seems a pagebreak on a HTML, the browser handles the print as it prefers. The best you could do is try to estimate the lenght of the printed page and put a lot of whitespace between a page and the next, but given the variety of browsers, systems, and printers combinations, you should have a lot of luck... If you want to generate "M$ Access-like" reports, divided into pages, you'll have to resort to a different formatting language. For example, you can generate a downloadable .rtf file, or even a .pdf one. It's a lot of work, but the result is guaranteed. /\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ Fabrizio Ermini Alternate E-mail: C.so Umberto, 7 [EMAIL PROTECTED] loc. Meleto Valdarno Mail on GSM: (keep it short!) 52020 Cavriglia (AR) [EMAIL PROTECTED]
[GENERAL] Help with plpgsql - subst variable value
Hi all. This my code snapshot: CREATE FUNCTION CreateObject(varchar(600)) RETURNS int4 AS ' DECLARE . . . ObjectSeqName text; BEGIN . . .. ObjectSeqName := ''ObjectLog'' || ObjectID || ''_Seq''; CREATE SEQUENCE ObjectSeqName start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 CYCLE; . . . END IF; RETURN ObjectID; END; ' LANGUAGE 'plpgsql'; When I run this function I got ERROR: parser: parse error at or near "$1" So how can get value of ObjectSeqName in sequence/table/etc. name?
[GENERAL] Re: Unixware 7.1.1 problem with psql
In article <3a952d04$0$36043$[EMAIL PROTECTED]>, Joel Quinet <[EMAIL PROTECTED]> wrote: >Hi all, > >I try to use Postgres 7.0.3 on SCO Unixware 7.1.1. It compiles fine, but I >have the following error message when a launch psql: >psql: No pg_hba.conf entry for localhost, user postgres, database postgres. >I have made no change to the pg-hba.conf. >I have read in the documentation, it is ok for all operation on local by >default. >I don't have the UDK on that machine, but I seem to have no impact ? Am I >right ? > >I don't understand what I am doing wrong. Any help appreciated. There is a bug in the accept() function on UnixWare and OpenServer. There is a patch in the FAQ_SCO file, or grab the 7.1beta4 tarball. LER > >Joel > > -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Re: [GENERAL] Errors in other languages
Quoting Peter Eisentraut <[EMAIL PROTECTED]>: > Luis Magaña writes: > > > It is possible to display error messages from PostgreSQL in other > languages rather than English ? > > No The JDBC driver does return it's own error messages in French, Dutch and Italian if the VM is in one of those locales. Peter -- Peter Mount [EMAIL PROTECTED] PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/ RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/
Re: [GENERAL] Query with multiples operators BETWEEN
Here they are : EXPLAIN (SELECT e.name FROM Observation o, Exposure_EPIC e WHERE o.numObs = e.obs AND e.instrPN IS NOT NULL AND o.RA BETWEEN 3 AND 5); NOTICE: QUERY PLAN: Hash Join (cost=25.02..60.98 rows=33 width=20) -> Seq Scan on exposure_epic e (cost=0.00..22.50 rows=333 width=16) -> Hash (cost=25.00..25.00 rows=10 width=4) -> Seq Scan on observation o (cost=0.00..25.00 rows=10 width=4) EXPLAIN EXPLAIN (SELECT e.name FROM Observation o, Exposure_EPIC e WHERE o.numObs = e.obs AND e.instrPN IS NOT NULL AND o.DE BETWEEN 2 AND 7); NOTICE: QUERY PLAN: Hash Join (cost=25.02..60.98 rows=33 width=20) -> Seq Scan on exposure_epic e (cost=0.00..22.50 rows=333 width=16) -> Hash (cost=25.00..25.00 rows=10 width=4) -> Seq Scan on observation o (cost=0.00..25.00 rows=10 width=4) EXPLAIN --- EXPLAIN (SELECT e.name FROM Observation o, Exposure_EPIC e WHERE o.numObs = e.obs AND e.instrPN IS NOT NULL AND o.RA BETWEEN 3 AND 5 AND o.DE BETWEEN 2 AND 7); NOTICE: QUERY PLAN: Nested Loop (cost=0.00..56.67 rows=3 width=20) -> Seq Scan on observation o (cost=0.00..30.00 rows=1 width=4) -> Seq Scan on exposure_epic e (cost=0.00..22.50 rows=333 width=16) EXPLAIN -- Table Observation has 5000 rows and 10 columns Table Exposure_Epic has 45000 rows and 6 columns I haven't create any index If use---> >2 and <7 , this is the same result. Thank you for your help Renaud THONNART
Re: [GENERAL] Printing PostgreSQL reports
I think CURSORS would be the correct way to do it.. http://www.postgresql.org/docs/aw_pgsql_book/node142.html Jeff On Tue, 20 Feb 2001, Richard Ehrlich wrote: > I can post info to PostgreSQL from a webform via JSP, and I can post > reports from PostgreSQL to a webpage. Can anyone tell me how I might format > a PostgreSQL report to a web page so that it will print discrete, sequenced > pages? > > Thanks, > Richard Ehrlich > Jeff MacDonald, - PostgreSQL Inc | Hub.Org Networking Services [EMAIL PROTECTED] | [EMAIL PROTECTED] www.pgsql.com | www.hub.org 1-902-542-0713 | 1-902-542-3657 - Facsimile : 1 902 542 5386 IRC Nick : bignose PGP Public Key : http://bignose.hub.org/public.txt
Re: [GENERAL] Errors in other languages
Luis Magaña writes: > It is possible to display error messages from PostgreSQL in other languages rather >than English ? No -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [GENERAL] Query with multiples operators BETWEEN
On Thu, Feb 22, 2001 at 05:10:51PM +0100, Renaud Tthonnart wrote: > This qwery takes about 2 seconds : [..] Can you send us the output of EXPLAIN for each of these queries? Also, how large are the tables you're working with? What indexes have been created? Cheers, Neil -- Neil Conway <[EMAIL PROTECTED]> Get my GnuPG key from: http://klamath.dyndns.org/mykey.asc Encrypted mail welcomed Good composers borrow. Great composers steal. -- Igor Stravinsky
[GENERAL] Query with multiples operators BETWEEN
This qwery takes about 2 seconds : SELECT e.name FROM Observation o, Exposure_EPIC e WHERE o.numObs = e.obs AND e.instrPN IS NOT NULL AND o.RA BETWEEN 3 AND 5; This one about 5 seconds : SELECT e.name FROM Observation o, Exposure_EPIC e WHERE o.numObs = e.obs AND e.instrPN IS NOT NULL AND o.DE BETWEEN 2 AND 7; And this takes a lot of time (I can't say how much because I 've always given it up before its end, at least more than 10 minutes) SELECT e.name FROM Observation o, Exposure_EPIC e WHERE o.numObs = e.obs AND e.instrPN IS NOT NULL AND o.RA BETWEEN 3 AND 5 AND o.DE BETWEEN 2 AND 7; Could someone help or explain me? Thanks in advance and excuse my bad English Renaud THONNART
[GENERAL] iterating over all NEW.* values in a trigger?
Inside a plpgsql function trigger, is it possible to a loop over all fields of the NEW record (and inspect their value) without knowing in advance from which table NEW will come? I am trying the following: DROP FUNCTION arch_func(); CREATE FUNCTION arch_func() RETURNS opaque AS ' DECLARE rec ecord; BEGIN FOR rec IN SELECT a.attname,t.typname FROM pg_attribute a JOIN pg_class c ON (a.attrelid = c.oid AND c.relname = TG_RELNAME) JOIN pg_type t ON (t.oid = a.atttypid) LOOP RAISE NOTICE ''Hello %: %!'', rec.attname, rec.typname; -- -- here I would like to access NEW.rec.attname in order to be able -- to: -- 1) compare it to OLD.rec.attname, -- 2) check if there is a change, -- 3) save a changed value in another "archive" table -- END LOOP; RETURN NULL; END; ' LANGUAGE 'plpgsql'; DROP TRIGGER arch_after ON auction; CREATE TRIGGER arch_after AFTER UPDATE OR DELETE ON auction FOR each ROW EXECUTE PROCEDURE arch_func(); -- PHEDRE: Dans mes jaloux transports je le veux implorer. Que fais-je ? Où ma raison va-t-elle s'égarer ? (Phèdre, J-B Racine, acte 4, scène 6)
[GENERAL] Re: Location of 7.1 version for download
ftp://ftp.postgresql.org/pub/dev/ all of the 7.1 files are there though they are still in beta form. Mike - Original Message - From: Paulo Parola To: [EMAIL PROTECTED] Sent: Thursday, February 22, 2001 10:04 AM Subject: Location of 7.1 version for download Where can I download PostgreSQL v7.1? Please forward me to the URL where I can download it. Is there already binaries or do I have to compile it? TIA, Paulo
[GENERAL] Location of 7.1 version for download
Where can I download PostgreSQL v7.1? Please forward me to the URL where I can download it. Is there already binaries or do I have to compile it? TIA, Paulo
[GENERAL] Re: inconstistent inheritance in 7.1?
On Thu, Feb 22, 2001 at 02:45:46PM +0100, Louis-David Mitterrand wrote: > Why does a SELECT apply to all tables, including inherited ones and not > UPDATEs or DELETEs? Is there a way to UPDATE a whole table hierarchy in > one fell swoop? Oops, I take that back; indeed it UPDATEs and DELETEs apply to inherited tables, however triggers don't. -- OENONE: De quel droit sur vous-même osez-vous attenter ? Vous offensez les Dieux auteurs de votre vie ; (Phèdre, J-B Racine, acte 1, scène 3)
[GENERAL] inconstistent inheritance in 7.1?
Why does a SELECT apply to all tables, including inherited ones and not UPDATEs or DELETEs? Is there a way to UPDATE a whole table hierarchy in one fell swoop? TIA -- HIPPOLYTE: Donnerai-je l'exemple à la témérité ? Et dans un fol amour ma jeunesse embarquée... (Phèdre, J-B Racine, acte 1, scène 1)
[GENERAL] Errors in other languages
Hello: It is possible to display error messages from PostgreSQL in other languages rather than English ?, if it is possible, how can achieve that. I love this RDBMS but this seems to be a problem for us in using it with our customers. Any help or answer will be aprecciated. Thank yo in advanced. Sincerely. -- Ing. Luis Magaña. Gnovus Networks & Software www.gnovus.com
Re: [GENERAL] problem while compiling user c functions in 7.1beta4
On Thu, Feb 22, 2001 at 11:24:48PM +1300, Christopher Sawtell wrote: > Very strange indeed. > I have found that using cvsup is a very reliable way to keep the code > in order. In my experience postgresql is of ultra-superior quality and > everything just makes "out of the box". i had the same problems in past too. i'm usings cvs -z9 update, and ... everything builds great. works out of the box, just some files in include directory are missing. depesz -- hubert depesz lubaczewski http://www.depesz.pl/ najwspanialszą rzeczą jaką dało nam nowoczesne społeczeństwo, jest niesamowita wręcz łatwość unikania kontaktów z nim ...
Re: [GENERAL] VACUUM LAZY compilation problem
* Guillaume Lémery <[EMAIL PROTECTED]> [010222 04:01] wrote: > Hi, > > I'm trying to compile postgresql with the VACUUM LAZY patch, > but I have an error with yacc : > > /usr/bin/yacc: f - maximum table size exceeded > > Where should I look to fix this ? Install Bison and re-run "configure" -- -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]]
[GENERAL] VACUUM LAZY compilation problem
Hi, I'm trying to compile postgresql with the VACUUM LAZY patch, but I have an error with yacc : /usr/bin/yacc: f - maximum table size exceeded Where should I look to fix this ? Thanx. Guillaume.
[GENERAL] Column into array.
Greetings, I have a small table with serial numbers in it. I wish to get a selection ( approx 30 ) of those numbers into an array in another table. Is this possible in SQL? -- Sincerely etc., NAME Christopher Sawtell CELL PHONE 021 257 4451 ICQ UIN45863470 EMAIL csawtell @ xtra . co . nz CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz -->> Please refrain from using HTML or WORD attachments in e-mails to me <<--
Re: [GENERAL] Weird indices
From: "Tom Lane" <[EMAIL PROTECTED]> > Martijn van Oosterhout <[EMAIL PROTECTED]> writes: > > We have a table with over 1 million rows and the statistics Postgres gathers > > are not particularly useful. There is not one (non-null) value that occurs > > significantly more often than other values but the distribution looks a lot > > like a 1/x curve I guess. The most common value occurs 5249 times but the > > average is only 95, so Postgres chooses seq scan almost always. We actually > > now set enable_seqscan=off in many areas of our code to speed it up to a > > useful rate. (This table also happens to have an (accedental) clustering on > > this column also). > > > What is the reasoning behind estimating like that? Why not just the average > > or the average + 1 SD? > > Can you think of a reasonable algorithm for VACUUM to obtain the true > average frequency? It has a tough enough time estimating the most > common frequency with any reliability. Given complaints in nearby > threads that VACUUM ANALYZE is too slow, it'd be a good idea if your > algorithm was faster than the current one, too ;-) I'm don't see that there's any way that you're going to get an analyser that _always_ gets it right. Might there not be some way of explicitly telling the analyser the distribution of the data. Like Martijn says above, he thinks the distribution is something like 1/x. In the cases where you really care you probably do know what sort of values are stored. I have to admit my maths isn't good enough to say how sensible an idea this is, but figured I'd put my tuppence-worth in. - Richard Huxton
Re: [GENERAL] problem while compiling user c functions in 7.1beta4
On Thu, 22 Feb 2001 22:39, hubert depesz lubaczewski wrote: > On Thu, Feb 22, 2001 at 10:21:12PM +1300, Christopher Sawtell wrote: > > I did a cvsup update about 12 hours ago and look:- > > 22:05:23 chris@berty:/usr/src/cvs/pgsql $ find . -name postgres.h > > ./src/include/postgres.h > > 22:16:22 chris@berty:/usr/src/cvs/pgsql $ > > sorry. my fault. i was wrong because the files were not installed in > working directory. strange. error in makefile's? Very strange indeed. I have found that using cvsup is a very reliable way to keep the code in order. In my experience postgresql is of ultra-superior quality and everything just makes "out of the box". -- Sincerely etc., NAME Christopher Sawtell CELL PHONE 021 257 4451 ICQ UIN45863470 EMAIL csawtell @ xtra . co . nz CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz -->> Please refrain from using HTML or WORD attachments in e-mails to me <<--
[GENERAL] Restore problems with 7.0.3 on Solaris/sparc
Hello: I'm having problems with restoring database. I'm using PostgreSQL 7.0.3 on Solaris 2.6 on a Sparc machine. The database contains large tables, the largest contains 1,5 million rows. While pg_dump is quite quick, restore takes ages. Situation improves if I split the dump file into smaller chunks, so no COPY command has more than 1 lines of data following. Then restore is faster, it takes "only" about 4 hours. I also tried to restore the database on another machine (Pentium II) with RedHat 7.0, and it took only about 5 minutes - without splitting the file into smaller chunks. Any ideas where the problem might be? Thanks in advance, Ales Pecnik
Re: [GENERAL] problem while compiling user c functions in 7.1beta4
On Thu, Feb 22, 2001 at 10:21:12PM +1300, Christopher Sawtell wrote: > I did a cvsup update about 12 hours ago and look:- > 22:05:23 chris@berty:/usr/src/cvs/pgsql $ find . -name postgres.h > ./src/include/postgres.h > 22:16:22 chris@berty:/usr/src/cvs/pgsql $ sorry. my fault. i was wrong because the files were not installed in working directory. strange. error in makefile's? depesz -- hubert depesz lubaczewski http://www.depesz.pl/ najwspanialszą rzeczą jaką dało nam nowoczesne społeczeństwo, jest niesamowita wręcz łatwość unikania kontaktów z nim ...
Re: [GENERAL] sequence and stored procedure
Stephan Szabo wrote:
> On Wed, 21 Feb 2001, Renaud Tthonnart wrote:
>
> > How can I use sequences in a stored procedure written with pl/pgsql?
>
> What exactly are you trying to do? Pretty much you can call the
> sequence functions you want to use on the sequence name you
> want (you have to double the quotes around the name since it's
> inside the quoted text of the function.
>
> create function foo() returns opaque as
> '
> begin
> NEW.y:=nextval(''seqseq'');
> return NEW;
> end;' language 'plpgsql';
>
> create trigger footrig before insert or update on testfoo for
> each row execute procedure foo();
Thank for your answer! That will help me !
Renaud THONNART
[GENERAL] problem while compiling user c functions in 7.1beta4
hi, i have functin which did compile on 7.0.3 and 7.1beta1, and now it doesn't. it includes were: #include #include #include #include since in 7.1beta4 there is no postgres.h i changed this to: #include #include #include #include all i need this for is to have type declarations, and postgresql versions of malloc, realloc and free - i.e. i dont use spi. when compiling i get this errors: gcc -O2 -Wall -ansi -I "/home/users/pgdba/work/include/postgresql/" -c dfti.c -fpic In file included from dfti.c:4: /home/users/pgdba/work/include/postgresql/c.h:312: parse error before `regproc' /home/users/pgdba/work/include/postgresql/c.h:312: warning: type defaults to `int' in declaration of `regproc' /home/users/pgdba/work/include/postgresql/c.h:312: warning: data definition has no type or storage class /home/users/pgdba/work/include/postgresql/c.h:313: parse error before `RegProcedure' /home/users/pgdba/work/include/postgresql/c.h:313: warning: type defaults to `int' in declaration of `RegProcedure' /home/users/pgdba/work/include/postgresql/c.h:313: warning: data definition has no type or storage class /home/users/pgdba/work/include/postgresql/c.h:364: parse error before `oidvector' /home/users/pgdba/work/include/postgresql/c.h:364: warning: type defaults to `int' in declaration of `oidvector' /home/users/pgdba/work/include/postgresql/c.h:364: warning: data definition has no type or storage class /home/users/pgdba/work/include/postgresql/c.h:373: `NAMEDATALEN' undeclared here (not in a function) dfti.c: In function `empty_text': dfti.c:16: warning: implicit declaration of function `palloc' dfti.c: In function `dfti_prepare': dfti.c:37: warning: implicit declaration of function `elog' dfti.c:37: `ERROR' undeclared (first use in this function) dfti.c:37: (Each undeclared identifier is reported only once dfti.c:37: for each function it appears in.) dfti.c:84: warning: implicit declaration of function `repalloc' dfti.c:86: warning: implicit declaration of function `pfree' my knowledge of c is extremly limited, so i can't work on those errors. my postgresql is build from cvs snapshot taken 21th of february 11:23. can anyone help me with this? depesz p.s. i'm not posting the whole function code since it's ugly, long and it *did* work (without warnings) with previous persions. -- hubert depesz lubaczewski http://www.depesz.pl/ najwspanialszą rzeczą jaką dało nam nowoczesne społeczeństwo, jest niesamowita wręcz łatwość unikania kontaktów z nim ...
