[GENERAL] Pgcluster 1.7 Fail safe !!!

[Trinath Somanchi]

Hi All ,

I have successfully installed pgcluster 1.7 rc7 and it is working fine
. But i have got a doubt on " Single Point Failure " . 

I need a suggest from u all on this .

For an Application which demands High Availability and  no Single Point
Failure and built Multi-Master Replication system  , is PGCLUSTER - II 
a suitable option.

Please suggest/support this issue .

Thanks in advance .

Best Regards,
-- 




begin:vcard
fn:Trinath Somanchi
n:Somanchi;Trinath
org:Intoto Software (I) Pvt Ltd;Process Tools
adr:Panjagutta;;4th floor UMA Plaza , Nagarjuna hills,;Hyderabad;Andhra Pradesh;500082;India
email;internet:[EMAIL PROTECTED]
title:Software Engineer
tel;cell:+91 9866365130
x-mozilla-html:TRUE
version:2.1
end:vcard


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[GENERAL] do you have an easy example of postgis and mapserver?

[Ottavio Campana]

Hi,

I'm sorry this mail is not very in topic, but I hope you can help me.

I'm trying to learn how postgis and mapserver work together, but I
cannot understand nearly anything. I mean, I read the documentation of
postgis and I think I understood it, but I cannot do anything useful
with it.

What I'd like to do is storing some polygons in a database and writing
some scripts in python which extract those polygons and draw an image,
given a zoom level and a position.

Do you know some good documentation? Or a working example would be great.

Thank you.



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] Solution to Bus error(coredump) from postgres binary

[Alvaro Herrera]

Jayaprakash, Sowmiya Lakshmi (STSD) wrote:
> Hi,
> 
> I'm in the process of building Postgres 7.4.2 on a HP-UX PA Machine.

Why are you trying 7.4.2 and not 7.4.17?  Or even 8.2.4, really.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] do you have an easy example of postgis and mapserver?

[Bill Thoen]

On Tue, Aug 21, 2007 at 12:44:49PM +0200, Ottavio Campana wrote:
> I'm sorry this mail is not very in topic, but I hope you can help me.

Just so you know, perhaps a better list to contact with this is the
MapServer mailing list at
http://lists.umn.edu/archives/mapserver-users.html, or maybe the PostGIS
list at http://www.postgis.org/mailman/listinfo/postgis-users.

> I'm trying to learn how postgis and mapserver work together, but I
> cannot understand nearly anything. I mean, I read the documentation of
> postgis and I think I understood it, but I cannot do anything useful
> with it.

I think before you try to do something customized with python you should
get familiar the mapserver MAP file which contains all the instructions to
map features from various sources like shape files, MapInfo TAB files,
PostGIS data sources, and so on. An example for mapping a layer from a
PostGIS source in a MAP file looks like this:

  LAYER
NAME states
TYPE POLYGON
STATUS ON
CONNECTION "user=gisuser password=*** dbname=us_data host=localhost
port=5432"
CONNECTIONTYPE POSTGIS
DATA "the_geom from states"
CLASSITEM 'name'
PROJECTION
  "+proj=latlong +datum=WGS84"
END
CLASS
  NAME "US States"
  OUTLINECOLOR 0 0 0
  COLOR 255 255 196
END
  END

The key bits here related to PostGIS are the CONNECTION, where you specify
all the info needed to get access to your PostgreSQL database, the
CONNECTIONTYPE which you set to POSTGIS, and the DATA directive in which
you supply a string that contains the SQL expression that pulls out the
geometry features you want to map.

If you do this with python, then you would first need to create an instance
of a mapObj, and then create a layerObj and add it to the map object,
setting the properties for the connection. However, my knowledge of this
approach is still very sketchy.

Documentation is spread around, but the two sites you should peruse are
the MapServer docs at: http://mapserver.gis.umn.edu/docs and the PostGIS docs
at: http://postgis.refractions.net/docs/.

- Bill Thoen


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Using oid as pkey

[hubert depesz lubaczewski]

On Mon, Aug 20, 2007 at 07:00:32PM -0500, D. Dante Lorenso wrote:
> Exactly what I was looking for.  Looks like I need to make moves to get 
> from 8.1 onto 8.2 ;-)

in any pg you should simply use select currval('sequence_name'); and be
happy with it.

depesz

-- 
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA.  here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

[GENERAL] Audit-trail engine inner-workings

[Marcelo de Moraes Serpa]

Hello list,

Taking this discussion as a base:

http://archives.postgresql.org/pgsql-general/2007-04/msg01034.php

Manuel helped me to develop an audit-trail engine, and even though it works
ok, I need to know better its inner workings:

>From what I understand, the flow is something like this:
   * User sends http request to server to update some record (for example);
   * The application server (tomcat) runs the servlet and the servlet gets a
connection from the pool
   * The servlet runs the "set_session_id(integer)" to set a session for
this request and saves the current application user in the lookup table
(session_id,user_id)
   * The db operation is done (db saved) - the user name is retrieved
through a lookup into the table mentioned above by getting this request's
session_id by running the "current_session_id" SP

* Each http request gets an instance of a session_id ? Or is it
per-connection ?
* Do I really need to call reset_session_id ? Isn't this connection
destroyed after it has been used by the application?
* Would it work with an application which does not use a connection pool but
a permanent connection (such as desktop apps)?

Thanks in advance!

Marcelo.

Re: [GENERAL] Audit-trail engine inner-workings

[Ted Byers]

--- Marcelo de Moraes Serpa <[EMAIL PROTECTED]>
wrote:

> Hello list,
> [snip] 
> 
> * Each http request gets an instance of a session_id
> ? Or is it
> per-connection ?

It depends.  There is no necessary correlation between
a session as defined within a web application and a
session as defined in the RDBMS.  I routinely set up
web applications that may have multiple "sessions" as
seen by the RDBMS.  Consider the idiom of doing
operations with the least priviledge required to get
the job done.  The application might log into the
database as one databse user with very limited rights,
to authenticate the user and pass data to the web
application regarding what the user is permitted to do
(all server side, on the application server).  Then,
the application may log in as a very different user
with limited rights to perform some operation the user
has initiated.  So far, we have two sessions as far as
the database is concerned and only one as far as the
web application is concerned.

If you're working with web applications, you must know
that multiple http requests can share a web
application session, as can multiple web applications
(if written to do so using the single sign-on idiom),
assuming you use technologies such as cookies or URL
rewriting or hidden form fields, or the magic of
ASP.NET, to set up and maintain sessions, and that the
session is restricted to a single http request if you
don't (plain old http/html is stateless, so there is
no useful concept of session without help from other
technologies).

HTH

Ted


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] SELECT question

[Michelle Konzack]

Am 2007-08-17 12:53:41, schrieb Michael Glaesemann:
> 
> On Aug 17, 2007, at 7:27 , Michelle Konzack wrote:
> 
> >*
> >* Do not Cc: me, because I am on THIS list, if I write here.*
> 
> You might want to consider changing your mailing list subscription  
> settings to "eliminatecc", e.g., send email to
> [EMAIL PROTECTED] (not the list address!) with body
> 
> set pgsql-general eliminatecc
> 
> This should prevent the mailing list from sending you a second copy.

Which mean, my "INBOX.ML_pgsql.general/" will never receive
messages and break all threads where someone send me CC's...

> I think what you want is something like:
> 
> SELECT DISTINCT ON (website_reference) website_reference,  
> download_date, file_path
> FROM indextable
> WHERE download_date <= ? -- whatever date you're interested in
> ORDER BY website_reference, download_date DESC;
> 
> This should return the most recent website_reference and its  
> download_date that's earlier than the download_date specified in the  
> WHERE clause.
> 
> DISTINCT ON is a (very helpful) PostgreSQL extension. You can get  
> similar results using a subquery;

I have never used "DISTINCT ON" (it was not known to me)
and was trying subqueries...  :-/

> SELECT website_reference, download_date, file_path
> FROM indextable
> NATURAL JOIN (
> SELECT website_reference, max(download_date) as download_date
> FROM indextable
> WHERE download_date <= ?
> GROUP BY website_reference
> ) most_recent_versions;
> 
> This may return more than one row per website_reference if the  
> website_reference has more than on file_path for a particular  
> download_date.
> 
> Does this help? If not, could you give a bit more of a concrete example?

I have an Enterprise which do researches  :-)  and I have a local cache
of more then 150.000.000 URL's and its content (~8 TByte)...  (I have
hit over 2000 md5 collisons and now using sha384)  Also I get per day
nearly 100.000 new files...

OK, HTML pages are downloaded and go into the first table like

indextable  FULL_URL, SHA384SUM

and the second table

content SERNUM (uniq), SHA384SUM (pri), LOCAL_PATH

the saved file get as the name the SHA384 name

If I open an HTML-URL with a specific date, it is parsed and the URL's
inline are adapted to make it work from my database, e.g.

http://www.postgresql.org/index.html

will become


http://webcache/show.php?date=123456789&url=http://www.postgresql.org/index.html

inline elements and already downloaded other links will bekome

http://webcache/show.php?date=123456789&url=

Thanks to the PostgreSQL developers that they have created "tablespace"
and "table partitioning" since searching in 150.000.000 ROW's is the
hell.  

> (Is is just me or have there been a lot of queries that can be solved  
> using DISTINCT ON recently?)

I do not know...  Since when does "DISTINCT ON" exist?

Greetings
Michelle Konzack
Systemadministrator
Tamay Dogan Network
Debian GNU/Linux Consultant


-- 
Linux-User #280138 with the Linux Counter, http://counter.li.org/
# Debian GNU/Linux Consultant #
Michelle Konzack   Apt. 917  ICQ #328449886
   50, rue de Soultz MSN LinuxMichi
0033/6/6192519367100 Strasbourg/France   IRC #Debian (irc.icq.com)


signature.pgp
Description: Digital signature

Re: [GENERAL] SELECT question

[Scott Marlowe]

On 8/18/07, Michelle Konzack <[EMAIL PROTECTED]> wrote:
> Am 2007-08-17 12:53:41, schrieb Michael Glaesemann:

> > (Is is just me or have there been a lot of queries that can be solved
> > using DISTINCT ON recently?)
>
> I do not know...  Since when does "DISTINCT ON" exist?

I have been lurking on this thread, don't have much to add, but
distinct on has been around in postgresql for as long as I can
remember.  It's not SQL spec, so it's not portable.  but it is
awefully useful.

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] SELECT question

[Alvaro Herrera]

Michelle Konzack wrote:
> Am 2007-08-17 12:53:41, schrieb Michael Glaesemann:
> > 
> > On Aug 17, 2007, at 7:27 , Michelle Konzack wrote:
> > 
> > >*
> > >* Do not Cc: me, because I am on THIS list, if I write here.*
> > 
> > You might want to consider changing your mailing list subscription  
> > settings to "eliminatecc", e.g., send email to
> > [EMAIL PROTECTED] (not the list address!) with body
> > 
> > set pgsql-general eliminatecc
> > 
> > This should prevent the mailing list from sending you a second copy.
> 
> Which mean, my "INBOX.ML_pgsql.general/" will never receive
> messages and break all threads where someone send me CC's...

So have messages which have the list on CC go to that folder as well,
:0:
* [EMAIL PROTECTED]
INBOX.ML_pgsql.general/

Another choice you have is to tell mutt that you're subscribed to the
list, adding to .muttrc

lists pgsql-general@postgresql.org
subscribe pgsql-general@postgresql.org

then it will generate the Mail-Followup-To: header, omitting your
address, so you will only get it via Majordomo.  (It will still fail
sometimes if older MUAs don't honor that field).  I found it annoying
so I disabled it by setting
set followup_to=no

Another thing I did was removing of duplicates with procmail,
:0 Wh: msgid.lock
| formail -D 65536 $HOME/.msgid.cache

so I don't get two copies when I'm on the list and on CC.

-- 
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
Management by consensus: I have decided; you concede.
(Leonard Liu)

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Question on INDEX and SQL - stalling my database?

[Richard Huxton]

Rishi Daryanani wrote:

I'm having problems with a query that's just
"stalling" my database. If someone could help me out -
I posted a forum topic on 
http://forums.devshed.com/postgresql-help-21/postgresql-new-index-pros-and-cons-467120.html


Did you get any advice from that forum? Was it helpful?


There's just this one integer field, which when
searched on, "stalls" my query and I don't get any
results. I tried adding an index to that integer, but
that didn't seem to help..


Don't get any results? Ever? If so, adding an index wouldn't help.

What you'll need to supply is:
1. The query
2. Details of the table(s) - number of rows, number of distinct values 
in columns being matched against

3. Output of EXPLAIN ANALYZE for your query
4. A couple of details of your server (RAM, number of disks, processors etc)

I'm sure someone here will be able to help then.

--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

[GENERAL] Need help doing a PostgreSQL vs Firebird feature comparison

[Tony Caduto]

Hi,

I was just wondering if anyone could help me out by taking a look to see 
if I missed any important features.


http://www.amsoftwaredesign.com/pg_vs_fb

This comparison is going to be for the benefit of Delphi users.  The 
Delphi community is heavily biased to Firebird.


Please post any comments or suggestions here:

http://www.amsoftwaredesign.com/smf/index.php?topic=138.0

You don't need to register.

Thanks,

Tony

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] Question on INDEX and SQL - stalling my database?

[Harald Armin Massa]

Rishi,

I looked up that thread

1st:) p.s. I am using PostgreSQL 7.4.17

Any reason for that? Actual version is 8.2.4; or at least 8.1.9

2nd) your query is:

SELECT DISTINCT c.*
FROM customer c
LEFT OUTER JOIN weborders w
ON c.username = w.username
WHERE   w.username IS NULL  
AND c.sourceid IS NOT NULL
AND c.usertype = 0
AND c.emailrestrict = 1
ORDER BY c.addcountrycode, c.surname, c.initials


so you force the poor database to do an left outer join just to find the
outer records? Why?

I would start with:


select c.* from customer c
where
c.sourceid IS NOT NULL
AND c.usertype = 0
AND c.emailrestrict = 1
and
c.username not in (select username from weborders)


BTW: I have a BAD feeling that your relation seems to be on "USERNAME",
which should make it a primary key on "customer" - and a user entered
primary key is a bad idea 104% of the time.

you can also save on the distinct, as every customer should have exactly one
line in table customer. If not, you should dump that complete database and
start from scratch.

Harald

On 8/18/07, Rishi Daryanani <[EMAIL PROTECTED]> wrote:
>
> Hi all,
>
> I'm having problems with a query that's just
> "stalling" my database. If someone could help me out -
> I posted a forum topic on
>
> http://forums.devshed.com/postgresql-help-21/postgresql-new-index-pros-and-cons-467120.html
>
> There's just this one integer field, which when
> searched on, "stalls" my query and I don't get any
> results. I tried adding an index to that integer, but
> that didn't seem to help..
>
> Please let me know if there's any solution to this.
> Any help would be really appreciated!
>
> Many thanks!
>
>
>
> Ready
> for the edge of your seat?
> Check out tonight's top picks on Yahoo! TV.
> http://tv.yahoo.com/
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
>



-- 
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
fx 01212-5-13695179
-
EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!

[GENERAL] Array with Subselect / ANY - cast?

[Josh Trutwin]

Hi - I have the following array field:

SELECT pb_ids FROM pb WHERE id = 123:

 pb_id  
---
 {196,213,215,229,409}

These numbers map to a productid in tblproducts so I figured I could
do this:

SELECT * 
  FROM tblproducts 
 WHERE productid = ANY (
   SELECT pb_ids FROM pb WHERE id=123
 );

This complains: "ERROR:  operator does not exist: integer =
integer[]".

This doesn't seem much different than the example in the docs: 

SELECT * FROM sal_emp WHERE 1 = ANY (pay_by_quarter);

If I do this:

SELECT * 
  FROM tblproducts 
 WHERE productid = ANY ('{196,213,215,229,409}'});

The query runs fine.

Any ideas on how to make this work?

Thanks!

Josh

P.S.  Postgres 8.1.9 on Linux

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Array with Subselect / ANY - cast?

[Pavel Stehule]

Hello

2007/8/21, Josh Trutwin <[EMAIL PROTECTED]>:
> Hi - I have the following array field:
>
> SELECT pb_ids FROM pb WHERE id = 123:
>
>  pb_id
> ---
>  {196,213,215,229,409}
>
> These numbers map to a productid in tblproducts so I figured I could
> do this:
>
> SELECT *
>   FROM tblproducts
>  WHERE productid = ANY (
>SELECT pb_ids FROM pb WHERE id=123
>  );
>

SELECT * FROM ... WHERE 1000 IN (SELECT pb_ids FROM pb WHERE id=123)

or

SELECT * FROM ... WHERE 1000 = ANY(ARRAY(SELECT pb_ids FROM pb WHERE id=123))


Pavel

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] history table

[Robin Helgelin]

Hi,

I want to save history for a few tables using triggers on update and
creation. What's the best approach to do this in a webapp environment
where I want to save which webapp user that is doing the change, not
the postgresql user?

-- 
regards,
Robin

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Array with Subselect / ANY - cast?

[Josh Trutwin]

On Tue, 21 Aug 2007 20:15:59 +0200
"Pavel Stehule" <[EMAIL PROTECTED]> wrote:

> SELECT * FROM ... WHERE 1000 IN (SELECT pb_ids FROM pb WHERE id=123)
> 
> or
> 
> SELECT * FROM ... WHERE 1000 = ANY(ARRAY(SELECT pb_ids FROM pb
> WHERE id=123))

Thanks - another way:

SELECT * FROM ... WHERE 1000 = ANY((SELECT pb_ids FROM pb
WHERE id=123)::integer[])

Josh

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] history table

[A. Kretschmer]

am  Tue, dem 21.08.2007, um 20:20:38 +0200 mailte Robin Helgelin folgendes:
> Hi,
> 
> I want to save history for a few tables using triggers on update and
> creation. What's the best approach to do this in a webapp environment
> where I want to save which webapp user that is doing the change, not
> the postgresql user?

Maybe tablelog.

20:49 < akretschmer> ??tablelog
20:49 < rtfm_please> For information about tablelog
20:49 < rtfm_please> see 
http://ads.wars-nicht.de/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html
20:49 < rtfm_please> or http://pgfoundry.org/projects/tablelog/


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] history table

[Guy Rouillier]

Robin Helgelin wrote:

Hi,

I want to save history for a few tables using triggers on update and
creation. What's the best approach to do this in a webapp environment
where I want to save which webapp user that is doing the change, not
the postgresql user?



Well, you haven't told us much about your webapp.  Are you using 
connection pooling?  If so, then you'll need to provide the webapp 
userid as an additional parameter to your database updates.  If you are 
not using connection pooling, such that your webapp userids are 
connecting as themselves, then the problem becomes much easier; you've 
got the correct userid to log by just looking at the connection details.


--
Guy Rouillier

---(end of broadcast)---
TIP 1: 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] Array with Subselect / ANY - cast?

[Michael Glaesemann]

On Aug 21, 2007, at 12:49 , Josh Trutwin wrote:


SELECT pb_ids FROM pb WHERE id = 123:

 pb_id
---
 {196,213,215,229,409}

These numbers map to a productid in tblproducts so I figured I could
do this:

SELECT *
  FROM tblproducts
 WHERE productid = ANY (
   SELECT pb_ids FROM pb WHERE id=123
 );


Out of curiosity, what led to the schema design of storing these  
pb_id values in an array rather than in a many-to-many table? You're  
working against the database server here. The usual way to define  
this relationship would be


CREATE TABLE pb (id INTEGER PRIMARY KEY);
CREATE TABLE pb_ids
(
id INTEGER NOT NULL REFERENCES pb
, pb_id INTEGER NOT NULL
REFERENCES tblproducts (pb)
, PRIMARY KEY (id, pb)
);

(if I've interpreted the column and table names correctly)

Then your query reduces to a simple
SELECT *
FROM tblproducts
JOIN pb_ids ON (pb_id = pb)
WHERE id = 123;

This reduces the query to straight-forward SQL (which is set based)  
rather than wrangling arrays (which are really better considered  
opaque from the standpoint of database schema design) and enables  
referential integrity using built-in foreign key constraints rather  
than requiring custom triggers (to make sure each element of the  
pb_id array corresponds to a pb value in tblproducts).


Michael Glaesemann
grzm seespotcode net



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] history table

[Robin Helgelin]

On 8/21/07, Guy Rouillier <[EMAIL PROTECTED]> wrote:
> Well, you haven't told us much about your webapp.  Are you using
> connection pooling?  If so, then you'll need to provide the webapp
> userid as an additional parameter to your database updates.  If you are
> not using connection pooling, such that your webapp userids are
> connecting as themselves, then the problem becomes much easier; you've
> got the correct userid to log by just looking at the connection details.

Yes, this is where I'm too new to postgresql, how do I tell the
database which user is logged in to the webapp? A session parameter?
There will be connection pooling, but if I know how to solve the
previous question I don't think it's hard to get it working with the
pool.

-- 
regards,
Robin

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] Converting non-null unique idx to pkey

[Ed L.]

I'm preparing a fairly large 7.4.6 DB for trigger-based 
replication.  I'm looking for ways to minimize my impact on the 
existing schema & data and uptime.  This replication solution 
requires every table to have a primary key.  Rather than adding 
a new key column and index for the pkey, it's appealing to just 
to reuse existing unique indices on non-null columns.  Are there 
are any known or obvious gotchas associated with transforming a 
unique index on a non null column into a primary key via this 
sql?

update pg_index 
set indisprimary = 't' 
where indexrelid = 

TIA.
Ed

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Array with Subselect / ANY - cast?

[Josh Trutwin]

On Tue, 21 Aug 2007 14:19:03 -0500
Michael Glaesemann <[EMAIL PROTECTED]> wrote:

> Out of curiosity, what led to the schema design of storing these  
> pb_id values in an array rather than in a many-to-many table?
> You're working against the database server here. The usual way to
> define this relationship would be

 

Yeah - I'm a big proponent of doing things the relational way but for
this application it seemed like the right thing (after much
debating) to do at the time and it's been working well so far.  The
app is kind of an object builder that lets any joe schmoe create an
object like a student, so you can create a field called fname, lname,
etc. and also fields that contain lists of values (phone numbers,
majors, etc) - it's a simple example but the number of list-type
fields is anticipated to be VERY high so I decided to deal with
arrays instead of creating all sorts of normalized tables. 
Was it the right thing to do?  I don't know yet but it's working so
far. It gets interesting when you set a field that links to another
object - say you have a class object and a student object and you
want to say student has many classes - the array in this case store
the class id pks but it's not a foreign key as arrays don't do that
so I had to put some triggers (as you mentioned) to keep things in
line (if a class is deleted for example). These are really poor
examples because anyone who does an app for students/classes creates
normalized tables (at least for oltp) but the app we're building gives
the end user the flexibility to create whatever they can think of.  

The meta data that this app uses to store information about the
objects the user is building is all stored in normalized tables, but
the data for the objects themselves I wanted to keep as wide as
possible.

So yeah, arrays are a PITA but I think for this it'll work, time will
tell...

Josh 

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Array with Subselect / ANY - cast?

[Pavel Stehule]

2007/8/21, Josh Trutwin <[EMAIL PROTECTED]>:
> On Tue, 21 Aug 2007 20:15:59 +0200
> "Pavel Stehule" <[EMAIL PROTECTED]> wrote:
>
> > SELECT * FROM ... WHERE 1000 IN (SELECT pb_ids FROM pb WHERE id=123)
> >
> > or
> >
> > SELECT * FROM ... WHERE 1000 = ANY(ARRAY(SELECT pb_ids FROM pb
> > WHERE id=123))
>
> Thanks - another way:
>
> SELECT * FROM ... WHERE 1000 = ANY((SELECT pb_ids FROM pb
> WHERE id=123)::integer[])
>

it works? no.

pavel=# select 1 from (values(10)) a(i)  where i = any((select * from
foo)::int[]);
ERROR:  cannot cast type integer to integer[]
LINE 1: ...values(10)) a(i)  where i = any((select * from foo)::int[]);
^
pavel=#

---(end of broadcast)---
TIP 1: 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] history table

[Ed L.]

On Tuesday 21 August 2007 1:22 pm, Robin Helgelin wrote:
>
> Yes, this is where I'm too new to postgresql, how do I tell
> the database which user is logged in to the webapp? A session
> parameter? There will be connection pooling, but if I know how
> to solve the previous question I don't think it's hard to get
> it working with the pool.

Tablelog looks pretty cool.  One way to handle your user ID issue 
would be to initiate a user session by storing a session record 
(for example:  id, username, starttime), then have your app pass 
that session ID to your updates for history.  Then you could 
store the user ID in an update_session_id column and tablelog 
would help track of the history.

Ed


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Need help doing a PostgreSQL vs Firebird feature comparison

[Dmitry Koterov]

One difference in SQL syntax is that FireBird could join stored procedures
like this:

SELECT b.*
FROM
  get_ids() a
  LEFT JOIN get_data(a.ID) ON 1=1

(where a.ID parameter is passed from the previous set as a next procedure
parameter), but Postgres cannot.



On 8/21/07, Tony Caduto <[EMAIL PROTECTED]> wrote:
>
> Hi,
>
> I was just wondering if anyone could help me out by taking a look to see
> if I missed any important features.
>
> http://www.amsoftwaredesign.com/pg_vs_fb
>
> This comparison is going to be for the benefit of Delphi users.  The
> Delphi community is heavily biased to Firebird.
>
> Please post any comments or suggestions here:
>
> http://www.amsoftwaredesign.com/smf/index.php?topic=138.0
>
> You don't need to register.
>
> Thanks,
>
> Tony
>
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
>
>http://archives.postgresql.org/
>

Re: [GENERAL] Array with Subselect / ANY - cast?

[Josh Trutwin]

On Tue, 21 Aug 2007 21:36:00 +0200
"Pavel Stehule" <[EMAIL PROTECTED]> wrote:

> it works? no.
> 
> pavel=# select 1 from (values(10)) a(i)  where i = any((select *
> from foo)::int[]);
> ERROR:  cannot cast type integer to integer[]
> LINE 1: ...values(10)) a(i)  where i = any((select * from
> foo)::int[]); ^
> pavel=#

oops - forgot to send to list:

This is the exact query I ran which produces a result set:

select * from tblproducts 
where productid = ANY(
   (select pb_correspondence from pb.pb_nurse
 where id = 140)::integer[]
) 
order by productid;

Your query looks similar - maybe it's the "select *" in the subquery?

Either way I actually found a better way to do what I was trying to
do here so no worries.  :)

Josh

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] history table

[Ed L.]

On Tuesday 21 August 2007 1:42 pm, Ed L. wrote:
> Then you could
> store the user ID in an update_session_id column and tablelog
> would help track of the history.

s/user ID/session ID/g;

Ed


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Converting non-null unique idx to pkey

[Scott Marlowe]

On 8/21/07, Ed L. <[EMAIL PROTECTED]> wrote:
>
> I'm preparing a fairly large 7.4.6 DB for trigger-based
> replication.  I'm looking for ways to minimize my impact on the
> existing schema & data and uptime.  This replication solution
> requires every table to have a primary key.  Rather than adding
> a new key column and index for the pkey, it's appealing to just
> to reuse existing unique indices on non-null columns.  Are there
> are any known or obvious gotchas associated with transforming a
> unique index on a non null column into a primary key via this
> sql?

What replication system is this?  Slony only requires a unique key on
a not null column.  If that's what you're using, see if alter it to
not null will get around this. Note you may have to update any null
values to something else first.

If you have a large db in 7.4.6, you should do two things.

1: Update to 7.4.19 or whatever the latest flavor of 7.4 is, right
now.  There are a few known data eating bugs in 7.4.6.
2: Start planning a migration to 8.2.4 now.  Start implementing it as
soon after that as you can.
3: Slony allows you to migrate from old to new versions, but only the
older versions of slony support 7.4.

>
> update pg_index
> set indisprimary = 't'
> where indexrelid = 

I wouldn't bet on that working right.  Others know the internals of
the db better than me, but that looks like a foot gun.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Need help doing a PostgreSQL vs Firebird feature comparison

[Tony Caduto]

Dmitry Koterov wrote:
One difference in SQL syntax is that FireBird could join stored 
procedures like this:


SELECT b.*
FROM
  get_ids() a
  LEFT JOIN get_data(a.ID ) ON 1=1

(where a.ID  parameter is passed from the previous set as 
a next procedure parameter), but Postgres cannot.





Can't PostgreSQL do that now though?
Functions with out params are treated like tables as of 8.1.

Later,

Tony

---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] PG Seg Faults Performing a Query

[Bill Thoen]

How would you suggest I try to track down this problem?
I run the following query:

SELECT a.* FROM compliance_2006 a, ers_regions b
 WHERE a.fips_st_cd=b.fips_st
   AND a.fips_cnty_cd=b.fips_cou AND b.region =1
   AND a.fips_st_cd='17' AND a.fips_cnty_cd='003';

and it works. But when I try this:

SELECT a.* FROM compliance_2006 a, ers_regions b
 WHERE a.fips_st_cd=b.fips_st
   AND a.fips_cnty_cd=b.fips_cou AND b.region =1
   AND a.fips_st_cd='17' ;

psql dies with the message:
Segmentation Fault.

Any suggestions?


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] Auto-partitioning?

[Steve Wampler]

Steve Wampler wrote:

... Specifically, I'm wondering if it's
possible to add a default rule that would create a new partition
(with indices, etc.) and would add a new rule for this partition
to match the similar ones above (and, of course, then move the
INSERT into the new partition).


I think I've answered my own question, but would love an expert
to validate the answer:

  The answer is "no", apparently because 'name' in

 CREATE RULE name ...

  can't be an expression.  So there's no way to dynamically create a
  sequence of rules because you can't create new rule names inside
  the body of the 'default' rule.  Ergo, no way to to auto-partitioning.

Is that correct?  Is that the only reason, or is 'create rule...'
simply not something that can be done inside another 'create rule...'?

It's possible to have a 'default' rule (of sorts) by taking advantage
of the alphabetic order of rule application, but that rule is unable
to produce new rules.  Pity.

Thanks,
Steve
--
Steve Wampler -- [EMAIL PROTECTED]
The gods that smiled on your birth are now laughing out loud.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Auto-partitioning?

[Joshua D. Drake]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Steve Wampler wrote:
> Steve Wampler wrote:
>> ... Specifically, I'm wondering if it's
>> possible to add a default rule that would create a new partition
>> (with indices, etc.) and would add a new rule for this partition
>> to match the similar ones above (and, of course, then move the
>> INSERT into the new partition).
> 
> I think I've answered my own question, but would love an expert
> to validate the answer:
> 
>   The answer is "no", apparently because 'name' in

That answer is no but you could probably pull it off with a trigger.

Sincerely,

Joshua D. Drake


- --

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997  http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFGy1BNATb/zqfZUUQRAlhxAJsEpKJicoMkvFXS+T5DiCjroSYj9QCcCjmt
qaaXSe764ULKH5h3z8p6QUc=
=2xC8
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] history table

[Guy Rouillier]

Robin Helgelin wrote:

On 8/21/07, Guy Rouillier <[EMAIL PROTECTED]> wrote:

Well, you haven't told us much about your webapp.  Are you using
connection pooling?  If so, then you'll need to provide the webapp
userid as an additional parameter to your database updates.  If you are
not using connection pooling, such that your webapp userids are
connecting as themselves, then the problem becomes much easier; you've
got the correct userid to log by just looking at the connection details.


Yes, this is where I'm too new to postgresql, how do I tell the
database which user is logged in to the webapp? A session parameter?
There will be connection pooling, but if I know how to solve the
previous question I don't think it's hard to get it working with the
pool.


Well, I can't find a way to set a variable associated with a connection, 
so probably the easiest thing to do is to add an "updated_by" column to 
your regular table (i.e., the non-history version.)  Then just include 
the userid from your webapp as the value for that column.  Your history 
table can then be updated by just copying the entire row from the base 
table whenever an insert or update occurs.


If you don't like the idea of adding an "updated_by" column to your base 
table, then you can wrap the insert inside of a stored proc and pass the 
 userid value to the stored proc.  The proc can update the base table 
without the userid, then update the history table with it.


--
Guy Rouillier

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] PG Seg Faults Performing a Query

[Scott Marlowe]

On 8/21/07, Bill Thoen <[EMAIL PROTECTED]> wrote:
> How would you suggest I try to track down this problem?
> I run the following query:
>
> SELECT a.* FROM compliance_2006 a, ers_regions b
>   WHERE a.fips_st_cd=b.fips_st
> AND a.fips_cnty_cd=b.fips_cou AND b.region =1
> AND a.fips_st_cd='17' AND a.fips_cnty_cd='003';
>
> and it works. But when I try this:
>
> SELECT a.* FROM compliance_2006 a, ers_regions b
>   WHERE a.fips_st_cd=b.fips_st
> AND a.fips_cnty_cd=b.fips_cou AND b.region =1
> AND a.fips_st_cd='17' ;
>
> psql dies with the message:
> Segmentation Fault.

so the client psql is what's dieing right?  In that case you likely
are getting too big a result set for psql to handle at once.  Trying
declaring a cursor to hold your query and fetching 100 or 1000 or so
rows at a time.

Just guessing.  What's the exact text of the error message?

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] PG Seg Faults Performing a Query

[Andrej Ricnik-Bay]

On 8/22/07, Bill Thoen <[EMAIL PROTECTED]> wrote:
> How would you suggest I try to track down this problem?
> Any suggestions?
postgres version?
Operating system?
Anything in the log(s)?


-- 
Please don't top post, and don't use HTML e-Mail :}  Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Converting non-null unique idx to pkey

[Ed L.]

On Tuesday 21 August 2007 1:45 pm, Scott Marlowe wrote:
> If you have a large db in 7.4.6, you should do two things.
>
> 1: Update to 7.4.19 or whatever the latest flavor of 7.4 is,
> right now.  There are a few known data eating bugs in 7.4.6.

Sounds like good advice from a strictly technical viewpoint.  
Unfortunately, in our particular real world, there are also 
political, financial, and resource constraints and impacts from 
downtime that at times outweigh the technical merits of 
upgrading 'right now'.

> > update pg_index
> > set indisprimary = 't'
> > where indexrelid = 
>
> I wouldn't bet on that working right.  Others know the
> internals of the db better than me, but that looks like a foot
> gun.

I'd still love to hear from any who know the internals well 
enough to say if this should work or if it's a bad idea.  It 
appears to work in some cursory testing.

TIA.
Ed

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Pgcluster 1.7 Fail safe !!!

[Merlin Moncure]

On 8/21/07, Trinath Somanchi <[EMAIL PROTECTED]> wrote:
>
>  Hi All ,
>
>  I have successfully installed pgcluster 1.7 rc7 and it is working fine . But 
> i have got a doubt on " Single Point Failure " .
>
>  I need a suggest from u all on this .

we don't see to many pgcluster questions here...you might have better
luck on the pgcluster list on pgfoundry, which appears to be active.

merlin

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Converting non-null unique idx to pkey

[Scott Marlowe]

On 8/21/07, Ed L. <[EMAIL PROTECTED]> wrote:
> On Tuesday 21 August 2007 1:45 pm, Scott Marlowe wrote:
> > If you have a large db in 7.4.6, you should do two things.
> >
> > 1: Update to 7.4.19 or whatever the latest flavor of 7.4 is,
> > right now.  There are a few known data eating bugs in 7.4.6.
>
> Sounds like good advice from a strictly technical viewpoint.
> Unfortunately, in our particular real world, there are also
> political, financial, and resource constraints and impacts from
> downtime that at times outweigh the technical merits of
> upgrading 'right now'.

Well, given the very real possibility of the entire database being
lost due to the bugs present in 7.4.6, and the fact that an upgrade
consists of pg_ctl stop;rpm -Uvh postgresql-7.4.xx;pg_ctl start I
can't really accept that as a reasonable argument.

I too work in the socalled "real world" and it took me almost a month
to get an update scheduled from 7.4.7 to 7.4.13 or so when some
serious bug fixes came out.  I can't remember the exact version after
7.4.7 that had the bug fixes I considered necessary off the top of my
head back then.

When is your next scheduled maintenance window?  I would definitely
schedule the update then.  5 minutes downtime versus hours or days if
the db gets corrupted seems a reasonable trade-off.  Plus that 5
minutes of downtime can be scheduled.  Murphy dictates that if your
data gets corrupted it will not happen at 2am when you're doing
maintenance.  It will happen wednesday at 12:30pm while you're at
lunch with your boss discussing your compensation.  :)

> > > update pg_index
> > > set indisprimary = 't'
> > > where indexrelid = 
> >
> > I wouldn't bet on that working right.  Others know the
> > internals of the db better than me, but that looks like a foot
> > gun.
>
> I'd still love to hear from any who know the internals well
> enough to say if this should work or if it's a bad idea.  It
> appears to work in some cursory testing.

Is this a unique index?  If it is, and you set the field(s) to not
null i would think that setting indisprimary might work.  But I'd test
it on a test database to be sure.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

[GENERAL] raw data into table process

[novice]

I am trying to record the following entries into a table.  I'm curious
to know if there's an efficient/effective way of doing this?  This
entries come from an ancient datalogger (note: separated by space and
uses YY/MM/DD format to record date)

Plain file sample.dat

3665   OK   BS 07/08/16 07:28
3665   CC   BS 07/08/16 07:29
3665   CS   BS 07/08/16 07:29
3665   CS   BS 07/08/16 07:29
4532   OK   BS 07/08/16 07:34
4004   OK   BS 07/08/16 07:51
3991   OK   BS 07/08/16 07:54


This is the table that I'm adding the entries to

CREATE TABLE maintenance
(
  maintenance_id SERIAL PRIMARY KEY,
  meter_id integer,
  status text,
  inspector text,
  inspection_date timestamp with time zone,
)

--  Begin SQL Script
--  First table to dump the records in
CREATE TABLE dataload1
(data text)

-- Dump records using \copy
\copy dataload1 FROM sample.dat

-- Second table to import unique records ONLY
CREATE TABLE dataload2 AS
  SELECT DISTINCT
  data FROM dataload1;

-- Now I update unique records into the maintenance table
-- maintenance_id is SERIAL so it will be populated automatically
INSERT INTO maintenance(meter_id, status, inspector, inspection_date)
SELECT substr("data", 1, 4)::int
  , substr("data", 8, 3)
  , substr("data", 21, 2)
  , (20||substr("data", 24, 2) ||'-'|| substr("data", 27, 2) ||'-'||
substr("data", 30, 2)||' '||substr("data", 33, 5))::timestamp as
inspection_date
  FROM dataload2
-- So the new records will also be in timestamp order
  ORDER BY inspection_date ;

-- Some housekeeping
VACUUM FULL VERBOSE ANALYZE maintenance;

-- Finally, drop the temporary tables
DROP TABLE dataload1
DROP TABLE dataload2

--  End SQL script

Any thoughts and suggestions welcome.

---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] Pgcluster 1.7 Fail safe !!!

[Trinath Somanchi]

Hi All ,

I have successfully installed pgcluster 1.7 rc7 and it is working fine . 
But i have got a doubt on " Single Point Failure " .


I need a suggest from u all on this .

For an Application which demands High Availability and  no Single Point 
Failure and built Multi-Master Replication system  , is PGCLUSTER - II  
a suitable option.


Please suggest/support this issue .

Thanks in advance .

Best Regards,

Trinath Somanchi.



This email message (including any attachments) is for the sole use of the intended recipient(s) 
and may contain confidential, proprietary and privileged information. Any unauthorized review, 
use, disclosure or distribution is prohibited. If you are not the intended recipient, 
please immediately notify the sender by reply email and destroy all copies of the original message. 
Thank you.


Intoto Inc. 

begin:vcard
fn:Trinath Somanchi
n:Somanchi;Trinath
org:Intoto Software (I) Pvt Ltd;Process Tools
adr:Panjagutta;;4th floor UMA Plaza , Nagarjuna hills,;Hyderabad;Andhra Pradesh;500082;India
email;internet:[EMAIL PROTECTED]
title:Software Engineer
tel;cell:+91 9866365130
x-mozilla-html:TRUE
version:2.1
end:vcard


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] history table

[Robin Helgelin]

On 8/21/07, Guy Rouillier <[EMAIL PROTECTED]> wrote:
> Well, I can't find a way to set a variable associated with a connection,
> so probably the easiest thing to do is to add an "updated_by" column to
> your regular table (i.e., the non-history version.)  Then just include
> the userid from your webapp as the value for that column.  Your history
> table can then be updated by just copying the entire row from the base
> table whenever an insert or update occurs.

Yes, I think this will be the easiest way, thanks!

-- 
regards,
Robin

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Converting non-null unique idx to pkey

[Tom Lane]

"Ed L." <[EMAIL PROTECTED]> writes:
> Are there 
> are any known or obvious gotchas associated with transforming a 
> unique index on a non null column into a primary key via this 
> sql?

> update pg_index 
> set indisprimary = 't' 
> where indexrelid = 

The problem with that is there won't be any pg_constraint entry,
nor any pg_depend entries linking to/from the constraint.

I don't offhand know which bits of logic look at indisprimary
and which pay attention to the pg_constraint entry (and 7.4 is
probably different from current sources on the point anyway).
Things could get a bit weird though, particularly for pg_dump.

Of course, you could gin up the required pg_constraint and pg_depend
entries by hand too, but it's a lot more complex than the above.

If you really wanna do this I'd strongly recommend experimenting
in a scratch database.

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[GENERAL] Problem with UPDATE and UNIQUE

[Frank Millman]

Hi all

I have a problem, which I suspect stems from bad design.

If I explain what I am doing, perhaps someone can suggest a better approach.

I want to store data in a 'tree' form, with a fixed number of levels, so
that each level has a defined role.

I have the following (simplified) table -

CREATE TABLE treedata (
  rowid serial primary key,
  levelno int not null,
  parentid int references treedata,
  seq int not null,
  code varchar not null,
  description varchar not null
  );

The 'root' item has a parentid of null, all other items must have a valid
parent. Items with a levelno of 0 represent raw data, higher levelno's
represent grouping levels. The seq indicator is used to display data in a
defined order.

To describe each of the levels in the tree, I have the following table -

CREATE TABLE treelevels (
  levelno int primary key,
  code varchar unique not null,
  description varchar not null
  );

Typical values for this table could be -
  (0,'Prod','Product code')
  (1,'Cat','Product category')
  (2,'*','All products')

Now for the problem. I want to insert or delete levels dynamically. I can
insert or delete levels in 'treedata' without a problem. However, I also
want to insert or delete a level in 'treelevels'.

Say I want to insert a level between 'code' and 'category' called 'group' -

INSERT INTO treelevels VALUES (1,'Group','Product group');

Obviously this will fail with a duplicate levelno. Therefore before the
insert statement I want to do this -

UPDATE treelevels SET levelno = (levelno+1) WHERE levelno >= 1;

The problem is that if there are a number of levels, and they are in
indeterminate order, I can get duplicate level numbers while the command is
being executed.

My workaround at present is the following -

UPDATE treelevels SET levelno = (levelno+10001) WHERE levelno >= 1;
UPDATE treelevels SET levelno = (levelno-1) WHERE levelno >= 1;

It works, but it feels very ugly.

Any suggestions will be much appreciated.

Thanks

Frank Millman


---(end of broadcast)---
TIP 1: 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