Re: [GENERAL] How to do?
mmm... I don't understand. The query brings a resultset just like the one you asked. When you say that 'there's no guarantee that A field is sorted or unique...', what do you mean? The query doesn't care about the "A" field, it just needs "UID" to be a candidate key. And I still don't understand what you need the row number for... On Tue, 2003-08-05 at 09:04, Robert Partyka wrote: At 20:05 03-08-01 -0300, you wrote: >This is the best I could come up with: > >SELECT >F1.a, F1.b, F1.uid >FROM >foo F1 >LEFT JOIN ( > SELECT uid FROM foo WHERE a>=(SELECT a FROM foo WHERE uid='AC88') > AND uid<>'AC88' ORDER BY a LIMIT 1 >) F2 ON (F2.uid=F1.uid) >LEFT JOIN ( > SELECT uid FROM foo WHERE a<=(SELECT a FROM foo WHERE uid='AC88') > AND uid<>'AC88' ORDER BY a LIMIT 1 >) F3 ON (F3.uid=F1.uid) >WHERE >F1.uid='AC88' OR >F2.uid IS NOT NULL OR >F3.uid IS NOT NULL > >I don't know how this query perfroms, but I'm sure it works :) > >Explained: >-F2 has the first record *after* AC88. >-F3 has the first record *before* AC88 >-The condition (the main WHERE) asks for the AC88 record itsself, or any >record where uid is not null (which are the ones brought by the left joins). > >Hope it helps... if it does not, ask again. > Almost it, but - there's no guarantee that A field is sorted or unique... :) because of that I ask how to get row number :) >On Fri, 2003-08-01 at 13:44, Robert Partyka wrote: >> >>Ron Johnson wrote: >> > No, but slightly ambiguous, at least for my old brain. >>I will try to by more unequivocal this time :) >> >>Shridhar Daithankar wrote: >> > select oid,name from a; >>I know it, but i have to have not oid's but row numbers :) such like : >> table "test" >> offset | value >>---+ >>1 |AC43 >>2 |AC4X >>3 |AX43 >>4 |ACX3 >> >>n |XC4A >> >>the best will be without using sequence :) >> >>Shridhar Daithankar wrote: >> > I didn't get that.. could you please elaborate? >> >>Franco Bruno Borghesi wrote: >> > And about the rows before and after that you ask, I don't understand... >>based on what you mean >> > *before* and *after*? you don't have an order by clause. >> >> > And what do you mean with "I know that in result is record with e.g. >>uid='AC13A1'"? >> > You know this uid *before* sending the query? is it part of your >statement>? can you use >> > this value as a hard coded condition for a subquery? >> >>Ok, so its goes something like that: >> >>lets say i have select query: select a,b,uid from foo where c='bar' order >>by a; >> >>with results like that: >> a | b | uid >>++-- >>2 |x | AC01 >>2 |w | AC43 >>4 |d | AC88 >>4 |a | AC13 >>... >>7 |c | AC22 >> >> >>and lets say I selected this before and I know that there is uid='AC88'; >> >>and in another connection (in lets say next requested www php script ) >>without selecting all >>this data or even full list of only uid`s and making sequence scan row by >>row I wont to get >>something like that from select I have write above: >> >> a | b | uid >>++-- >>2 |w | AC43 >>4 |d | AC88 >>4 |a | AC13 >>(3 rows) >> >>if there is row before and row next of uid='AC88' or >> >> a | b | uid >>++-- >>2 |w | AC43 >>4 |d | AC88 >>(2 rows) >>if uid='AC88' is last one row >> >>or >> >> a | b | uid >>++-- >>4 |d | AC88 >>4 |a | AC13 >>(2 rows) >>if uid='AC88' is first row >> >>I hope its more understandable than before :) >> >>regards >>Robert 'BoBsoN' Partyka >> >> >>---(end of broadcast)--- >>TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html signature.asc Description: This is a digitally signed message part
[GENERAL] tsearch2 on postgresql 7.3.4
I am trying to setup tsearch2 on postgresql 7.3.4 on a Redhat9 system, installed from rpms. There seemed to be some files required for installation of tsearch missing so I downloaded the src bundle too. Tsearch2 then compiled ok but now the command: psql mydb < tsearch2.sql fails with a message along the lines of: unable to stat $libdir/tsearch2 no such file I read up on valena.com about what $libdir was, but there's no mention of how to find out what the value of $libdior is. how do i find out where $libdir is for the current install? what files do i need to move into $libdir directory to get tsearch2 up and running? Thanks! -- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] v7.4 Beta 1 Bundle Available for Testing ...
On Tue, Aug 05, 2003 at 21:00:53 -0300, The Hermit Hacker <[EMAIL PROTECTED]> wrote: > > Just a quick note to everyone that v7.4 is now official in Beta Freeze, > with the first Bundle available for download, testing and bug reports ... http://developer.postgresql.org/beta.php doesn't point to the 7.4 betas but instead says to check back in a few months. ---(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] Error message: Ralation X does not have attribute Y
On 6 Aug 2003 at 14:34, Jan Oksfeldt Jonasen wrote: > Hi, > I'm a very new user of the PostgreSQL database, but I've quickly > encountered what I consider an pretty odd error. Let it be said, this is > using the PeerDirect PostgreSQL Beta 4 using Win2K SP3, so it's bound to > have certain issues. The following is from a simple console session with psql. > > pgtestdb=# CREATE TABLE SMT_PROPERTIES ( "PropertyKey" varchar (50) NOT > NULL,"PropertyValue" varchar (255) ) WITHOUT OIDS; > CREATE Why are you quoting column names? Is that required? What happens if you don't? Bye Shridhar -- understand, v.: To reach a point, in your investigation of some subject, at which you cease to examine what is really present, and operate on the basis of your own internal model instead. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] INSERT RULE QUERY ORDER
You are correct Tom, vtquotehistory is a view of tquotehistory, and I am
trying to log in this example. Sorry I didn't point that out. The view is a
straight view with no WHERE clause or criteria of any kind. The UPDATE does
cause the OLD record to become updated to whatever changes have been set as
needed in the NEW record. In that sense, the OLD record is discarded, but my
code sometimes assumes the keyword OLD is still a valid reference even after
an UPDATE. Is that my problem?
ARGGHH!!! I just re-read 9.2.1. Read Committed Isolation Level and I am
screwed! It was my understanding that all the queries in parenthesis in a
RULE were evaluated within a transaction, and that that meant that all
queries saw the same snapshot of the database and changes were committed all
at once at the end. I see now that queries within a transaction are only
ever free from seeing the COMMITs of _other_ transactions, and even that is
only for SERIALIZABLE transactions.
I've got over twenty rules with multiple updates within them that could be
affecting each other in ways I haven't evaluated. I've got my work cut out
for me. You're a lifesaver Tom, thanks.
---
jtocci
Fort Wayne, IN
PS - I can't abandon my beautiful rules for triggers just yet :-) With only
five pages of code, all rules, I've built a replacement for the DOS
application we use (soon to be 'used') to keep track of inventory,
shipping/receiving, purchasing and manufacturing('build' from a bill of
materials).
---
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Hm. Am I right in supposing that vtquotehistory is a view on
tquotehistory? Does the UPDATE cause the row that was visible in the
view to be no longer visible in the view (or at least not matched by the
constraints on the original UPDATE command)? If so, that's your problem
--- the "old" references in the INSERT will no longer find any matching
row in the view.
If your goal is to log operations on tquotehistory, my recommendation is
to forget about views and rules and just use a trigger on tquotehistory.
Triggers are *way* easier to understand, even if the notation looks
worse.
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] postgres+daemontools
On Wed, Aug 06, 2003 at 11:28:23 -0300, Kolus Maximiliano <[EMAIL PROTECTED]> wrote: > > I'm about to install postgres on a box that has daemontools on it > and I would like to use it. Is there anybody here already running postgres > from daemontools? Is it advisable? Do you have any problems or issues I > should took care before trying it? I find it much simpler to run multiple instances of postgres than using RH's init script. The run file I use is: #!/bin/sh exec 2>&1 exec setuidgid postgres /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] tsearch2: Error: Word is too long
Bad form to reply to my own posting i know but - I notice that the integer dictionary can accept MAXLEN for the longest number that is considered a valid integer. Can i set MAXLEN for the en dictionary to be the longest word i want indexed? I think i'd need to create a new dictionary...? > I tried to index my data with tsearch2 but I get the following error: > > Error: Word is too long > > I have looked through the docs and googled for "Word is too long" + > tsearch2 with no luck - so i hope i haven't missed something obvious. > > The problem is due to binary encodings in the data, base64 etc. > > Can i supply a max word length somewhere? > and how can I find out what the max limit is? > > Thanks! > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Error while running pg_dump
Hi, v7.3.3 $ pg_dump -Ft -v test1 | gzip > test1.pdmp.tar.gz pg_dump: saving database definition pg_dump: reading namespaces pg_dump: reading user-defined types [snip] pg_dump: dumping contents of table t_lane_tx2 pg_dump: dumping contents of table t_lane_tx pg_dump: [tar archiver] could not write to tar member (wrote 47, attempted 317) Has anyone seen this error before? TIA -- +-+ | Ron Johnson, Jr.Home: [EMAIL PROTECTED] | | Jefferson, LA USA | | | | "I'm not a vegetarian because I love animals, I'm a vegetarian | | because I hate vegetables!"| |unknown | +-+ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] postgres+daemontools
We recently started using daemontools to manage the herd of postmasters (see the recent thread regarding running multiple postmasters on a single machine), and its working quite well. We still have to figure out how to mimic the "pg_ctl stop -m fast|immediate" modes, svc -d /service/ mimics "pg_ctl stop -m smart" which may be all we need (I guess svc -i /service/; svc -d /service/" effectively gives the "fast" mode.) Murthy -Original Message- From: Kolus Maximiliano [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 06, 2003 10:28 To: [EMAIL PROTECTED] Subject: [GENERAL] postgres+daemontools I'm about to install postgres on a box that has daemontools on it and I would like to use it. Is there anybody here already running postgres from daemontools? Is it advisable? Do you have any problems or issues I should took care before trying it? Thanks in advance. ---(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] ext3 block size
Don't know the answer to your question, but I thought I would just pipe in and say that if this is an SMP (has multiple processors) Linux box you don't want to use ext3!!! I used ext3 on my SMP box here at work and now I can't have children (I guess it would help if I got a wife first)!! But in all seriousness SMP + ext3 = BAD(unpredictable crashes depending loosely related to system load). Wilson A. Galafassi Jr. wrote: hello. my database size is 5GB. what is the block size recommend? thanks wilson
Re: [GENERAL] plPHP -- sort of an announcement.. but not commercial
On Wed, 6 Aug 2003, Peter Eisentraut wrote: > scott.marlowe writes: > > > Do we need official permission to call the language plPHP by the way? > > Can someone explain to me why language handler modules for PostgreSQL are > always called "PL/Language"? Consider if someone wrote a language binding > for Scheme, then calling that "procedural language Scheme" sounds like an > insult to me. vs calling them ... what? :) For lack of any other suggestion, they are always called PL/Language? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] pg_xlog question
> > If you could replicate/rsync pg_xlog to another machine, you could get it > > correctly in case of failover. > > Argh.. I forgot.. Simple option is to sync the pg_xlog while doing failover. It > might add some 10 odd sec to failover process but should be bullet proof.. > > I don't know if PG is nose poking about permissions of those files. otheriwse > it might refuse to start.. Check out.. > > Worth adding to failover HOWTO. I doubt if one exists for postgresql. Thanks for the response, but syncing pg_xlog separatly (without DRBD device) is useless, because a failover will take place when there's an error, so the extra sync of pg_xlog is very likely to fail. Regards, Mario Weilguni ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Error message: Ralation X does not have
On Wed, Aug 06, 2003 at 03:06:12PM +0100, Jan Oksfeldt Jonasen wrote: > At 01:40 PM 8/6/2003, Peter Eisentraut wrote: > >You need to double-quote the names or they will be converted to lower > >case. > > > > Thank you both. The reason why the initial table creation have quoted > columns names is that it's generated by a program we use for schema > creation. I'm currently adding to it so it can support Postgres too and the > way columns are set up was more or less copied from the MS Sql > implementation. > > I'm really not used to databases being so case sensitive, neither Oracle or > MS Sql Server is that, but I'll keep this thing in mind moving along. Quite > impressed with the response time, or maybe it was just a too easy question > :-) Well, it's only case-sensetive if you ask for it, ie by quoting. The basic rule is: Either always quote or never quote. Always quote = case-sensetive Never quote = case-insensetive -- 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] Error while running pg_dump
Ron Johnson <[EMAIL PROTECTED]> writes: > $ pg_dump -Ft -v test1 | gzip > test1.pdmp.tar.gz > pg_dump: saving database definition > pg_dump: reading namespaces > pg_dump: reading user-defined types > [snip] > pg_dump: dumping contents of table t_lane_tx2 > pg_dump: dumping contents of table t_lane_tx > pg_dump: [tar archiver] could not write to tar member (wrote 47, > attempted 317) > Has anyone seen this error before? Sounds like this: http://archives.postgresql.org/pgsql-bugs/2003-08/msg00025.php If you're not suffering from /tmp overflow, let us know. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] pg_dump corrupts database?
I've encountered this a few times with 7.2 and 7.3. If I do pg_dump of some large (> 100Mb - the bigger the more likely) database, and it gets interrupted for some reason (e.g. the target disk fills up), the source database become corrupt. I start getting errors like: open of /var/lib/pgsql/data/pg_clog/0323 failed: No such file or directory and I have to drop/restore the table in question. Is this a known problem? Is there some safe way to dump databases that avoids it? Stephen -- Stephen Robert Norris <[EMAIL PROTECTED]> CommSecure Australia Pty Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] migrating data from 7.3.x down to 7.2.x
Hello, You really don't want to do this. 7.3.x is much more stable, offers better support for various things and handles load much better. If you need a 7.3.x host look at (plug) www.commandprompt.com or even phHoster.com J Ian Barwick wrote: On Tuesday 15 July 2003 09:51, Stefan Armbruster wrote: Hi, Am Mon, 2003-07-14 um 21.01 schrieb Ian Barwick: On Monday 14 July 2003 16:04, Stefan Armbruster wrote: Hi, I tried to migrate a database from 7.3.2 down to PostgreSQL 7.2.2 Any particular reason? Yes, I'm developing with 7.3.2, production is 7.2.2. Not hostsharing.net by any chance? (...) In other words: a short script with some cut & replace operations could do the job? Yes, although depending on your data it might take a bit of trial and error. Is there a specific document describing all the DDL changes from 7.2 to 7.3?ßß The release notes: http://www.postgresql.org/docs/7.3/static/release.html#RELEASE-7-3 are usually a good starting point. Ian Barwick [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Changing DB ownership
Hi, > Why would you want to do that? > > Why not do it an easier way and dump the database and restore it into > your new database? > > There's got to be a lot of stuff to consider when doing something as > radical as renaming a database. He is not talking about renaming his database, he is talking about changing the OWNER of the database. > >UPDATE pg_database SET datdba = 504 WHERE datname='chris'; This is how I change the owner of the database too. It's not that diffucult, but it would be nice if it could be changed using an ALTER statement. I have noticed in the past that the dumps produced by pg_dump are difficult to restore if the datdba you change to has no rights to create databases. I haven't tested this with recent releases though. I suspect that this has already been fixed in pg_dump. Bye, Sander. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Fatal error: Call to undefined function: pg_connect()
Hi All, I updated postgre from 7.2.4 to 7.3.2 in my RHLinux7.3 system and updated current pghba.conf and postgresql.conf files as per my need. But If I try to connect through PHP which was connecting earlier with 7.2.4 is giving the following error Fatal error: Call to undefined function: pg_connect() Can any body encounter the same problem earlier can help me that in solving. Thanks alot, Sreedhar Bhaskararaju ---(end of broadcast)--- TIP 8: explain analyze is your friend
