Re: [HACKERS] [GENERAL] Using oids
> That said, there is no reason why someone couldn't create a last_sequence() > function so you could say SELECT currval( last_sequence() ). Ofcourse, if > your table has no SERIAL field, you're stuffed either way. Instead of SELECT currval( last_sequence() ), what about implementing oracl type binding? Ala http://groups.google.com.au/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&th=5cc63a569577d024#link2 With the new FE/BE changes, how easy would it be to implement? (I mentioned it the FE/BE discussions) Ashley Cambrell ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Optimizer picks an ineffient plan
"Bupp Phillips" <[EMAIL PROTECTED]> writes: > but... > > select * from customer order by customer_id, first_name; > QUERY PLAN: > Sort(cost=142028.25..142028.25 rows=102834 width=724) > -> Seq Scan on customer (cost=0.00..4617.34 rows=102834 width=724) > Total runtime: 1.81 msec Actually in this case the optimizer would likely still use a sequential scan even if it had an index it thought it could use. If you're going to be reading the whole table anyways it'll be faster to read it in order than to jump all around even if you have to sort it. However you do have a point. In this case I don't think postgres even considers using the index. Even if it would decide not to use it in this case there could conceivably be cases where it would want to use it. However I'm not sure I see a lot of cases where this would come up. Even in automatically generated code, which is the usual cause of redundant things like this, I don't think I've seen this particular combination ever come up. -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] inserting via "on insert" rule
Peter Eisentraut wrote: Andreas Fromm writes: I was thinking of defining a view "users" over "persons" which would let me retrive the list of useres. But How would I implement the rule for insertiung users? I tryed the following but NEW is not known where I want to use it: CREATE VIEW users AS SELECT * FROM persons WHERE is_user(person.id) = TRUE; CREATE RULE insert_on_users AS ON INSERT TO users DO INSTEAD INSERT INTO persons SELECT * FROM NEW; You can write ... DO INSTEAD INSERT INTO persons VALUES (NEW.col1, NEW.col2, ...); Yes, but the advantage of the select would be that I could do a SELECT .. FROM .. WHERE , or how can I perform a checking of the data before insertion? Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] setting last_value of sequence
John Harrold wrote: -- Start of PGP signed section. > i've run into the situation where i need to set the last_value of a > sequence. can someone tell me how this is done? setval()? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Query
"Satish Kumar" <[EMAIL PROTECTED]> writes: > I'm looking for a database that will support Mac 8.6 onwards. Do you have any > suggestions. PostgreSQL runs fine on OS X, but doesn't and won't run on "Classic" Mac OS. -Doug ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: CPAN, P for postgresql [Re: [GENERAL] LAST_DAY Function in Postgres]
Shridhar Daithankar wrote: > On 2 Sep 2003 at 15:08, Amin Schoeib wrote: > > I would like to know if there is a Function in Postgres > > Like the LAST_DAY Function in Oracle?? > > In Oracle you can use the function the get the last day of a month. > > While this is not a direct answer to question.. > > It made me think. I am sure lot of people have lot of functions written in > order to get job done. Can we have an archive of such functions, if authors are > willing to make it open. > > Something like CPAN? Yeah we don't need to change that name..:-) There is the PostgreSQL Cookbook on the techdocs web site. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] is it possible to do this? have a subselect that
On Wed, 2003-09-03 at 13:49, Ron wrote: > see below > > Greg Stark wrote: > > >So I have a query in which some of the select values are subqueries. The > >subqueries are aggregates so I don't want to turn this into a join, it would > >become too complex and postgres would have trouble optimizing things. > > > >So my question is, is there some way to have a subselect return multiple > >columns and break those out in the outer query? > > > >Something like: > > > >SELECT x,y,z, > > (SELECT a,b FROM foo) AS (sub_a,sub_b) > > FROM tab > > > > SELECT x, y, z, SS.* > FROM tab, (SELECT a,b FROM foo) SS But where's the join between tab and foo? Wouldn't you then get a combinatorial explosion? -- - Ron Johnson, Jr. [EMAIL PROTECTED] Jefferson, LA USA "Perl is worse than Python because people wanted it worse." Larry Wall, 10/14/1998 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Inquiry From Form [pgsql]
Hi Donald, Donald Formiga Leite Junior wrote: Hi. Sorry about my English. I´m finishing my studies in the University and my final project is about PostgreSQL. I want to know if is it possible to send some materials, manual, documentation about this Database. Thank You. I wait for reply. Have you checked http://www.postgresql.org/docs/ ? -- Josué Maldonado. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Tomcat Connection Pool?
Oki, thx... Any ideas to what is sensible values also? (Like number of maxIdle compared to maxActive, etc..) BTJ On Wed, 2003-09-03 at 15:34, Paul Thomas wrote: > On 02/09/2003 23:06 Bjørn T Johansen wrote: > > I am running a connection pool for the PostgreSQL and I was wondering > > which values you would reccommend for the connection pool? > > > > > > maxWait > > 5000 > > > > > > maxActive > > 10 > > > > > > maxIdle > > 2 > > > > > > What does maxWait and maxIdle means? > > maxWait is is maximum time the connection pool will wait for a connection > to become available so it only has an effect when all maxActive > connections are being used at the same time. maxIdle is the maximum number > of connections what the pool will keep open. Using your settings as an > example, say you reach a point where all 10 connections have beed created > and are in use and that sometime later the nunber of requests drops so > that they can serviced by just a few connections. In this case excess idle > connections will be closed but there will always be at least maxIdle > connections left open. > HTH ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] PostgreSQL upgrade -> fails to start server
Quoting Sander Smeenk ([EMAIL PROTECTED]): > > Postgres already has a -P option to disable system indexes. Would it be > > feasable to have a safe mode so that if your locale is broken it sets that > > flag and also sets enable_indexscan to false. This would at least give you > > enough to pg_dump things. > Also see my post to Miquel on this list. I'd really like to see such an > option. Even though i'll try hard not to have this problem ever again. > So. I'll be hacking postgresql sources today ;) Well, not really that day, but today I hacked postgresql :) Line 2261 in src/backend/access/transam/xlog.c changed from -if (setlocale(LC_CTYPE, ControlFile->lc_collate) == NULL) to +if (setlocale(LC_CTYPE, "nl_NL.UTF-8") == NULL) rebuilt the debian package, made backups, installed it and: -rw-r--r--1 postgres postgres 8461201 Sep 3 22:38 db.all.sql The dump looks all normal! Guess I was lucky that the 'wrong' locale the db was initialised with was also 100% nl_NL.UTF-8 aparently :) I'll go re-create the database then, and try to import the dump. Thanks everyone for all the ideas! Sander. -- | I must consider my sins... | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D pgp0.pgp Description: PGP signature
Re: [GENERAL] delivering database stand-alone
> -Original Message- > From: Christopher Browne [mailto:[EMAIL PROTECTED] > Sent: Monday, September 01, 2003 12:43 PM > To: [EMAIL PROTECTED] > Subject: Re: [GENERAL] delivering database stand-alone > > > Martha Stewart called it a Good Thing > [EMAIL PROTECTED] (Joost Kremers)wrote: > > i am planning to build a database (a dictionary in fact) > that i will > > eventually want to distribute on a cd (or downloadable iso). what i > > would like to know is if this is technically possible with > postgresql. > > and how exactly would it be done? would i have to make > postgresql run > > off the cd, or should it first be installed to the hard disk? (the > > latter would be problematic on linux, given the many > different distros > > and their different package management systems...) how would i deal > > with systems that already have a postgreql server or (more > difficult > > perhaps) a different database server running? > > > > or should i not make use of postgresql (or any database > server) at all > > for the cd? after all, the data in the database is static, > users will > > not have to modify it, just look it up. > > This sounds like a candidate for Dan Bernstein's "CDB" (Constant > DataBase) library. It builds highly efficient "compiled" > hash tables, that are intended to be treated as "read-only." > (In fact, they can't readily be updated, once compiled.) > > That presents three issues: > > 1. It's basically doing "hash table" access; no ordering; no > approximate matches. > > 2. No ability to submit SQL queries. > > 3. Discussions of DJB's licensing arrangements tends to cause brain > haemorraging, anger, and other ills. The Debian folk have > created an alternative version that I believe is in the > public domain (e.g. - not GPL; not LGPL; possibly "freer than > the BSD License"). > > In contrast, read-only access to PostgreSQL databases is, > while occasionally discussed, not currently attainable. > (Not, at least, in a "burn the DB on CD" form.) Something else to consider: http://www.garret.ru/~knizhnik/databases.html All freely available and open source. They all use OO paradigm, and so will be uncomfortable for those who are not used to it. ---(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] TCL trigger doesn't work after deleting a column
>From what i am seeing / thinking did you drop it from the table ??? I am assuming yes and therefore you should recreate the function and the trigger and you should be fine. Using 7.3.x use the CREATE OR REPLACE FUNCTION to replace the function so it will still have the same OID. Next drop the trigger and then recreate the trigger and you should be fine HTH Darren On Wed, 3 Sep 2003, Josué Maldonado wrote: > Hello list, > > The TCL trigger that uses NEW and OLD arrays failed after after I > removed a unused column, now I got this error: > > pltcl: Cache lookup for attribute 'pg.dropped.24' type 0 > failed > > I already did a vacuum, but the error remain. Any idea how to fix/avoid > that? > > Thanks in advance > > -- Darren Ferguson ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] absolute value fro timestamps
Claudio Lapidus wrote: > Hello > > Evidently is possible to have a negative time interval: > > clapidus=> select interval '-1'; > interval > --- > -01:00:00 > > However, there seems to be no provision to get the absolute value in such > case: > > clapidus=> select @ interval '-1'; > ERROR: operator does not exist: @ interval > HINT: No operator matches the given name and argument type(s). You may need > to add explicit typecasts. > > clapidus=> select abs(interval '-1'); > ERROR: function abs(interval) does not exist > HINT: No function matches the given name and argument types. You may need > to add explicit typecasts. > > What did I miss this time? Why would you want an abolute value of a negative interval? This works: test=> select -(interval '-1'); ?column? -- 01:00:00 (1 row) so I suppose you could create a function or CASE statement to get the absolute value. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] absolute value fro timestamps
Bruce Momjian wrote: > Why would you want an abolute value of a negative interval? Because I'm trying to match pairs of records that satisfy certain criteria, one of which is that both records have a timestamp that *may* be slightly offset between them, so I substract the two and the result must be no greater than the allowed offset. I don't know which record has the greater timestamp, so I don't know the sign of the substraction in advance. > > This works: > > test=> select -(interval '-1'); > ?column? > -- > 01:00:00 > (1 row) > > so I suppose you could create a function or CASE statement to get the > absolute value. > In the meantime I implemented it the following way: \set maxoffset 4 select ... where abs(extract(epoch from age(m1.ts, m2.ts))) < :maxoffset ... Which I think is more compact. Anyway, it would be nice to be able to write directly abs(age(m1.ts, m2.ts)) IMHO. thanks cl. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] TCL trigger doesn't work after deleting a column
Hi Ian, Ian Harding wrote: Is the column you deleted one that you referred explicitly by name in your function? No, the column I deleted does not get refered explicitly in the function. What version are you using? 7.3.3, and I'm planning to upgrade to 7.3.4 later today maybe that can help. Thanks, ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Using oids
Jonathan Bartlett <[EMAIL PROTECTED]> writes: >>> If you want a globally unique ID based on OIDs, use the table OID >>> concatenated with the row OID. >> Ok, this make sense ! > Are you sure this works after you hit the 4 billion mark? If you have a unique index on OID on each table for which you care, yes. As someone else pointed out, you do then have to cope with the possibility of insertions failing because of OID conflicts. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Using oids
On Wed, 2003-09-03 at 22:12, Jonathan Bartlett wrote: > Are you sure this works after you hit the 4 billion mark? As long as the returened oid is unique on the table in current session ! /BL ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Index not being used ?
CREATE TABLE public.base ( nombre varchar(255), calle varchar(255), puerta int2, resto varchar(255), lid int2, area varchar(4), telefono varchar(10) ) CREATE INDEX base_dir ON base USING btree (lid, calle, puerta); And trying the following select: select * from base where lid = 457 and calle = 'MALABIA' and puerta = 10 I think its because lid and puerta are int2's and 457 and 10 are int4's. Try lid = '457'::int2 and puerta = '10'::int2. To use an index the variables have to match types exactly. --- Adam Kavan --- [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] TCL trigger doesn't work after deleting a column
Hi Darren, [EMAIL PROTECTED] wrote: From what i am seeing / thinking did you drop it from the table ??? I am assuming yes and therefore you should recreate the function and the trigger and you should be fine. Using 7.3.x use the CREATE OR REPLACE FUNCTION to replace the function so it will still have the same OID. Next drop the trigger and then recreate the trigger and you should be fine Did that, and still got the same error. I'm currently preparing the server to update to 7.3.4 I hope this could help. Thanks ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] setting last_value of sequence
Perhaps:
SELECT SETVAL('resrc_serial', MAX(resource_id)) FROM ia_resources;
the sequencethe column the table
This sets the sequence to the highest number after I have used "copy" to load a table;
other values instead of MAX() could be used (e.g. 123456, etc.).
HTH,
Greg Williamson
-Original Message-
From: John Harrold [mailto:[EMAIL PROTECTED]
Sent: Tuesday, September 02, 2003 2:01 PM
To: pgsql general list
Subject: [GENERAL] setting last_value of sequence
i've run into the situation where i need to set the last_value of a
sequence. can someone tell me how this is done?
--
--
| /"\
john harrold | \ / ASCII ribbon campaign
jmh at member.fsf.org| X against HTML mail
the most useful idiot | / \
--
What difference does it make to the dead, the orphans, and the homeless,
whether the mad destruction is brought under the name of totalitarianism or
the holy name of liberty and democracy?
--Gandhi
--
gpg --keyserver keys.indymedia.org --recv-key F65A739E
--
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] XA Resource Manager
Is there an XA Resource Manager for PostgreSQL (perhaps with an interface exposed using CORBA/IDL) ? If not, would you know what would prevent one from being written ? Thanks! -- __ Sign-up for your own personalized E-mail at Mail.com http://www.mail.com/?sr=signup CareerBuilder.com has over 400,000 jobs. Be smarter about your job search http://corp.mail.com/careers ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] About GPL and proprietary software
[EMAIL PROTECTED] (Ron Johnson) wrote: > That "_by whatever means_" seems to include "network link", and that > doesn't sound right. Ah, but in order to use it over the network link you need to be running their server software, on the one side, and their client access software, on the other. Both sides are linked to GPL-licensed software. Your client software has to link in software belonging to MySQL AB, and that's where they are now "biting" people on this. This is one of the reasons why the PHP people removed bundled MySQL support in version 5 back in June. -- let name="aa454" and tld="freenet.carleton.ca" in name ^ "@" ^ tld;; http://www.ntlug.org/~cbbrowne/rdbms.html "It is not enough to succeed, others must fail." -- Gore Vidal ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Query
Hi, I'm looking for a database that will support Mac 8.6 onwards. Do you have any suggestions. Your input/advice on this will be appreciated, satish
Re: [GENERAL] After install 7.3.4, I got a 7.3.3 ver. no?
Sorry... I'm running PostgreSQL under Win32 I've forget to upgrade my cygwin after do that, it's ok! Sepho "Doug McNaught" <[EMAIL PROTECTED]> writes news:[EMAIL PROTECTED] > "Sephiroth" <[EMAIL PROTECTED]> writes: > > > After install PostgreSQL 7.3.4, I got a 7.3.3 version no? > > I compiled 7.3.4 from source and both psql and the server report the > correct version. Are you sure you actually installed 7.3.4 in the > right place? Did you get the source from CVS or use a tarball? > > My best guess is that you're still running 7.3.3. :) > > -Doug > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] automatic update
Hi to all ... I'm looking to a way to auto update some fields of a row when other fileds are updated. the table structure are simple, CREATE TABLE param ( id int4 PRIMARY KEY, val int4, ts timestam(3) DEFAULT CURRENT_TIMESTAMP ); so when a new entry are insert ts areautomatically update, but i would make the same on update ... i would that on UDPATE param SET val=100 WHERE id=1; also ts field have to be updated to CURRENT_TIMESTAMP I try with a RULE but obtain only a loop, seem RULE aren't good to make such things, modify a statment on the same table it's related :( i look for a trigger but I suppose the same problem arise ... how i can solve the problem ??? mhhh does i have to have a VIEW of parm ... called param2 without the ts field and make a rule on param2 that update param.ts ??? seem a bit tricky :( ... thanks in advance ... -- Alessandro GARDICH <[EMAIL PROTECTED]> gremlin.it ---(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] Crosstab function Problem
Hi,
I am having problem using the
crosstab function ( which is under the contrib/tablefunc directory). I have
installed the tablefunc module and also installed the functions into my
DB.
I am using postgresql 7.4 beta
version.
I am issuing the following query,
select *from crosstab('select
path,name,valuefrom file f, metadata mwhere f.id = m.file_idand
f.type=1order by 1,2;',3)AS DP(name text,runid text, plottype
text,region text) ;
I am getting the following error
message.
No function matches the given
name and argument types. You may need to add explicit typecasts.
I also issued the given example in the tablefunc
directory and it gave the same error.
Help me out with this,
Kuldeep.
[GENERAL] Optimizer picks an ineffient plan
I have a customer table that has the field CUSTOMER_ID as the primary key (cust_pkkey), the table has 102,834 records in it. The following select statement works fine: select * from customer order by customer_id; QUERY PLAN: Index Scan using cust_pkkey on customer (cost=0.00..5175.17 rows=102834 width=724) Total runtime: 5999.47 msec but... select * from customer order by customer_id, first_name; QUERY PLAN: Sort(cost=142028.25..142028.25 rows=102834 width=724) -> Seq Scan on customer (cost=0.00..4617.34 rows=102834 width=724) Total runtime: 1.81 msec It seems that the optimizer should be able to detect (in this instance at least) that the first order by field is a primary key and should not consider the other fields because it's pointless... the resultset will be in order. NOTE: I'm testing this on Postgresql 7.2 for Windows, so this my have already been dealt with. Thanks and keep up the great work!! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Use of oids
While updating to 7.3.4 I note with some alarm the following passage in README.Debian.migration.gz written by Oliver Elphick: "Some schema designs rely on the use of oids as row identifiers. This is definitely not recommended, not least because oids are not guaranteed to exist in all future versions of PostgreSQL. Oids are an internal feature only. They are not suitable as candidate keys, since they are not guaranteed to be unique; furthermore, the starting point for oids is likely to change whenever a change to the database structure occurs." While I have not used oids to join tables, I have used them extensively in programming, because if Postgres has supplied a unique number for each row, why on earth should I bother supplying another one of my own? Like many people starting with Postgres, three or four years ago I carefully read Bruce Moynihan's excellent introductory book on Postgres, which explained many initially difficult concepts with such clarity. The book states "Every row in POSTGRESQL is assigned a unique, normally invisible number called an object identification number (OID). When the software is initialized with initdb, a counter is created and set to approximately seventeen-thousand. The counter is used to uniquely number every row. Although databases may be created and destroyed, the counter continues to increase. It is used by all databases, so identification numbers are always unique. No two rows in any table or in any database will ever have the same object ID." Further down we read: "Object identification numbers can be used as primary and foreign key values in joins. Since every row has a unique object ID, a separate column is not needed to hold the row's unique number." On the next page are listed the limitations of oids, for example they are nonsequential, nonmodifiable, and not backed up by default, but for my uses these were not problems at all. I have merely used the oid number as a temporary unique identifier before assigning a permanent booking number to it, which takes about a nanosecond, and in other similar cases. To sum up: The Debian migration gzip file declares that oids are not guaranteed to be unique, issues dire warnings about using them as keys and worst of all states that they may be phased out in the future. The book states that they are unique, tells you how to use them, actually gives an example of using them as primary and foreign keys (which fortunately I decided was not very wise) and certainly doesn't say anything about phasing them out in the future. Can anybody shed any light on this? Malcolm Warren ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Index not being used ?
Hi, I'm trying to understand why a perfect match index is not being used, and a sequence scan is done in place: PostgreSQL 7.3.4 on i686-pc-cygwin, compiled by GCC gcc (GCC) 3.2 20020927 (prerelease) I've a table with 7M records, and an index on 3 fields: CREATE TABLE public.base ( nombre varchar(255), calle varchar(255), puerta int2, resto varchar(255), lid int2, area varchar(4), telefono varchar(10) ) CREATE INDEX base_dir ON base USING btree (lid, calle, puerta); And trying the following select: select * from base where lid = 457 and calle = 'MALABIA' and puerta = 10 yields Seq Scan on base (cost=1.00..100212801.12 rows=1 width=63) Filter: ((lid = 457) AND (calle = 'MALABIA'::character varying) AND (puerta = 10)) even with enable_seqscan set to off, as you may have guessed. What am I missing here ? (There's another index on area and telefono which works as expected, so it's not a LOCALE problem AFAIK). -- Carlos G Mendioroz <[EMAIL PROTECTED]> LW7 EQI Argentina ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] delivering database stand-alone
You should check out Firebird (firebirdsql.sourceforge.net). I believe your can set databases to be read-only and when embedding, Firebird is a single DLL. Jacob On Mon, 01 Sep 2003 15:42:32 -0400, Christopher Browne <[EMAIL PROTECTED]> wrote: >Martha Stewart called it a Good Thing [EMAIL PROTECTED] (Joost Kremers)wrote: >> i am planning to build a database (a dictionary in fact) that i will >> eventually want to distribute on a cd (or downloadable iso). what i >> would like to know is if this is technically possible with >> postgresql. and how exactly would it be done? would i have to make >> postgresql run off the cd, or should it first be installed to the >> hard disk? (the latter would be problematic on linux, given the many >> different distros and their different package management systems...) >> how would i deal with systems that already have a postgreql server >> or (more difficult perhaps) a different database server running? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] web hosting postgres
see http://techdocs.postgresql.org/hosting.php On Tue, 3 Sep 2003, Aaron wrote: > Hi, > Can anyone out there point me to an inexpensive web hosting solution > that offers postgres database support??? > Thanks > Aaron > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Comparing dates
My date setting is ISO with US conventions, and output from a select is in the form -mm-dd (2002-01-18, for example. When I do a select such as SELECT * FROM table WHERE date BETWEEN '2001-12-28' AND '2002-01-28' It misses the entry with date '2002-01-28' (which does exist!). Likewise, SELECT * FROM table WHERE date = '2001-12-28' ; gives me '0 rows'. The only way I've been able to handle this, so far, is in this fashion: SELECT * FROM table WHERE date BETWEEN 20011228 - .001 AND 20020128 + .001 ; and similarly instead of = using, I can use date BETWEEN 20020128 - .001 AND 20020128 + .001 I must be missing something, but I can't find it. What is the 'right' way to select for a date type = a particular date, and for BETWEEN to work as advertised? I have a copy of 'Practical Postgresql', but I can't find the answer there, or in the online manual. Of course there are a lot of places to look and I may have missed it. Thanks, John Velman ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Using oids
> No it don't know anything about the table it insert into. I simply do
> the following :
>
> 1. INSERT data (comming from another layer)
> 2. Get the last oid
> 3. SELECT * FROM the same table where oid = what I just found.
>
> I know absolutly nothing about the table, and I like it this way :-)
The way I do it is to have a global sequence called 'objects' that spits
out 64-bit values, and then EVERY TABLE has a 64-bit field called
object_id, which defaults to nextval('objects') if I don't specify it.
So, on every table no matter what, I could do:
1. select nextval('objects');
2. INSERT data (comming from another layer, but set object_id to the
value I got in #1)
3. SELECT * FROM the same table where oid = what I just selected in #1
Jon
---(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] OffsetNumber offnum (LOCKTag)
the offnum of LOCKTAG I gather indicates which row (tuple) is being locked in a row level locking. But when I lock 2 diffrent rows of a table, offset for both is 0. and also offset is 0 if i take a table lock on the same table. (blkno is the same for all three locks)..shouldnt the OffsetNumber offnum be different for each individual row lociked? if not, then what field of LOCKTAG recognizes each individual row (tuple) locked? thanks! _ Get MSN 8 and help protect your children with advanced parental controls. http://join.msn.com/?page=features/parental ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Debian packages of 7.4beta2
I have made Debian packages of PostgreSQL 7.4beta2 and uploaded them to Debian's experimental archive. The package version is 7.3.99.7.4beta2-1 (so that when 7.4's final version comes out, it will be perceived as a later package). They are built on a machine running current unstable, so they cannot be loaded on a woody machine. I don't plan to make a woody version until 7.4 is properly released. The packages may not be visible for a while because there are some new binary packages that need to be authorised by the archive maintainers. Comments on the packages will be welcome. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "And he said unto his disciples, Therefore I say unto you, Take no thought for your life, what ye shall eat; neither for the body, what ye shall put on. For life is more than meat, and the body is more than clothing. Consider the ravens, for they neither sow nor reap; they have neither storehouse nor barn; and yet God feeds them; how much better you are than the birds! Consider the lilies, how they grow; they toil not, they spin not; and yet I say unto you, that Solomon in all his glory was not arrayed like one of these. If then God so clothe the grass, which is to day in the field, and tomorrow is cast into the oven; how much more will he clothe you, O ye of little faith? And seek not what ye shall eat, or what ye shall drink, neither be ye of doubtful mind. But rather seek ye the kingdom of God; and all these things shall be added unto you." Luke 12:22-24; 27-29; 31. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Web page for selecting presentations
I have created a web page describing my presentations: http://candle.pha.pa.us/main/writings/selecting.html This should help people who want me to speak to their group but have trouble deciding on the proper talk. There is also a link to this from my home page under Writings/Computer. Right now, I have Mexico(September), Germany(November), and Denmark(January) scheduled. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Using oids
On Wednesday 03 September 2003 17:24, Bo Lorentsen wrote: > On Wed, 2003-09-03 at 16:13, Tom Lane wrote: > > The reason OIDs shouldn't be considered unique is that there is no > > mechanism to enforce that they are unique --- unless you make one, > > that is, create a unique index on OID for a table. The system does > > not do that for you since it would be excessive overhead for tables > > in which the user doesn't care about OID uniqueness. But I'd > > definitely recommend it if you are using OIDs for row identifiers. > > Ok, so my little INSERT / SELECT show will continue to work for a long > time, as I only uses the oids on short term bacis. > > > If you want a globally unique ID based on OIDs, use the table OID > > concatenated with the row OID. > > Ok, this make sense ! > > > No, there isn't. There is only ctid, which is not useful as a long-term > > row identifier, because UPDATE and VACUUM can change it. > > But there is no way for the client user to user these in a > "PQgetLastCtid" and "SELECT * from zxy where ctid = 42", so this will > not help :-) > > Thanks anyway, may oid's live for a long time, and one day become grown > up 64 bit values :-) > Any other way, a lot of (my) code will become useless :-( Regards ! ---(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] What is the good equivalent for ENUM ?
On Wed, 2003-09-03 at 09:50, Vivek Khera wrote:
> > "SD" == Shridhar Daithankar <[EMAIL PROTECTED]> writes:
>
> SD> On 3 Sep 2003 at 14:30, Bruno BAGUETTE wrote:
> >> The problem is that this MySQL database uses ENUM, do you see what can I
> >> do to migrate ENUM into PostgreSQL ?
>
> SD> varchar with check constraints. Add constraits to allow only
> SD> certain values of varchar string.
>
> I used to do this. It turns out to be horribly inflexible when you
> need to alter the enum values since the constraints cannot easily be
> changed.
It'll be better when domains have alterable constraints. Your
way is the traditional (and best, IMO) way, though.
> What I do is create a short table for the enum like this:
>
> CREATE TABLE status_levels (
> status varchar(10) PRIMARY KEY
> ) WITHOUT OIDS;
> INSERT INTO status_levels (status) VALUES ('active');
> INSERT INTO status_levels (status) VALUES ('overdue');
> INSERT INTO status_levels (status) VALUES ('suspended');
> INSERT INTO status_levels (status) VALUES ('terminated');
>
> then reference it via foreign key from the "enum" field:
>
> CREATE TABLE whatever (
> ...
> status varchar(10) NOT NULL DEFAULT 'active' REFERENCES status_levels(status),
> ...
> );
>
--
-
Ron Johnson, Jr. [EMAIL PROTECTED]
Jefferson, LA USA
"You ask us the same question every day, and we give you the
same answer every day. Someday, we hope that you will believe us..."
U.S. Secretary of Defense Donald Rumsfeld, to a reporter
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Access to an element of array NEW in TCL
Hi list,
Is there a way to access an especific element of the array NEW in an TCL
trigger, I have a loop that goes for each field (thanks Ian & Darren)
like this:
foreach id [array names NEW] {
then I can refer to an element with this (inside the loop):
$NEW($id)
I do need to make a reference to an especific column name (duser) in
that array to get its value, I already tried $NEW(duser),
$NEW(\'duser\') but didn't work. I'll appreciate any help from you
Thanks
Josue Maldonado
---(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] Using oids
On Wed, 2003-09-03 at 17:28, Martijn van Oosterhout wrote: > If you know the OID of a row, PostgreSQL doesn't have a special lookup table > to find it. That's also why they're not unique; the backend would have to > scan through every table to find out if the next one is available. Ahh, thats not nice, hav'nt checked that, yet. > So, unless you specifically add an index to the table, looking up by OID > will always trigger a sequential scan. I thought it was much more easy for PG to find these, but I quess ctid are the one that is fast to find. > That said, there is no reason why someone couldn't create a last_sequence() > function so you could say SELECT currval( last_sequence() ). Ofcourse, if > your table has no SERIAL field, you're stuffed either way. Not as nice as oid's. /BL ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Using oids
Bo Lorentsen <[EMAIL PROTECTED]> writes:
> > If I were doing it would extract the primary key of each table on startup
> > and then change that one line of code to:
> >
> > os << "SELECT * FROM " << sTable << " WHERE "
> ><< prikey << " = currval('" << sTable << "_" << prikey << "_seq')";
>
> Thanks, but I have to be aware of the "prikey" name, and demand a prikey
> for all tables to insert row into :-(
This is an issue faced mostly by driver developers that want to provide high
level abstract interfaces.
The problem is that using OIDs is basically imposing a primary key on every
table even when the application designer didn't want one. They're mostly
redundant because most tables will have a primary key, wasteful for small
tables, and inadequate for large tables.
I don't like hard coding the assumption that the sequence name is based on the
primary key column name either though. Not every table will have a primary key
of "serial" type. Consider reference tables where the primary key is
non-arbitrary value. Even when it is, the sequence name can be truncated.
The new binary FE protocol included some discussion of API features to allow
drivers like JDBC get column information. I believe that API included an
indication of what the primary key column was. I'm not sure it includes a hook
to get the value of the last insertion, presumably via the sequence. If it
does I would think that would be far preferable to using OIDs.
The disadvantage: tables with no primary key whatsoever would couldn't be
supported by your high level abstraction. I only end up with tables with no
primary keys for many-to-many relationships (or one-to-many sets of immutable
data which amounts to the same thing) anyways. You want to insert, do mass
deletions, but never update such records anyways.
The pros: no extra overhead for OIDs, more portable to other databases.
--
greg
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] pg_autovacuum
At 09:41 PM 9/2/03 -0400, Matthew T. O'Connor wrote: On Tue, 2003-09-02 at 20:40, Adam Kavan wrote: > And there is the problem, all of the counts stay at 0 no matter what I do. OK, so why is this happening... a bug in the stats system? Ignoring pg_autovaccu, what numbers do you get from the stats system when you do a: SELECT relname,n_tup_ins,n_tup_upd,n_tup_del from pg_stat_user_tables where relname = 'foobar'; both before and after your insert / update script. Matthew Before updates: relname | n_tup_ins | n_tup_upd | n_tup_del +---+---+--- PointUsage | 0 | 0 | 0 (1 row) After updates: relname | n_tup_ins | n_tup_upd | n_tup_del +---+---+--- PointUsage | 0 | 0 | 0 (1 row) After deleting updates: relname | n_tup_ins | n_tup_upd | n_tup_del +---+---+--- PointUsage | 0 | 0 | 0 (1 row) This is the problem... I just don't know how to fix it, or even what is causing it. I am 100% sure that I am inserting into the same database that I executed the select from. Here are the relevent lines from postgresql.conf: stats_start_collector = true #stats_command_string = true #stats_block_level = true stats_row_level = true stats_reset_on_server_start = false Any ideas? --- Adam Kavan --- [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Using oids
On Wed, Sep 03, 2003 at 01:47:01PM +0200, Bo Lorentsen wrote: > On Wed, 2003-09-03 at 13:19, Martijn van Oosterhout wrote: > > The only thing you need to know is the name of the primary key field. This > > many be a problem in a generic layer. If you like you can make a UNIQUE > > INDEX on the oid column and retry inserts when they fail. > Hmm, it all end up putting alot of information to a lower layer, and > this is sad as PG already knows, but it may not tell me. Well, in a sense it know and in a sense it doesn't. Sequences are not considered special in terms of returning data to the client. It's just another function from the parser's point of view. > > In your code, do create an index on the OID column? If not, that's be a > > performance hit, > I'm not sure what you mean ! If you know the OID of a row, PostgreSQL doesn't have a special lookup table to find it. That's also why they're not unique; the backend would have to scan through every table to find out if the next one is available. So, unless you specifically add an index to the table, looking up by OID will always trigger a sequential scan. That said, there is no reason why someone couldn't create a last_sequence() function so you could say SELECT currval( last_sequence() ). Ofcourse, if your table has no SERIAL field, you're stuffed either way. -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > "All that is needed for the forces of evil to triumph is for enough good > men to do nothing." - Edmond Burke > "The penalty good people pay for not being interested in politics is to be > governed by people worse than themselves." - Plato pgp0.pgp Description: PGP signature
Re: [GENERAL] Using oids
On Wed, Sep 03, 2003 at 08:46:42 -0700, Dennis Gearon <[EMAIL PROTECTED]> wrote: > Why is that, anyway, and why should it be? Because it reduces contention by giving each backend its own pool of sequence values. But until you call nextval a backend won't have any values reserved. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Using oids
On Wed, 2003-09-03 at 16:13, Tom Lane wrote: > The reason OIDs shouldn't be considered unique is that there is no > mechanism to enforce that they are unique --- unless you make one, > that is, create a unique index on OID for a table. The system does > not do that for you since it would be excessive overhead for tables > in which the user doesn't care about OID uniqueness. But I'd > definitely recommend it if you are using OIDs for row identifiers. Ok, so my little INSERT / SELECT show will continue to work for a long time, as I only uses the oids on short term bacis. > If you want a globally unique ID based on OIDs, use the table OID > concatenated with the row OID. Ok, this make sense ! > No, there isn't. There is only ctid, which is not useful as a long-term > row identifier, because UPDATE and VACUUM can change it. But there is no way for the client user to user these in a "PQgetLastCtid" and "SELECT * from zxy where ctid = 42", so this will not help :-) Thanks anyway, may oid's live for a long time, and one day become grown up 64 bit values :-) /BL ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Localization (for dates) Oracle vs. Postgresql
"Amin Schoeib" <[EMAIL PROTECTED]> writes: > Is there maybe any other??? The only other suggestion I can make is to write your own formatting function. It'd be a pain in the neck to build a general-purpose one, but you could handle a fixed output format with just a few lines of code in plpgsql. (Might be even easier in plperl or pltcl.) That would probably tide you over until to_char() has more locale support. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Using oids
Dennis Gearon <[EMAIL PROTECTED]> writes: > Oliver Elphick wrote: > > >You cannot use currval() until you have used nextval() on the same > >sequence in the same session. > Why is that, anyway, and why should it be? Because that's what currval() does. It doesn't have anything to do with sequence values in other sessions. It gives you the last value *you* got for thee sequence, so if you haven't called nextval() yet you should and do get an error. Maybe it should have been called lastval(), but that could be a bit misleading too... -Doug ---(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] pg_dump incorrect sequence value
"Joshua L. San Juan" <[EMAIL PROTECTED]> writes:
> but in the second table - the sequence was incorrect:
> SELECT pg_catalog.setval ('table2_id_seq', 1, true);
It seems fairly hard to believe that what pg_dump emitted was not the
actual state of the sequence object. Why do you think this is incorrect?
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
[GENERAL] postmaster crashing
I have been trying to find out more about the postmaster crashing, but things seem to be getting stranger! I am experiencing problems running postmaster in gdb too (see end of message) I will put all the information in this posting for completness, apologies for the duplicated sections. I am running postgresql 7.3.4 on ia64 Red Hat Advance Server 3 beta. Now compiled from 7.3.4 source downloaded from postgresql.org. Tsearch2 compiled from tsearch-v2-stable.tar.gz I am very stuck so thank you for any ideas or guesses about whats happening or how to further research the problem. Potentially useful output below (in the order i did it): cd postgresql7.3.4_src_dir ./configure --enable-debug <- rest of install process from top of readme -> <- tsearch2 compile/install -> initdb on /data createdb test <- create a table in test and populate it with test data -> <- query test data sucessfully -> test# SELECT 'Our first string used today'::tsvector; tsvector --- 'Our' 'used' 'first' 'today' 'string' (1 row) test=# SELECT to_tsvector( 'default', 'this is many words' ); server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. !# LOG: server process (pid 7698) was terminated by signal 11 LOG: terminating any other active server processes LOG: all server processes terminated; reinitializing shared memory and semaphores LOG: database system was interrupted at 2003-09-02 13:26:42 UTC LOG: checkpoint record is at 0/967458 LOG: redo record is at 0/967458; undo record is at 0/0; shutdown TRUE LOG: next transaction id: 581; next oid: 25098 LOG: database system was not properly shut down; automatic recovery in progress FATAL: The database system is starting up LOG: ReadRecord: record with zero length at 0/9674A0 LOG: redo is not required LOG: database system is ready <-shutdown backend -> After more poking i discovered that the to_tsvector function call does not cause a seg fault in the backend if you pass it only numbers, characters and whitespace, but instead works as desired. ddd postmaster <- run postmaster with -D /data -> psql test <- seg fault, similar LOG message to above but now with signal 5 -> psql db_that_not_exist <- seg fault as previous -> How do i get the core files to examine? There never seem to be any produced, even outside the debuggers. I can't even connect to the db when its running in the debugger Thanks for reading this far, Grateful for any help or sugestions, Matt -- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Using oids
The elimination is in concert with the dying of popularity in 'Object Oriented Databases', right? Shridhar Daithankar wrote: On 3 Sep 2003 at 10:27, Malcolm Warren wrote: To sum up: The Debian migration gzip file declares that oids are not guaranteed to be unique, issues dire warnings about using them as keys and worst of all states that they may be phased out in the future. The book states that they are unique, tells you how to use them, actually gives an example of using them as primary and foreign keys (which fortunately I decided was not very wise) and certainly doesn't say anything about phasing them out in the future. Yes. It is correct. As of 7.3.x and onwards oids are optional at table creation times. They default to be available for new objects but that is for backwards compatibility I believe. In future, they would default to be not available for a particular object(hopefully). Right now you need to explicitly specify no oids while creating tables etc. About oids not being unique, oids can assume 4 billion different values. If you have more than those many rows in a table, oids will wrap around and will no longer be unique in that object. About oids being eliminated, I am sure it would happen some time in the future, looking at the development on this issue. Core team could elaborate more on this. Correct me if I am wrong. HTH Bye Shridhar -- Nusbaum's Rule: The more pretentious the corporate name, the smaller the organization. (For instance, the Murphy Center for the Codification of Human and Organizational Law, contrasted to IBM, GM, and AT&T.) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Using oids
Why is that, anyway, and why should it be?
Oliver Elphick wrote:
On Wed, 2003-09-03 at 12:19, Martijn van Oosterhout wrote:
If I were doing it would extract the primary key of each table on startup
and then change that one line of code to:
os << "SELECT * FROM " << sTable << " WHERE "
<< prikey << " = currval('" << sTable << "_" << prikey << "_seq')";
You cannot use currval() until you have used nextval() on the same
sequence in the same session.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] What is the good equivalent for ENUM ?
that's how I'd do it, since in reality, that's all an ENUM is, is a mini
foreign table internalized onto a column in another table.
Vivek Khera wrote:
"SD" == Shridhar Daithankar <[EMAIL PROTECTED]> writes:
SD> On 3 Sep 2003 at 14:30, Bruno BAGUETTE wrote:
The problem is that this MySQL database uses ENUM, do you see what can I
do to migrate ENUM into PostgreSQL ?
SD> varchar with check constraints. Add constraits to allow only
SD> certain values of varchar string.
I used to do this. It turns out to be horribly inflexible when you
need to alter the enum values since the constraints cannot easily be
changed.
What I do is create a short table for the enum like this:
CREATE TABLE status_levels (
status varchar(10) PRIMARY KEY
) WITHOUT OIDS;
INSERT INTO status_levels (status) VALUES ('active');
INSERT INTO status_levels (status) VALUES ('overdue');
INSERT INTO status_levels (status) VALUES ('suspended');
INSERT INTO status_levels (status) VALUES ('terminated');
then reference it via foreign key from the "enum" field:
CREATE TABLE whatever (
...
?status varchar(10) NOT NULL DEFAULT 'active' REFERENCES status_levels(status),
...
);
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Commercial postgresql
> "TL" == Tom Lane <[EMAIL PROTECTED]> writes: TL> I don't believe Bruce has yet made any effort to update the SGML TL> release-notes file for 7.4. Instead look at the CVS-tip HISTORY file: Definitely not since the 7.4b2 INSTALL docs refer to it being version 7.3. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Trunc in Postgres
"Amin Schoeib" <[EMAIL PROTECTED]> writes: > Is there an equivalent for the trunc function of Oracle in Postgres??? > I need to trunc(the zeros) a number which is stored as a char with a lot of= > zeros > Like that : 004 > In oracle you can make that by trunc(YOUR_COLUMNNAME,0) We use the SQL-standard spelling, TRIM(). regards, tom lane ---(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] Trunc in Postgres
On Wed, 3 Sep 2003, Chris Boget wrote:
> > You can try
> > select CAST(cast('4' as NUMERIC(20)) AS varchar);
>
> noobie question:
>
> Do multiple casts like that slow down the query (in general and not specifically
> for the query above)? If not, what about if the query is complex?
I don't know. This is simple solution. You can write UDF in c if qwery
will be slowly, like this?
PG_FUNCTION_INFO_V1 (ztrim);
Datum ztrim(PG_FUNCTION_ARGS)
{
BpChar *rc = PG_GETARG_BPCHAR_P(0);
int l = VARSIZE (rc) - VARHDRSZ;
while (*rc == ' ' || *rc == '0')
{
rc++;
if (--l == 0) break;
}
BpChar *res;
res = palloc (VARHDRSZ + 1);
VARATT_SIZEP (res) = VARHDRSZ + 1;
strncpy(VARDATA(res), rc, l);
PG_RETURN_BPCHAR_P (res);
}
>
> thnx,
> Chris
>
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Commercial postgresql
> "SD" == Shridhar Daithankar <[EMAIL PROTECTED]> writes: >> Reindexing a table takes an exclusive table lock. If I did it inside >> a transaction, wouldn't it still take that lock and block out all >> other access? SD> Well, you donm't need to reindex as such. You can create a new index from SD> scratch and drop the old one inside a transaction. What about the primary keys? How do you create a new primary key without first dropping the old one? -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Localization (for dates) Oracle vs. Postgresql
"Amin Schoeib" <[EMAIL PROTECTED]> writes: > When I would execute this: > select to_char(now(),'DD.Month,') > I would get the monthname in english but how can I perform with to_char > That I become the monthname in german?? Karel, isn't there a way to get localized month names using to_char() ? I thought there was, but I don't see anything about it in the manual. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Using oids
On Wed, Sep 03, 2003 at 01:05:30PM +0200, Malcolm Warren wrote: > I agree with you about database design and in fact fortunately I don't use > oids as foreign keys, which I thought unwise. However I have found oids very > useful as temporary unique references to a record in my programming. If I > had known when I started writing my code three years ago that there was even > the slightest doubt about continuing with oids then I wouldn't have used > them. You can create tables WITH OIDS (this is by default on 7.3, but will probably changed in some future release). If you also create an unique index on the oid column of the table, you have all you need. But beware that some INSERTs will fail because the OID counter will wrap around at some point. There is a non-zero probability that the newly generated OID will collide with an existing tuple in that table; you have to be prepared to repeat your query in that case, which can be a pain if you are doing something else in the same transaction. -- Alvaro Herrera (<[EMAIL PROTECTED]>) "Lo esencial es invisible para los ojos" (A. de Saint Exúpery) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Oracle decode Function in Postgres
--- Amin Schoeib <[EMAIL PROTECTED]> wrote: > Hi, > Like I see there is no equivalent to the Oracle > decode Function > In Postgres.Is there maybe somebody who wrote decode > as a > Function? The problem for any such body is that there is no one function possible in PostgreSQL that will cover all of the possible use cases of "decode" in Oracle. As I understand things, "decode" takes a variable number of arguments, for a variety of datatypes. In PostgreSQL you would need to create a separate "decode" function for every distinct set of arguments (number and datatypes) that you are likely to encounter. That would be a lot of work for a generalized case. You may be best served by searching your code for usage of "decode", and writing only those versions that you need. The coding for each one would be trivial, using "case" constructions. > > Schoeib > > 4Tek Gesellschaft für angewandte > Informationstechnologien mbH > Schoeib Amin > Tel. +49 (0) 69 697688-132 > Fax. +49 (0) 69 697688-111 > http://www.4tek.de > > __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Localization (for dates) Oracle vs. Postgresql
Is there maybe any other??? -Ursprüngliche Nachricht- Von: Karel Zak [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 3. September 2003 16:53 An: Tom Lane Cc: Amin Schoeib; [EMAIL PROTECTED] Betreff: Re: AW: AW: [GENERAL] Localization (for dates) Oracle vs. Postgresql On Wed, Sep 03, 2003 at 10:36:29AM -0400, Tom Lane wrote: > "Amin Schoeib" <[EMAIL PROTECTED]> writes: > > When I would execute this: > > select to_char(now(),'DD.Month,') > > I would get the monthname in english but how can I perform with > > to_char That I become the monthname in german?? > > Karel, isn't there a way to get localized month names using to_char() > ? I thought there was, but I don't see anything about it in the > manual. No way:-( But I think it's good point to TODO of the 7.5 release. Karel -- Karel Zak <[EMAIL PROTECTED]> http://home.zf.jcu.cz/~zakkr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Localization (for dates) Oracle vs. Postgresql
First of all I want to thank you for your quick response. That would be very nice if it is possible. But using to_char I can only set the format or is it in Postgres Other? When I would execute this: select to_char(now(),'DD.Month,') I would get the monthname in english but how can I perform with to_char That I become the monthname in german?? -Ursprüngliche Nachricht- Von: Tom Lane [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 3. September 2003 16:21 An: Amin Schoeib Cc: [EMAIL PROTECTED] Betreff: Re: AW: [GENERAL] Localization (for dates) Oracle vs. Postgresql "Amin Schoeib" <[EMAIL PROTECTED]> writes: > I want to change for example for one session the date-format from > english to german, so that The month names for example march will be > März in German. I only need that for the monthnames. I think you can do this if you are willing to use to_char() to format the dates for display. There's no provision for such localization in the basic date or timestamp datatypes, though. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Restarting, ownership, and permissions
> "Richard" == Richard Huxton <[EMAIL PROTECTED]> writes: Richard> Hmm - so you can't "su - postgres" because as "admin" Richard> you'll need a password. Off the top of my head you Richard> could: One possibility given ssh access to the machine, from admin :- ssh [EMAIL PROTECTED] making sure that admin can ssh to postgres without a password or passphrase, Sincerely, Adrian Phillips -- Who really wrote the works of William Shakespeare ? http://www.pbs.org/wgbh/pages/frontline/shakespeare/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] What is the good equivalent for ENUM ?
> "SD" == Shridhar Daithankar <[EMAIL PROTECTED]> writes:
SD> On 3 Sep 2003 at 14:30, Bruno BAGUETTE wrote:
>> The problem is that this MySQL database uses ENUM, do you see what can I
>> do to migrate ENUM into PostgreSQL ?
SD> varchar with check constraints. Add constraits to allow only
SD> certain values of varchar string.
I used to do this. It turns out to be horribly inflexible when you
need to alter the enum values since the constraints cannot easily be
changed.
What I do is create a short table for the enum like this:
CREATE TABLE status_levels (
status varchar(10) PRIMARY KEY
) WITHOUT OIDS;
INSERT INTO status_levels (status) VALUES ('active');
INSERT INTO status_levels (status) VALUES ('overdue');
INSERT INTO status_levels (status) VALUES ('suspended');
INSERT INTO status_levels (status) VALUES ('terminated');
then reference it via foreign key from the "enum" field:
CREATE TABLE whatever (
...
status varchar(10) NOT NULL DEFAULT 'active' REFERENCES status_levels(status),
...
);
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.Khera Communications, Inc.
Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Trunc in Postgres
Look at the "trim" function. While you are about it, looking over the other available functions would be worth your while too (look under "Functions and Operators" in the docs). --- Amin Schoeib <[EMAIL PROTECTED]> wrote: > > Hi, > Is there an equivalent for the trunc function of > Oracle in Postgres??? > I need to trunc(the zeros) a number which is stored > as a char with a lot of zeros > Like that : 004 > > In oracle you can make that by > trunc(YOUR_COLUMNNAME,0) > > Thanxx > > Schoeib > > 4Tek Gesellschaft für angewandte > Informationstechnologien mbH > Schoeib Amin > Tel. +49 (0) 69 697688-132 > Fax. +49 (0) 69 697688-111 > http://www.4tek.de > > __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Recursive Selects
Varun Kacholia <[EMAIL PROTECTED]> writes: > I wanted to know whether recursive selects are supported in the latest version > of postgres. No. Some folks at Red Hat worked on it, but didn't finish in time for 7.4. It might show up in 7.5. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Querying $libdir
"Nigel J. Andrews" <[EMAIL PROTECTED]> writes: > On Wed, 3 Sep 2003 [EMAIL PROTECTED] wrote: >> I don't know of a way of querying $libdir directly, but you can find >> out its value using >> pg_config --libdir > LOL, can you say dimwit? I completely forgot about that even though I knew it > did that and indeed use it elsewhere. BTW, I think that the backend's $libdir actually corresponds to what pg_config calls --pkglibdir. They are the same directory in some configurations, but not all. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Trunc in Postgres
Your solution works in this example, but when
I take a columnname which type is char(30)
I beome the following error:
ERROR: Cannot cast type character to integer
-Ursprüngliche Nachricht-
Von: Pavel Stehule [mailto:[EMAIL PROTECTED]
Gesendet: Mittwoch, 3. September 2003 15:56
An: Amin Schoeib
Cc: [EMAIL PROTECTED]
Betreff: Re: [GENERAL] Trunc in Postgres
You can try
select CAST(cast('4' as NUMERIC(20)) AS varchar);
Pavel
>
> Hi,
> Is there an equivalent for the trunc function of Oracle in Postgres???
> I need to trunc(the zeros) a number which is stored as a char with a
> lot of zeros Like that : 004
>
> In oracle you can make that by trunc(YOUR_COLUMNNAME,0)
>
> Thanxx
>
> Schoeib
>
> 4Tek Gesellschaft für angewandte Informationstechnologien mbH Schoeib
> Amin Tel. +49 (0) 69 697688-132
> Fax. +49 (0) 69 697688-111
> http://www.4tek.de
>
>
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] identifying rows locked in row level locking
> I understand that ObjID of LOCKtag recognizes each individual row locked by a row level lock. BUt i have noticed that if i lock 2 different rows of the same table they have the same blkno. is this deduction ok? If they happen to be stored in the same block, they'd have the same blkno ... then how would we recognize each individual row locked? _ Help protect your PC: Get a free online virus scan at McAfee.com. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] identifying rows locked in row level locking
> I understand that ObjID of LOCKtag recognizes each individual row locked by a row level lock. BUt i have noticed that if i lock 2 different rows of the same table they have the same blkno. is this deduction ok? If they happen to be stored in the same block, they'd have the same blkno ... then how would we recognize each individual row locked? _ Get MSN 8 and enjoy automatic e-mail virus protection. http://join.msn.com/?page=features/virus ---(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] Using oids
On Wed, 2003-09-03 at 13:19, Martijn van Oosterhout wrote:
> But your insert function needs to know something about the table it's
> inserting into. The sequences have quite predicatable names. Besides, you
> can set the name yourself (DCL does this IIRC).
No it don't know anything about the table it insert into. I simply do
the following :
1. INSERT data (comming from another layer)
2. Get the last oid
3. SELECT * FROM the same table where oid = what I just found.
I know absolutly nothing about the table, and I like it this way :-)
> The only thing you need to know is the name of the primary key field. This
> many be a problem in a generic layer. If you like you can make a UNIQUE
> INDEX on the oid column and retry inserts when they fail.
Hmm, it all end up putting alot of information to a lower layer, and
this is sad as PG already knows, but it may not tell me.
> In your code, do create an index on the OID column? If not, that's be a
> performance hit,
I'm not sure what you mean !
> Something called CTID usually (AFAIK). It's a (block,tuple) pair. It changes
> whenever you do an update. Or a vacuum.
So no id for a row, but only for a row instance ? Is this the reason for
the growing index files ?
> If I were doing it would extract the primary key of each table on startup
> and then change that one line of code to:
>
> os << "SELECT * FROM " << sTable << " WHERE "
><< prikey << " = currval('" << sTable << "_" << prikey << "_seq')";
Thanks, but I have to be aware of the "prikey" name, and demand a prikey
for all tables to insert row into :-(
> Hope this helps,
I know what you mean, but I don't like the impact of the solution.
/BL
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] Using oids
On Wed, Sep 03, 2003 at 12:20:42PM +0200, Bo Lorentsen wrote:
> On Wed, 2003-09-03 at 11:38, Shridhar Daithankar wrote:
>
> > Well, what I do is, declare a serate sequence, retrive next available value and
> > explicitly insert it into a integer field. That avoids having to retrieve the
> > latest value again.
> Yeps, this is what I call an application specific implimentation, as one
> can't do this at a more genral layer (that does not know about your
> table layout).
But your insert function needs to know something about the table it's
inserting into. The sequences have quite predicatable names. Besides, you
can set the name yourself (DCL does this IIRC).
> Like having a general function that insert a row and return the newly
> inserted row, containing the defaults set by PG. My code contain this
> function (http://www.lue.dk/prj/dbc), and I have no way to make this
> work if I'm not able to fetch the oid after an insert, in some way.
The only thing you need to know is the name of the primary key field. This
many be a problem in a generic layer. If you like you can make a UNIQUE
INDEX on the oid column and retry inserts when they fail.
In your code, do create an index on the OID column? If not, that's be a
performance hit,
> > However historically oids were assumed to be 32 bit. There could be places
> > which unintentionally assumed it as such. Cleaning all those places is pretty
> > difficult given the big code base postgresql has.
> One day I will try to look after myself, but what does PG do internaly,
> when referring to rows in a unique way (lets say in an index) ?
Something called CTID usually (AFAIK). It's a (block,tuple) pair. It changes
whenever you do an update. Or a vacuum.
> > For large table containing billions of rows, Oids add to tuple size and overall
> > IO. If you are not using Oids, they become overhead. Ability to turn them off
> > is certainly nice..
> Yeps, if they really are not nessesary.
If I were doing it would extract the primary key of each table on startup
and then change that one line of code to:
os << "SELECT * FROM " << sTable << " WHERE "
<< prikey << " = currval('" << sTable << "_" << prikey << "_seq')";
Say you have a LAST_ID function and you a table with more than one sequence,
which would it return?
Hope this helps,
--
Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/
> "All that is needed for the forces of evil to triumph is for enough good
> men to do nothing." - Edmond Burke
> "The penalty good people pay for not being interested in politics is to be
> governed by people worse than themselves." - Plato
pgp0.pgp
Description: PGP signature
Re: [GENERAL] Using oids
On Wed, 2003-09-03 at 11:38, Shridhar Daithankar wrote: > Well, what I do is, declare a serate sequence, retrive next available value and > explicitly insert it into a integer field. That avoids having to retrieve the > latest value again. Yeps, this is what I call an application specific implimentation, as one can't do this at a more genral layer (that does not know about your table layout). Like having a general function that insert a row and return the newly inserted row, containing the defaults set by PG. My code contain this function (http://www.lue.dk/prj/dbc), and I have no way to make this work if I'm not able to fetch the oid after an insert, in some way. > I don't know if this is a widespread practice but I find it useful for more > than one way in the environment in which I program. You are not the only one recommenting this solution :-) > I understand. With growing use of 64 bit hardware, 4 billion will be history > pretty soon. Agreed ! > However historically oids were assumed to be 32 bit. There could be places > which unintentionally assumed it as such. Cleaning all those places is pretty > difficult given the big code base postgresql has. One day I will try to look after myself, but what does PG do internaly, when referring to rows in a unique way (lets say in an index) ? > If you compile postgresql with Oid as 64 bit integer, that will work in most > cases probably. However it does not guarantee that it will always work. There > always could be some places which assumed 32 bit data types. But if convertet to a string type, all involved places would fail, and no uncertency are involved when fixing it (well, a little too primitive argument, I know) :-) > You can request this to be a TODO to hackers. They will decide if this is worth > having it. Personally I support it the way it is. Optionally available. I'm not sure, if my skills reach this fare, but thanks for the advice. And for the rest --- we disagree :-) > For large table containing billions of rows, Oids add to tuple size and overall > IO. If you are not using Oids, they become overhead. Ability to turn them off > is certainly nice.. Yeps, if they really are not nessesary. /B ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Using oids
On 3 Sep 2003 at 11:28, Bo Lorentsen wrote: > On Wed, 2003-09-03 at 11:10, Shridhar Daithankar wrote: > > > Yes. It is correct. As of 7.3.x and onwards oids are optional at table creation > > times. They default to be available for new objects but that is for backwards > > compatibility I believe. In future, they would default to be not available for > > a particular object(hopefully). Right now you need to explicitly specify no > > oids while creating tables etc. > I do understand the limitation of the oid as implimented now, but why > remove the possibility to make unique row references (like after an > insert), instead of extenting the oid ? > > I don't care if they wrap, or is a unique string or anything else, as > long as I can use it to refetch a row after an insert, without keeping > track of app. implimentation specific SERIAL fields. Well, what I do is, declare a serate sequence, retrive next available value and explicitly insert it into a integer field. That avoids having to retrieve the latest value again. I don't know if this is a widespread practice but I find it useful for more than one way in the environment in which I program. > > > About oids not being unique, oids can assume 4 billion different values. If you > > have more than those many rows in a table, oids will wrap around and will no > > longer be unique in that object. > I see that this is a problem, and 4 billion is not alot, but why not > make another format like in oracle, so that it still is possible to > refere to a row using a unique --- thing. There must be an internal oid > somewhere, what we may be able to use. I understand. With growing use of 64 bit hardware, 4 billion will be history pretty soon. However historically oids were assumed to be 32 bit. There could be places which unintentionally assumed it as such. Cleaning all those places is pretty difficult given the big code base postgresql has. If you compile postgresql with Oid as 64 bit integer, that will work in most cases probably. However it does not guarantee that it will always work. There always could be some places which assumed 32 bit data types. That is one of the problem as I understand. (Gathered and extended from one of Tom's post. correct me if I am wrong) > > About oids being eliminated, I am sure it would happen some time in the future, > > looking at the development on this issue. Core team could elaborate more on > > this. > Sounds like a sad thing, if this is not replaced by something simular, > but more fit to a large DB. You can request this to be a TODO to hackers. They will decide if this is worth having it. Personally I support it the way it is. Optionally available. For large table containing billions of rows, Oids add to tuple size and overall IO. If you are not using Oids, they become overhead. Ability to turn them off is certainly nice.. Bye Shridhar -- Unfair animal names:-- tsetse fly -- bullhead-- booby -- duck-billed platypus-- sapsucker-- Clarence -- Gary Larson ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
