Обсуждение: dynamic SQL
I need to create a sql where the name of a tabla is on a variable..
vbuffer varchar;
SELECT buffer
INTO vbuffer
FROM rutas r, unidades u
WHERE r.codigo_ruta = u.codigo_ruta AND
u.codigo_uni = 'B15_AFF666';
SELECT within(the_geom,(SELECT geomunion(the_geom) from var vbuffer)) as inside
Thanks..
On Wed, Sep 10, 2008 at 9:45 AM, davyd <davydky@gmail.com> wrote: > Hi list, > I need to create a sql where the name of a tabla is on a variable.. > > vbuffer varchar; > > SELECT buffer > INTO vbuffer > FROM rutas r, unidades u > WHERE r.codigo_ruta = u.codigo_ruta AND > u.codigo_uni = 'B15_AFF666'; > > > SELECT within(the_geom,(SELECT geomunion(the_geom) from var vbuffer)) as > inside You'll need to do it by building the query as a string in a plpgsql function and then execute it.
Hi all, Can any one tell me how to replicate a db. Regards aftab This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you arenot the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not tocopy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mailmay contain viruses. TSR Darashaw Ltd. has taken every reasonable precaution to minimize this risk, but is not liablefor any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checksbefore opening the e-mail or attachment. TSR Darashaw Ltd. reserves the right to monitor and review the content ofall messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the TSRDarashaw Ltd's e-mail system.
Do you mean Replication? There are already several posts about replication. What Platform are you using and what version of PostgreSQL are you using?
> From: AAlam@tsrdarashaw.com
> To: pgsql-admin@postgresql.org
> Date: Thu, 11 Sep 2008 09:39:48 +0530
> Subject: [ADMIN] replica of database
>
> Hi all,
>
> Can any one tell me how to replicate a db.
>
> Regards
> aftab
>
> This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. TSR Darashaw Ltd. has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. TSR Darashaw Ltd. reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the TSR Darashaw Ltd's e-mail system.
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
MSN Technology brings you the latest on gadgets, gizmos and the new hits in the gaming market. Try it now!
Yes ,I want a replica of my db so that I can use it as failover
Version 7.3 linux AS release 4 update 6
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Vishal Arora
Sent: Thursday, September 11, 2008 10:11 AM
To: Aftab Alam; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] replica of database
Do you mean Replication? There are already several posts about replication. What Platform are you using and what version of PostgreSQL are you using?
> From: AAlam@tsrdarashaw.com
> To: pgsql-admin@postgresql.org
> Date: Thu, 11 Sep 2008 09:39:48 +0530
> Subject: [ADMIN] replica of database
>
> Hi all,
>
> Can any one tell me how to replicate a db.
>
> Regards
> aftab
>
> This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. TSR Darashaw Ltd. has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. TSR Darashaw Ltd. reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the TSR Darashaw Ltd's e-mail system.
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
MSN Technology brings you the latest on gadgets, gizmos and the new hits in the gaming market. Try it now!
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. TSR Darashaw Ltd. has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. TSR Darashaw Ltd. reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the TSR Darashaw Ltd's e-mail system.
Aftab Alam wrote: > Yes ,I want a replica of my db so that I can use it as failover > > > > Version 7.3 linux AS release 4 update 6 Version 7.3 is end of life and I don't know of *any* of the replication technologies that will work with it. Joshua D. Drake
From: AAlam@tsrdarashaw.com
To: aroravishal22@hotmail.com; pgsql-admin@postgresql.org
Date: Thu, 11 Sep 2008 10:14:42 +0530
Subject: RE: [ADMIN] replica of database
Yes ,I want a replica of my db so that I can use it as failover
Version 7.3 linux AS release 4 update 6
I would strongly recommend you to upgrade to 8.3.x. this is the latest release. 7.3 is too old and I don't think even postgres community is supporting it any more. If need help in upgrading, do let us know.
- Vishal
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Vishal Arora
Sent: Thursday, September 11, 2008 10:11 AM
To: Aftab Alam; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] replica of database
Do you mean Replication? There are already several posts about replication. What Platform are you using and what version of PostgreSQL are you using?
> From: AAlam@tsrdarashaw.com
> To: pgsql-admin@postgresql.org
> Date: Thu, 11 Sep 2008 09:39:48 +0530
> Subject: [ADMIN] replica of database
>
> Hi all,
>
> Can any one tell me how to replicate a db.
>
> Regards
> aftab
>
> This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. TSR Darashaw Ltd. has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. TSR Darashaw Ltd. reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the TSR Darashaw Ltd's e-mail system.
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
MSN Technology brings you the latest on gadgets, gizmos and the new hits in the gaming market. Try it now!
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. TSR Darashaw Ltd. has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. TSR Darashaw Ltd. reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the TSR Darashaw Ltd's e-mail system.
MSN Technology brings you the latest on gadgets, gizmos and the new hits in the gaming market. Try it now!
On Wed, Sep 10, 2008 at 10:52 PM, Joshua D. Drake <jd@commandprompt.com> wrote: > Aftab Alam wrote: >> >> Yes ,I want a replica of my db so that I can use it as failover >> >> >> Version 7.3 linux AS release 4 update 6 > > Version 7.3 is end of life and I don't know of *any* of the replication > technologies that will work with it. I think older versions of slony can talk to it, but I'd only use that to upgrade in place to 8.3. I'm pretty sure you'd have to make a stop at 8.0 or 8.1 along the way...
Version 8.3 has pg_standby. Does anyone know how well that works for failover? --- On Thu, 9/11/08, Scott Marlowe <scott.marlowe@gmail.com> wrote: From: Scott Marlowe <scott.marlowe@gmail.com> |
On Wed, Sep 10, 2008 at 09:52:41PM -0700, Joshua D. Drake wrote: > Version 7.3 is end of life and I don't know of *any* of the replication > technologies that will work with it. The 1.0.x Slony releases work with 7.3.x, x>2. I don't recommend sticking with 7.3, however. A -- Andrew Sullivan ajs@commandprompt.com +1 503 667 4564 x104 http://www.commandprompt.com/
On Thu, 2008-09-11 at 10:35 -0400, Andrew Sullivan wrote: > On Wed, Sep 10, 2008 at 09:52:41PM -0700, Joshua D. Drake wrote: > > Version 7.3 is end of life and I don't know of *any* of the replication > > technologies that will work with it. > > The 1.0.x Slony releases work with 7.3.x, x>2. I don't recommend > sticking with 7.3, however. If you ignore this very wise advise, and stick with 7.3 and decide to give Slony 1.0.x a shot, be prepared for lots of late nights rebuilding replicas and dealing if a whole multitude of other bugs it had then. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp.
On Thu, Sep 11, 2008 at 7:42 AM, Rex Mabry <rexmabry@yahoo.com> wrote: > Version 8.3 has pg_standby. Does anyone know how well that works for > failover? pg_standby is part of the Point in time recovery suite, which can be used to create a cold (recovery on comand) or warm standby (continuous recovery) server ready to take over with a few commands. It's quite different from slony, in that there's not hot read-only standby, so there's no load balancing, however, it usually produces less load on the master db, so that's a plus. PITR is quite well tested and used in a lot of production environments. It has very different design objectives than slony or pgpool or pgbouncer, which all allow you to have multiple live servers at once.
jd@commandprompt.com ("Joshua D. Drake") writes: > Aftab Alam wrote: >> Yes ,I want a replica of my db so that I can use it as failover >> >> >> >> Version 7.3 linux AS release 4 update 6 > > Version 7.3 is end of life and I don't know of *any* of the > replication technologies that will work with it. Slony-I version 1.1 can still work with 7.3. We ceased support of 7.3 when we released v1.2, but recommended, at the time, using 1.1 for 7.3-compatibility. http://archives.postgresql.org/pgsql-announce/2006-10/msg00012.php -- output = ("cbbrowne" "@" "linuxfinances.info") http://linuxfinances.info/info/emacs.html "A LISP programmer knows the value of everything, but the cost of nothing." -- Alan J. Perlis
On Thu, Sep 11, 2008 at 8:30 AM, Chris Browne <cbbrowne@acm.org> wrote: > jd@commandprompt.com ("Joshua D. Drake") writes: >> Aftab Alam wrote: >>> Yes ,I want a replica of my db so that I can use it as failover >>> >>> >>> >>> Version 7.3 linux AS release 4 update 6 >> >> Version 7.3 is end of life and I don't know of *any* of the >> replication technologies that will work with it. > > Slony-I version 1.1 can still work with 7.3. We ceased support of 7.3 > when we released v1.2, but recommended, at the time, using 1.1 for > 7.3-compatibility. > And as buggy as slony 1.0 might have been, I ran it for about 2 years in production replicating a HUGE amount of data daily with zero failures. Of course, the servers were fast and reliable, so that might have helped cover a lot of issues other people had for us.
On Thu, 2008-09-11 at 12:19 -0600, Scott Marlowe wrote: > On Thu, Sep 11, 2008 at 8:30 AM, Chris Browne <cbbrowne@acm.org> wrote: > > jd@commandprompt.com ("Joshua D. Drake") writes: > >> Aftab Alam wrote: > >>> Yes ,I want a replica of my db so that I can use it as failover > >>> > >>> > >>> > >>> Version 7.3 linux AS release 4 update 6 > >> > >> Version 7.3 is end of life and I don't know of *any* of the > >> replication technologies that will work with it. > > > > Slony-I version 1.1 can still work with 7.3. We ceased support of 7.3 > > when we released v1.2, but recommended, at the time, using 1.1 for > > 7.3-compatibility. > > > > And as buggy as slony 1.0 might have been, I ran it for about 2 years > in production replicating a HUGE amount of data daily with zero > failures. Of course, the servers were fast and reliable, so that might > have helped cover a lot of issues other people had for us. And when we ran it we had replica's getting corrupted due to bugs almost weekly (based on a particular pattern of activity). The edges are there. It did never lose data on us. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp.
bnichols@ca.afilias.info (Brad Nicholson) writes: > On Thu, 2008-09-11 at 12:19 -0600, Scott Marlowe wrote: >> On Thu, Sep 11, 2008 at 8:30 AM, Chris Browne <cbbrowne@acm.org> wrote: >> > jd@commandprompt.com ("Joshua D. Drake") writes: >> >> Aftab Alam wrote: >> >>> Yes ,I want a replica of my db so that I can use it as failover >> >>> >> >>> >> >>> >> >>> Version 7.3 linux AS release 4 update 6 >> >> >> >> Version 7.3 is end of life and I don't know of *any* of the >> >> replication technologies that will work with it. >> > >> > Slony-I version 1.1 can still work with 7.3. We ceased support of 7.3 >> > when we released v1.2, but recommended, at the time, using 1.1 for >> > 7.3-compatibility. >> >> And as buggy as slony 1.0 might have been, I ran it for about 2 years >> in production replicating a HUGE amount of data daily with zero >> failures. Of course, the servers were fast and reliable, so that might >> have helped cover a lot of issues other people had for us. > > And when we ran it we had replica's getting corrupted due to bugs almost > weekly (based on a particular pattern of activity). The edges are > there. It did never lose data on us. Mind you, that was on 7.4, not 7.3. It's possible that: a) 7.4 did new stuff, so that those index corruptions would not have been present in 7.3, but also that b) 7.3 might have "data-eating problems not present in 7.4." I *would* suggest using 1.1.[latest], of Slony-I, as that should have the fewest issues, on the Slony-I side, of any version available to run against PG 7.3, and should work the most cleanly. But on the other hand, I'd *also* strongly urge using this to get off of v7.3 and onto something a LOT newer, ASAP. I think you can get to PostgreSQL 8.1 using the 1.1 branch, which is usefully newer :-). -- let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];; http://www3.sympatico.ca/cbbrowne/unix.html Economists are still trying to figure out why the girls with the least principle draw the most interest.