[GENERAL] Solution found! was Re: Finding the number of rows affected by UPDATE or INSERT?
I found out how to do it: In pl/pgsql: INT nr; UPDATE GET DIAGNOSTICS SELECT PROCESSED INTO nr; will put the number of rows into nr. It's a very useful feature to have this in PG7.1. ---(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] Default encoding in database
How does the output from psql -l look like? List of databases Database | Owner | Encoding +--+--- template0 | postgres | SQL_ASCII template1 | postgres | SQL_ASCII Jacob I don't know why your template1 DB's encoding is SQL_ASCII, but you need to change the encoding of the DB anyway. try: psql -c update pg_database set encoding = 7 where datname = 'template1' template1 -- Tatsuo Ishii ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] tuning
Hi, Sorry for my last empty message. I have a pgsql database containing DHCP lease informtation,one table for each month. As we get more and more customers there will be more rows in the tables. The table for august now contains about 2.7 million rows. There is about 5 inserts per second. Table definition: start | boolean ts| timestamp mac | macaddr port | character varying(50) ip| inet There are indexes on ts,mac,port and ip. Database is running under Linux 2.2 on a Compaq with a P3 800 and 256MB memory. Now for the problem :) I get what I think is very poor response times,about 90-140s to get a result when searching on ip. The only options I have set in postgresql.conf is: wal_files = 8 wal_sync_method = fdatasync What can I do to tune this database besides memory upgrade and adding a additional CPU? Kind regards, Daniel Lundqvist ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: Fw: [JDBC] Regarding vacuumdb
Shanmugasundaram Doraisamy wrote: - Original Message - From: Peter Eisentraut [EMAIL PROTECTED] To: Shanmugasundaram [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, August 29, 2001 5:11 PM Subject: Re: [JDBC] Regarding vacuumdb Shanmugasundaram writes: I am getting the following error while doing vacuumdb, ERROR: mdopen: couldn't open test1: No such file or directory vacuumdb: database vacuum failed on db1. Here 'db1' is the database and 'test1' is a table. When, displaying the structure of the table 'test1', it comes correctly. But I can't drop the table. What could be wrong? You probably rolled back a DROP TABLE operation, so the underlying table file on disk is gone but the catalog structures have been preserved. You could probably try touch $PGDATA/base/db1/test1 and then drop the table. Consider upgrading to the latest stable release (7.1.3) when you get a chance to eliminate this class of problems. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter I tried with the command you gave. But, if I try to delete the table 'test1' it comes with the following error: ERROR: mdopen: couldn't open test1: Permission denied How to solve it?Any help. shan ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] weird behaviour
hi, i'm notice a weird behaviour while running a query on the same table, the query take a long time to be executed when this table is empty , and when this table isn't empty, it take about 1/2 second the two tables are freshly vacuumed ... here the same tables, one is empty, not the other : Table classement_jeu Attribute | Type | Modifier ---+--+-- semaine | smallint | annee | integer | id_turf | integer | points| integer | tag | smallint | Table classement_jeumat Attribute | Type | Modifier ---+--+-- semaine | smallint | annee | integer | id_turf | integer | points| integer | tag | smallint | psql -c select count(*) from classement_jeu count --- 21318 (1 row) psql select count(*) from classement_jeumat count --- 0 (1 row) now the query i want to run : SELECT id_turf FROM classement_jeumat WHERE semaine = 35 AND annee = 2001 AND id_turf IN (5247,5425, ) the IN clause contain about 600 entries run the query on table classement_jeumat (empty table) time psql laquery id_turf - (0 rows) real1m18.308s user0m0.000s sys 0m0.015s run the query on table classement_jeu (not empty table) time psql laquery id_turf - [... snip results ... ] (592 rows) real0m0.472s user0m0.009s sys 0m0.014s somebody understand what happened ? it's not really important because i will not work on an empty table ... but i'm surprised ... thanxs mat ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] weird behaviour
sorry, i forgot some version number :) [mat@biniac ~]$ psql --version psql (PostgreSQL) 7.1.2 contains readline, history support Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group Portions Copyright (c) 1996 Regents of the University of California Read the file COPYRIGHT or use the command \copyright to see the usage and distribution terms. [mat@biniac ~]$uname -rs FreeBSD 4.3-RELEASE[mat@biniac ~]$ mat ---(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] sub select
Can I: select *, sum_rows as ( select count(*) from table2; ) from table1; -- Canaan Surfing Ltd. Internet Service Providers Ben-Nes Michael - Manager Tel: 972-4-6991122 http://sites.canaan.co.il -- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] [Revoke] cannot revoke the permission of some User....
Hello all,
when i use the "\dp" to view the permission
of "mytable":
mytable |
{"=","28=arw","34=arw","36=arw","40=arw","42=arw","cgi=arw"}
i guess those numeric thingy is kinda
object id of a user, and i've deleted
the user b4 i revoke the permission of that
user from "mytable".
is it possible for me to remove those 28,
34 36?
thanks in advance.
---
matthew
Re: [GENERAL] sub select
On Thu, 30 Aug 2001, Ben-Nes Michael wrote: Can I: select *, sum_rows as ( select count(*) from table2; ) from table1; I think you need to say: select *, (select count(*) from table2) as sum_rows from table1; But this won't be a very interesting query because the sum_rows column will be the same in every row. Where subselects get interesting is when you do a correlated subquery like this: select *, (select count(*) from table2 where table2.x = table1.y) as sum_rows from table1; -- Tod McQuillin ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] MySQL treads belong else where.
Guy Fraser wrote: Hi I would appreciate it if the MySQL zealots with troglodytical mentalities would confine themselves to there own mailing list. I've yet to see any MYsql zealots on this list. The odd comparison is OK but the flame wars are a waste of storage. MySQL and PostgreSQL both have loyal followers for there own reasons. Both of these open source database engines have pros and cons. Each database project has it's own unique set of requirements which are more suitable to one or the other, but could probably be don in either. Marketing a database by claiming your features are better and the feature of the other are useless is immature. No it's not useless - if you don't offer alternative view (like MYsql's information on foreign keys) people do believe everything they read. I use both, but I look to this list for PostgreSQL questions and answers. The bantering of MySQL zealots does not help anyone on this list with PostgreSQL questions or answers. Detailed and open discussion of MYsql vs Postgresql does belong on the general list. A lot of people do believe everything MYsql says (like I used to) like to get both sides of the story and then be able to pass the information to others. It helps in making decisions on which database to use. So far I haven't seen any real flames or insults thrown about yet - no MYsql will kill your puppy threads at all. There can be rigourous discussion without insults being thrown and so far that's all I've seen. Guy Fraser PS If you don't understand any of these words use a dictionary to find out what they mean. Don't just presume they are insults. -- There is a fine line between genius and lunacy, fear not, walk the line with pride. Not all things will end up as you wanted, but you will certainly discover things the meek and timid will miss out on. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(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: Default encoding in database
On Thu, Aug 30, 2001 at 05:28:16PM +0900, Tatsuo Ishii wrote: How does the output from psql -l look like? List of databases Database | Owner | Encoding +--+--- template0 | postgres | SQL_ASCII template1 | postgres | SQL_ASCII Jacob I don't know why your template1 DB's encoding is SQL_ASCII, but you need to change the encoding of the DB anyway. try: psql -c update pg_database set encoding = 7 where datname = 'template1' template1 IMHO more careful is re-build it by initdb with option -E LATIN1. KArel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Re: Default encoding in database
On Thu, Aug 30, 2001 at 11:04:39AM +0200, Jacob Vennervald Madsen wrote: If I do rebuild it with initdb will I still have all my data? No of course, initdb create everything again... but if you have only templates DB you probably haven't some data, else you must dump all your DB and backup it and rebuild templates. List of databases Database | Owner | Encoding +--+--- template0 | postgres | SQL_ASCII template1 | postgres | SQL_ASCII Jacob BTW, I don't understand why you don't create new databases by CREATE DATABASE bar WITH ENCODING = 'foo' or by createdb -E. Karel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
RE: [GENERAL] MySQL treads belong else where.
So far I haven't seen any real flames or insults thrown about yet - no MYsql will kill your puppy threads at all. There can be rigourous discussion without insults being thrown and so far that's all I've seen. No, but it WILL kill your data, if the DB is big enough and has been in use long enough.. speaking from personal experience *weep* ;-) I used to think mysql was great while I was learning SQL, because it was free, unbloated, and easy to use. But I think one grows out of it for larger and more complex projects... Postgres' larger set of features and sophisticated locking become invaluable. I know (from reading this list) that people sometimes have trouble with PGSQL, but quite honestly I found that after I had read the Postgresql docs in their entirety, everything fit and seemed intuitive. (I found it not unlike the feeling of becoming a FreeBSD user after having used linux...) For the project as a whole I guess its a matter of killing off the genuine FUD with one hand, and fixing the areas in Postgres that MySQL does have genuinely have an advantage, with the other hand. - Andrew ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Finding the number of rows affected by UPDATE or INSERT?
I am writing a bunch of stuff in pl/pgsql, and some of these functions do UPDATEs or INSERTs on various tables. How could I find out, from within the pl/pgsql function, how many rows were affected? This way, if the update or insert fails for some reason, I can return a failed message from the function, instead of pretending that everything is ok. I think you want GET DIAGNOSTICS. See http://www.postgresql.org/idocs/index.php?plpgsql-description.html Here's the relevant section: 24.2.5.4. Obtaining other results status GET DIAGNOSTICS variable = item [ , ... ] This command allows retrieval of system status indicators. Each item is a keyword identifying a state value to be assigned to the specified variable (which should be of the right datatype to receive it). The currently available status items are ROW_COUNT, the number of rows processed by the last SQL query sent down to the SQL engine; and RESULT_OID, the Oid of the last row inserted by the most recent SQL query. Note that RESULT_OID is only useful after an INSERT query. HTH, -- Joe ---(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[2]: [GENERAL] RFC: PostgreSQL and MySQL comparison.
Greetings, Peter! At 29.08.2001, 23:32, you wrote: But I do think that the statements in http://www.mysql.com/doc/M/y/MySQL-PostgreSQL_features.html should NOT go unanswered. PE Okay, I answered them: PE http://webmail.postgresql.org/~petere/comparison.html Looks good, but it's not exactly what I've had in mind. You see it is too often considered that if someone has to justify oneself, he is automatically guilty. I want to write an article that would make MySQL's developers justify themselves... Besides, one has to read the MySQL's article *before* this just to understand what's this all about... BTW, can I use parts of your answers in my work? And, a question to SQL standard gurus: is it necessary for a DBMS to implement subqueries and views to be considered entry-level SQL92 compliant. Besides, is there any document which has all PostgreSQL's deviations from standard in one place? PE I tried to be reasonable and biased at the same time. ;-) PE This article does not go into the advantages of PostgreSQL, since that's PE already done elsewhere, such as here: PE http://www.ca.postgresql.org/features.html -- Yours, Alexey V. Borzov, Webmaster of RDW.ru ---(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] pg7.1 release date
Greetings, pgsql-general! While researching material for a certain upcoming article, I found out that online docs still claim 2001-??-?? as a release date for PostgreSQL 7.1 -- Yours, Alexey V. Borzov, Webmaster of RDW.ru ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] url to free text search stategies in postgresql?
* Matthew Kennedy [EMAIL PROTECTED] wrote: | | There was a brief mention in the RFC: PostgreSQL and MySQL comparison | thread about free text search in postgresql. Is there any information | avaliable on this? Nothing came up when I searched the interactive Check the openfts addon for postgresql at : http://openfts.sourceforge.net/ You can also test it in action by searching the PostgreSQL mailing lists at : http://fts.postgresql.org/db/mw/ cheers, Gunnar -- Gunnar Rønning - [EMAIL PROTECTED] Senior Consultant, Polygnosis AS, http://www.polygnosis.com/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] RE: Default encoding in database
If I do rebuild it with initdb will I still have all my data? Jacob -Original Message- From: Karel Zak [mailto:[EMAIL PROTECTED]] Sent: 30. august 2001 11:03 To: Tatsuo Ishii Cc: Jacob Vennervald Madsen; [EMAIL PROTECTED] Subject: Re: Default encoding in database On Thu, Aug 30, 2001 at 05:28:16PM +0900, Tatsuo Ishii wrote: How does the output from psql -l look like? List of databases Database | Owner | Encoding +--+--- template0 | postgres | SQL_ASCII template1 | postgres | SQL_ASCII Jacob I don't know why your template1 DB's encoding is SQL_ASCII, but you need to change the encoding of the DB anyway. try: psql -c update pg_database set encoding = 7 where datname = 'template1' template1 IMHO more careful is re-build it by initdb with option -E LATIN1. KArel -- Karel Zak [EMAIL PROTECTED] http://home.zf.jcu.cz/~zakkr/ C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] --enable-syslog and Solaris 7
Hi, I'm having a strange problem with syslog and Solaris 7, and I've run out of ideas about what might be wrong. I configure postgres with --enable-syslog, and everything works fine. Configure finds syslog, so that's not a problem. In postgres.conf, I have the following: # # Syslog # #ifdef ENABLE_SYSLOG syslog = 2 # range 0-2 syslog_facility = 'LOCAL1' syslog_ident = 'postgres' #endif And, in /etc/syslog.conf, I have #Postgres logging local1.*/opt/OXRS/logs/postgres/pg.log (yes, the whitespace is tabs). Now, the strange thing is that nothing ever seems to go to the syslog daemon. If I start up syslogd with -d, it never shows anything coming from postgres, even though I have logging levels turned up. If I redirect the log to both console and logfile, I get lots of noise on the console, bit nothing in the file, so I know it's not from want of data. Any suggestion is much appreciated, because I'm stumped. I've never seen anything like this before. Thanks, A -- Andrew Sullivan 87 Mowat Avenue Liberty RMS Toronto, Ontario Canada [EMAIL PROTECTED] M6K 3E3 +1 416 646 3304 x110 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
RE: [GENERAL] RFC: PostgreSQL and MySQL comparison.
On Wed, 29 Aug 2001, Robert J. Sanford, Jr. wrote: http://www.phpbuilder.com/columns/tim20001112.php3 Now *that* was very informative, thank you. The best benefit to this, is that the optimization engine is supposedly vastly improved in the 7.2 tree, so that'll just increase the lead. If they clean up vacuum to actually get indexes, the planner will have a better chance at picking more optimal execution plans, too. I'm glad that development has picked up. It seemed like 6.5x would be around forever. Thanks for knocking down the walls guys. ^_^ -- +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ | Shaun M. ThomasINN Database Programmer | | Phone: (309) 743-0812 Fax : (309) 743-0830| | Email: [EMAIL PROTECTED]AIM : trifthen | | Web : hamster.lee.net | | | | Most of our lives are about proving something, either to | | ourselves or to someone else. | | -- Anonymous | +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] MySQL treads belong else where.
On Wed, 29 Aug 2001, Guy Fraser wrote: I would appreciate it if the MySQL zealots with troglodytical mentalities would confine themselves to there own mailing list. Er? Man, everybody just has to chime in with their $0.02. Let the moderators handle it, oh flaming sword o' justice. The odd comparison is OK but the flame wars are a waste of storage. Which, while an amusing read, also point out actual flaws in Postgres that we could stand fixing. People do bitch without a reason, but the do it more often when they have something to bitch about. Me, I was just being devil's advocate. Looks like not everyone caught that. Oh well. PS If you don't understand any of these words use a dictionary to find out what they mean. Don't just presume they are insults. Now, now. You had me going for a while, but you have just instantly turned what may have been a mildly informative post into a pathetic flame. I mean, really. What was that about troglodytical mentalities? For the most part, I agree with you. For everyone else who doesn't get it. Upgrade. Upgrade now, upgrade quickly, and upgrade until you can't upgrade anymore. Postgres 7.1 is not the postgres of yesteryear. Postgres has evolved past the horrid thing MySQL compared itself to. The best part is that 7.2 will be even better. The point here is to know postgres's flaws. Ignore the flames, and upgrade. The more people using the newer versions, the better the next one will be. C'mon, kiss a developer today! -- +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ | Shaun M. ThomasINN Database Programmer | | Phone: (309) 743-0812 Fax : (309) 743-0830| | Email: [EMAIL PROTECTED]AIM : trifthen | | Web : hamster.lee.net | | | | Most of our lives are about proving something, either to | | ourselves or to someone else. | | -- Anonymous | +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] tuning
On Thu, 30 Aug 2001, Daniel Lundqvist wrote: I have a pgsql database containing DHCP lease informtation,one table for each month. As we get more and more customers there will be more rows in the tables. The table for august now contains about 2.7 million rows. There is about 5 inserts per second. Table definition: start | boolean ts| timestamp mac | macaddr port | character varying(50) ip| inet There are indexes on ts,mac,port and ip. Database is running under Linux 2.2 on a Compaq with a P3 800 and 256MB memory. Now for the problem :) I get what I think is very poor response times,about 90-140s to get a result when searching on ip. The only options I have set in postgresql.conf is: wal_files = 8 wal_sync_method = fdatasync What can I do to tune this database besides memory upgrade and adding a additional CPU? Have you been running vacuum analyze? What are the queries you're running and what does explain show for them? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] glacial deletes (long)
Looking for some hints on how to speed up deletes Thanks in advance
Using PostgreSQL 7.1.2 on hppa2.0-hp-hpux10.20, compiled by GCC 2.95.3
(2 processors)
Have a file record wx_grib_file, with data stored as large object.
The rule wx_grib_file_delete does the lo_unlink on grib_file_id.
Table wx_grib_file
Attribute | Type| Modifier
--+---+--
grib_file_id | oid | not null
name | char(40) |
parse_time | timestamp |
Index: wx_grib_file_pkey
Rule: wx_grib_file_delete
Triggers: RI_ConstraintTrigger_180325,
RI_ConstraintTrigger_180327
Have a catalog entry which uses the wx_grib_file key as a foreign key.
For referential integrity, the catalog entry must be deleted before the
file.
For refTable wx_grib_catalog
Attribute | Type| Modifier
---+---+--
edition | integer | not null
discipline| integer | not null
generating_center | integer | not null
sub_center| integer | not null
scale_factor | integer |
grib_product_id | integer | not null
prod_category | integer |
grib_model_id | integer | not null
run_time | timestamp | not null
fcst_time | integer | not null
grib_region_id| integer | not null
level | integer | not null
level_units | char(8) | not null
projection| char(16) | not null
bmp_usage | boolean | not null
wx_usage | boolean | not null
gds_usage | boolean | not null
grib_file_id | oid |
gds_offset| integer |
pds_offset| integer | not null
drs_offset| integer |
ds_offset | integer | not null
bms_offset| integer |
Index: wx_grib_catalog_pkey
Constraints: ((prod_category = 0) AND (prod_category = 19))
(fcst_time = 0)
(gds_offset = 0)
(pds_offset = 0)
(drs_offset = 0)
(ds_offset = 0)
(bms_offset = 0)
((edition = 1) OR (edition = 2))
((generating_center = 7) AND (generating_center = 99))
Inserts to these (including the lo_import) are very fast (5 to 10
sets per second), but deletes are very slow (1 or 2 secs per item).
Here are the plans:
explain verbose delete from wx_grib_catalog where grib_file_id = 3347194;
NOTICE: QUERY DUMP:
{ SEQSCAN :startup_cost 0.00 :total_cost 2787.20 :rows 1 :width 6
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 27
:restypmod -1 :resname ctid :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk true } :expr { VAR :varno 1 :varattno -1 :vartype 27 :vartypmod
-1 :varlevelsup 0 :varnoold 1 :varoattno -1}}) :qpqual ({ EXPR :typeOid
16 :opType op :oper { OPER :opno 1137 :opid 184 :opresulttype 16 }
:args ({ VAR :varno 1 :varattno 18 :vartype 26 :vartypmod -1
:varlevelsup 0 :varnoold 1 :varoattno 18} { CONST :consttype 23
:constlen 4 :constbyval true :constisnull false :constvalue 4 [ 0 51 18
-6 ] })}) :lefttree :righttree :extprm () :locprm () :initplan
:nprm 0 :scanrelid 1 }
NOTICE: QUERY PLAN:
explain verbose delete from wx_grib_file where grib_file_id = 3347194;
NOTICE: QUERY DUMP:
{ INDEXSCAN :startup_cost 0.00 :total_cost 3.10 :rows 1 :width 4
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23
:restypmod -1 :resname lo_unlink :reskey 0 :reskeyop 0 :ressortgroupref
0 :resjunk false } :expr { EXPR :typeOid 23 :opType func :oper { FUNC
:funcid 964 :functype 23 } :args ({ VAR :varno 1 :varattno 1 :vartype 26
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1})}}) :qpqual
:lefttree :righttree :extprm () :locprm () :initplan :nprm 0
:scanrelid 1 :indxid ( 180258) :indxqual (({ EXPR :typeOid 16 :opType
op :oper { OPER :opno 607 :opid 184 :opresulttype 16 } :args ({ VAR
:varno 1 :varattno 1 :vartype 26 :vartypmod -1 :varlevelsup 0 :varnoold
1 :varoattno 1} { CONST :consttype 23 :constlen 4 :constbyval true
:constisnull false :constvalue 4 [ 0 51 18 -6 ] })})) :indxqualorig (({
EXPR :typeOid 16 :opType op :oper { OPER :opno 1137 :opid 184
:opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 26
:vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1} { CONST
:consttype 23 :constlen 4 :constbyval true :constisnull false
:constvalue 4 [ 0 51 18 -6 ] })})) :indxorderdir 1 }
NOTICE: QUERY PLAN:
Index Scan using wx_grib_file_pkey on wx_grib_file (cost=0.00..3.10
rows=1 width=4)
NOTICE: QUERY DUMP:
{ NESTLOOP :startup_cost 0.00 :total_cost 3698.46 :rows 1 :width 14
:qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 27
:restypmod -1 :resname ctid :reskey 0 :reskeyop 0 :ressortgroupref 0
:resjunk true } :expr { VAR :varno 65000 :varattno 1 :vartype 27
:vartypmod -1 :varlevelsup 0 :varnoold 6 :varoattno -1}}) :qpqual
:lefttree { INDEXSCAN :startup_cost 0.00 :total_cost 3.10 :rows 1
Re: [GENERAL] Re: Fw: [JDBC] Regarding vacuumdb
I tried with the command you gave. But, if I try to delete the table 'test1' it comes with the following error: ERROR: mdopen: couldn't open test1: Permission denied How to solve it?Any help. Make sure the file is owned and writable by the user that the database runs as. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] mx is needed by postgresql-python-7.1.3-1PGDG
Hi I had to rpm --rebuild postgresql-7.1.3-1PGDG.src.rpm on a RedHat 6.2 machine. After I rebuilt the software I attempted to install all the compiled packages but got the error message : error: failed dependencies: mx is needed by postgresql-python-7.1.3-1PGDG This /root/.cpan/build/Net-DNS-0.12/demo/mx is the only mx I could find on my system. Being a simple program name, I have no idea where to get the required package. If the file is supposed to be a program to find DNS MX RR's then this program may be OK. Below is taken from the output from the build process. Begin output from build Requires: python = 1.5 mx ld-linux.so.2 libcrypt.so.1 libc.so.6 libpq.so.2 /usr/bin/env libc.so.6(GLIBC_2.0) Conflicts: python = 1.6 End Can anyone tell me what it is and where I can find it? Guy -- There is a fine line between genius and lunacy, fear not, walk the line with pride. Not all things will end up as you wanted, but you will certainly discover things the meek and timid will miss out on. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Appology : MySQL threads belong else where.
Sorry if I insulted the wrong people. Like I said in the message, The odd comparison is OK was poorly stated, I was tired of having to go through all the posts from people who appear to be close to anti PostgreSQL. I was trying to indicate that suggesting feature enhancements that are SQL92 and in other databases are appropriate. I am not saying in any way that MySQL's features are bad, but I would rather see PostgreSQL specification compliant than a MySQL knock off. Of course the management programs may not be required to meet any specifications. I the suggestions are aimed at making the management system better suggestions should be welcome. I am not against discussing comparisons between PostgreSQL and other RDBMS systems. Discussions are often a good way of sowing the seeds of ideas for the developers. When discussions appear to become arguments it seems appropriate that somebody steps in to calm down the thread. Again, I am sorry for being harsh, I was upset, but that is no excuse. Guy PS: I hope this thread can stop soon. -- There is a fine line between genius and lunacy, fear not, walk the line with pride. Not all things will end up as you wanted, but you will certainly discover things the meek and timid will miss out on. ---(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: A903-35A9-76AF : CONFIRM from pgsql-general (subscribe)
accept A903-35A9-76AF ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Re: quick question: index optimisations on small tables
On Fri, 31 Aug 2001, Andrew Snow wrote: Hrmm... I have 26 rows in mine at the moment, and after vacuum analyzing, it uses a seq. scan. How come yours used the index? I thought mine wasn't using an index because postgres won't use an index until the table is big enough. But if an index page is already in cache.. surely it'd be faster using it than doing a seq. scan. (Yes, I know its a small table, but I think the worst case for seq. scan would be a fair bit worse than for the index, and every little bit counts, right?) If the table is small enough to fit in one page, a sequence scan across those rows may be faster than the index scan since the index scan will need to read two pages (one for the index, one for the heap -- the visibility info is only in the heap so that must be consulted for each index match) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Is that pgsql support the database partitioning?
Hi all expert, I am a newbie on pgsql. It is so many thing that I don't know. Is that pgsql supports the database partitioning just like oracle do? For the current project, I need to store data into the same database. Can I split the data into different storage device by using database partitioning? Best regards, Corn ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Run scripts
Working with oracle I used the command @/home/lange/textfile.sql to run scripts. Does anyone happen to know how this works with postgres? Thanks for any help in advance, Chris ---(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] no Relation no 'id' in psql and PGAccess
I install PostgreSQL 7.1.3 first time and have problem. I create table Klient with PGAccess and - from it when I do table open a see every data - when I tried filter ID=32 I see error no column 'id' in psql I can't do any select on table Klient error : no Relation command \dt show this table then I create second table in psql klienci when query looks like select * from klienci, select count(*) from klienci everything is ok but when I tried select Id, Nazwa from klienci the error apear no 'id' what is wrong or what I'm doing incorrect? maybe there is problem with lower and upper case I tried also copy klienci from '..' DELIMITER '|' was incorrect error parse error near 'delimiter' copy klienci from '..' delimiter '|' was correct any ideas? columns in table: Id int4 Nazwa text NIP text ... Arek Malinowski ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] HELP WANTED: Open Source Consultant Programmer wanted for quick project
HELP WANTED: Open Source Consultant Programmer wanted for quick project We are in need of someone to quickly develop a new web project. You will need to be an expert on setting up Apache Web Server on a Linux system and in designing an online data base using Postgresql or MySQL. HTML required, PHP probably required, Java Java Script may or may not be required. You will be totally responsible for taking this rough idea and implementing a working, fully scalable website. Initial design will be two load shared Linux boxes running Apache on the front end and one Linux box running Postgresql or MySQL on the back end. The site will be co-located at a broadband ISP's facility so complete remote admin will be required, including a daily data dump from the web server to the admin system. This is not a giant bucks project but we have some cash to pay for this work. We are located in central Florida and would prefer someone in this area but it is not an absolute requirement. All code will be our property meaning we can not pay for code to be developed and licensed to us. Development cycle is 8 to 12 weeks, so you need to know your stuff. No time for learning curve on this one. This is a very interesting project. If you are a serious candidate with the required skill set, please send your complete resume and contact information to [EMAIL PROTECTED] tmb PS, If you are a shop and your rates are salary + overhead + margin you are probably not what we are looking for. ---(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] Mysql to postgres tools -reviews?
I am looking for feedback from anyone that has moved their data from Mysql to Postgres using the tools in contrib/mysql or any other data porting tools on the net. Did any data get lost in the transfer? Recommendations? Amount of time for transfers? Any feedback would be greatly appreciated. Thanks in advance. GW ---(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: [SQL] getting the oid for a new tuple in a BEFORE trigger
On Wed, Aug 29, 2001 at 11:15:08AM +0200, Markus Wagner wrote: Hi, we need to control database changes within BEFORE triggers. There is no problem with triggers called by update, but there is a problem with triggers called by insert. We strongly need to know the oid of a newly inserted tuple. In this case, we use tg_newtuple of the TriggerData structure passed to thetrigger function, and its t_data - t_oid will have the value '0'. Using BEFORE and AFTER triggers would make our lives much harder. Is there any way (even hack) to get the oid the newly inserted tuple will receive? Thank you very much, Markus ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly end of the original message Read section 24.2.5.4 'Obtaining other results status' of the Programmer's Guide. This is for the PL/pgSQL language, though. Francesco Casadei ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
