[SQL] query
Hello, I want to create a trigger in PostgresSQL In trigger, Before inserting the record, if data is already in the table, the trigger fire the mesaage that data is already there, and after that trigger ckeck for next insert statement. How can I do this , Please reply. Regards, _ Aftab Alam
Re: [SQL] idea for a geographically distributed database: how best to implement?
Thanks Aidan, That's very interesting! I've not used slony yet, so I'll setup some tests and try exactly this mechanism. What I like about it is that the changes that have to occur to either add a cell or to split an existing cell into two to manage growing traffic have a localised effect on the web of database interdependencies, making it more straightforward to do maintenance without the website being taken offline for most people. They'd only notice downtime if they happened to live in or next to the cells being messed around with. Not having to touch the actual web application is nice. The other advantage over my manual hack is that the replicated data is just that - replicated, so when each database does a backup, it's only the master data that is getting backed up and restored. In my manual scheme, following restoration after a node failure, I'd have to do a postprocessing step to work out which restored data was actually data slaved from a neighbouring cell, and refresh it in case the neighbouring data has been updated since the node went down. (I hadn't considered backup and restore properly up till now, but I'm going to add that to the list of good reasons for taking advantage of my 'distantly disjoint' data set. If my national data is shared between 500 cells, then each database is going to be titchy, and backup will take 'no time at all', as in parallel, 500 streams of database data flow first to node local storage and thence to safer, remote storage) Cheers, Andy - -Original Message- From: Aidan Van Dyk [mailto:[EMAIL PROTECTED] Sent: 17 November 2005 19:54 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: idea for a geographically distributed database: how best to implement? Andy, This is *very* interesting scenario. Definitely peaked my interest on this one. I haven't tried this scenario before, but I have used "inheritted" tables to do "splitups". Basically, just partition your data, and replicate certain partitions between various neighbours. http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html With Slony, you can get "master slave" replication of particular tables "sets" (not necessarily whole databases). So I haven't developped this idea, or tried it, but you might be able to play with it and make it work. On *each* database, you have a head table, with no records: CREATE TABLE complete (); And a bunch of "cell" tables that inherit the "complete" table: CREATE TABLE cell_a () INHERITS "complete"; CREATE TABLE cell_b () INHERITS "complete"; Basically, you *always* work on the "complete" table, with the following rules: CREATE RULE cell_replicate_insert ON INSERT TO complete DO INSTEAD INSERT INTO cell_a (...); CREAT RULE cell_replicate_update ON UPDATE TO complete DO INSTEAD UPDATE cell_a ...; Use SlonyI to replecate cella to NODE B (master->slave), and on NODE B, do something similar (except work on b, and replicate b -> NODE A). This way, all your web/interface code everywhere is using the "complete" table for any insert/update/select. When insert/updateing, it will always go to the "local" table, which Slony "replicates" out to the neighbouring nodes. And when selecting on it (the "complete" table), you get data from your local table, and any "replicated" neighbouring node's tables which are in the local schema inheriting from "complete" and SlonyI is updating. Good luck, and if you get anywhere with this, let me know! Andy Ballingall wrote: > Hello, > > I've got a database for a website which is a variant of the 'show stuff > near to me' sort of thing. > > Rather than host this database on a single server, I have a scheme in mind > to break the database up geographically so that each one can run > comfortably on a small server, but I'm not sure about the best way of > implementing it. > > Here's the scheme: > > > Imagine that the country is split into an array of square cells. > Each cell contains a database that stores information about people who > live in the area covered by the cell. > > There's one problem with this scheme. What happens if you live near the > edge of a cell? > > My solution is that any inserted data which lies near to the edge of cell > A is *also* inserted in the database of the relevant neighbouring cell - > let's say cell B. > > Thus, if someone lives in cell B, but close to the border with cell A, > they'll see the data that is geographically close to > them, even if it lies in cell A. > > > > Is this a common pattern? > > I could, of course, simply find every insert, update and delete in the > application and alter the code to explicitly update all the relevant > databases, but is there a more elegant way of simply saying: "Do this > transaction on both Database A and Database B" monotonically? > > I've had a look at some replication solutions, but they all seem to > involve replicating an entire
Re: [SQL] how to do a find and replace
On Thu, Nov 17, 2005 at 02:51:05PM -0800, Dawn Buie wrote:
> I'm just confused about how I should write code to update the selected
> items to remove the 'v.'
>
> Would I use substring? An example would be much appreciated.
You need a combination of overlay and location. The following will
work if you always have _only_ 'v.' in there in the one place you
want it. If it is too early in the string, this _won't_ work:
andrewtest=# SELECT version();
version
---
PostgreSQL 7.4.7 on i386-pc-linux-gnu, compiled by GCC
i386-linux-gcc (GCC) 3.3.5 (Debian 1:3.3.5-12)
(1 ligne)
andrewtest=# SELECT * from mytable ;
location
-
/0/v.myimage.jpg
/0/v.myotherimage.jpg
/0/v.myvthotherimage.jpg
/0/v.myvthotherv.image.jpg
(4 lignes)
Note that I've fiddled with the initial spacing here, in case that
hasn't been totally consistent either. This is for illustration.
andrewtest=# select overlay(location placing '' from (position('v.'
in location)) for 2) from mytable;
overlay
---
/0/myimage.jpg
/0/myotherimage.jpg
/0/myvthotherimage.jpg
/0/myvthotherv.image.jpg
(4 lignes)
Note here that the _second_ 'v.' in the last entry doesn't get pulled
out. These functions work on the first hit, so this is as expected.
But if you have something like '/v.0/v.myimage.jpeg' you'll not lose
the 'v.' you want, I expect.
A
--
Andrew Sullivan | [EMAIL PROTECTED]
The whole tendency of modern prose is away from concreteness.
--George Orwell
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] idea for a geographically distributed database: how best to implement?
On Fri, Nov 18, 2005 at 09:09:24AM -, Andy Ballingall wrote: > That's very interesting! I've not used slony yet, so I'll setup some tests > and try exactly this mechanism. If you do this with Slony, and have any success, I know that the folks on the Slony list would dearly like to hear about it (especially if you can provide details of how you did it). If you get this working in a production system, I can think of more than one conference that would _also_ like a paper on it. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Export to XML
What is the process to export a PostgreSQL table to XML? How is it done? Thanks! Warren L Murray Booz Allen Hamilton Phone: 404.518.7940 Email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] [postgres] pgcluster
Hi, ich bin dabei DB-Server zu einem Cluster zusammen zuführen mit PGCLUSTER. Hat schon jemand von euch damit gearbeitet und kann mir mal seine Meinung/Erfahrung dazu sagen (positiv oder negativ). Werde auch meine Erfahrung noch Erfolg hier mal posten. Server: 7.4.1 (suse9.0) pgcluster-1.1 Grüsse aus Berlin. Stephan. Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden Sie eine E-Mail an: [EMAIL PROTECTED] Yahoo! Groups Links <*> Besuchen Sie Ihre Group im Web unter: http://de.groups.yahoo.com/group/postgres/ <*> Um sich von der Group abzumelden, senden Sie eine Mail an: [EMAIL PROTECTED] <*> Mit der Nutzung von Yahoo! Groups akzeptieren Sie unsere: http://de.docs.yahoo.com/info/utos.html ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] idea for a geographically distributed database: how best to implement?
Hello, I’ve got a database for a website which is a variant of the ‘show stuff near to me’ sort of thing. Rather than host this database on a single server, I have a scheme in mind to break the database up geographically so that each one can run comfortably on a small server, but I’m not sure about the best way of implementing it. Here’s the scheme: Imagine that the country is split into an array of square cells. Each cell contains a database that stores information about people who live in the area covered by the cell. There’s one problem with this scheme. What happens if you live near the edge of a cell? My solution is that any inserted data which lies near to the edge of cell A is *also* inserted in the database of the relevant neighbouring cell – let’s say cell B. Thus, if someone lives in cell B, but close to the border with cell A, they’ll see the data that is geographically close to them, even if it lies in cell A. Is this a common pattern? I could, of course, simply find every insert, update and delete in the application and alter the code to explicitly update all the relevant databases, but is there a more elegant way of simply saying: “Do this transaction on both Database A and Database B” monotonically? I’ve had a look at some replication solutions, but they all seem to involve replicating an entire database. The advantage of my scheme is that if I can distribute my application over large numbers of small servers, I’ll end up with more bangs for the buck, and it’ll be much easier to manage growth by managing the number of servers, and number of cells hosted on each server. Thanks for any suggestions! Andy Ballingall 'pgsql-sql@postgresql.org'
Re: [SQL] Export to XML
On 2005-11-11 10:26, Warren Murray wrote:
What is the process to export a PostgreSQL table to XML? How is it done?
Thanks!
Two ways using PSQL:
1. Select HTML output ("\H" command) and then do a very minor amount of
post processing (details left to the reader).
2. Select "expanded" output ("\x" command), and then use SED to
post-format the output into XML:
sed -r 's:^-\[ RECORD (.*) \]-+$:\n:;s:([^ ]*)
+\| (.*): <\1>\2:;s:^$::;1s:\n::'
There's probably a cleaner way with AWK, but the above is simple enough
(your eMail reader may break the above into separate lines at the
positions where I had a space). The last two "s" commands just handle
the first/last line cases.
The only thing the above does not handle is NULL values as distinct from
zero-length strings.
-- Dean
---(end of broadcast)---
TIP 6: explain analyze is your friend
[SQL] DISTINCT ON
Hi, I have a table: observation ( id int4 NOT NULL [PRIMARY KEY], vector_id NOT NULL [FORGIEN KEY], obs_type VARCHAR(4) NOT NULL, date TIMESTAMP NULL ) I need to select the newest observation id, classify by type, for each vector (there can be multiple observation ids on each vector). I have read the postgresql manual and see that there is a "DISTINCT ON" statement which can do this. i.e. SELECT DISTINCT ON (vector_id, obs_type) id FROM observation ORDER BY vector_id, obs_type, date DESC; However the documentation also states that "DISTINCT ON" is not part of the SQL standard and should be avoided when possible, stating that aggregations and sub-queries should be used instead... How would this be done in this scenario? Thanks for you help Jeremy ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] DISTINCT ON
On Nov 19, 2005, at 7:49 , Jeremy Palmer wrote: SELECT DISTINCT ON (vector_id, obs_type) id FROM observation ORDER BY vector_id, obs_type, date DESC; However the documentation also states that "DISTINCT ON" is not part of the SQL standard and should be avoided when possible, stating that aggregations and sub-queries should be used instead... How would this be done in this scenario? Something like: select max(date), id from observation group by vector_id, obs_type; Do test to see which is better in your situation. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] DISTINCT ON
Thanks for the reply. Unfortunately that does not work as "id" column needs to be included in the group by statement or be used in an aggregate function. If I did this it definitely would note return the correct answer, as the "id" column is the primary key for the table. Any further suggestions? -Original Message- From: Michael Glaesemann [mailto:[EMAIL PROTECTED] Sent: Saturday, 19 November 2005 12:28 p.m. To: Jeremy Palmer Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] DISTINCT ON On Nov 19, 2005, at 7:49 , Jeremy Palmer wrote: > SELECT DISTINCT ON (vector_id, obs_type) >id > FROM observation > ORDER BY vector_id, >obs_type, >date DESC; > > However the documentation also states that "DISTINCT ON" is not > part of the > SQL standard and should be avoided when possible, stating that > aggregations > and sub-queries should be used instead... > > How would this be done in this scenario? Something like: select max(date), id from observation group by vector_id, obs_type; Do test to see which is better in your situation. Michael Glaesemann grzm myrealbox com ---(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: [SQL] DISTINCT ON
-Original Message- From: Michael Glaesemann [mailto:[EMAIL PROTECTED] Sent: Saturday, 19 November 2005 12:28 p.m. On Nov 19, 2005, at 7:49 , Jeremy Palmer wrote: SELECT DISTINCT ON (vector_id, obs_type) id FROM observation ORDER BY vector_id, obs_type, date DESC; However the documentation also states that "DISTINCT ON" is not part of the SQL standard and should be avoided when possible, stating that aggregations and sub-queries should be used instead... Something like: select max(date), id from observation group by vector_id, obs_type; On Nov 19, 2005, at 11:50 , Jeremy Palmer wrote: Unfortunately that does not work as "id" column needs to be included in the group by statement or be used in an aggregate function. If I did this it definitely would note return the correct answer, as the "id" column is the primary key for the table. [Please don't top post. It makes the post harder to read. I've reordered the post for readability.] Try something like this: select id from ( select max(date) as date vector_id, obs_type from observation group by vector_id, obs_type ) latest_observations join observation using (date, vector_id, obs_type) Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] DISTINCT ON
> -Original Message- > From: Michael Glaesemann [mailto:[EMAIL PROTECTED] > Sent: Saturday, 19 November 2005 4:07 p.m. > To: Jeremy Palmer > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] DISTINCT ON > > [Please don't top post. It makes the post harder to read. I've > reordered the post for readability.] > > Try something like this: > > select id > from ( > select max(date) as date > vector_id, obs_type > from observation > group by vector_id, obs_type > ) latest_observations > join observation using (date, vector_id, obs_type) > > Michael Glaesemann > grzm myrealbox com Thanks again for the help. This query looks close to what I need. I think the only change is to use an explicit join criteria, as the max date alias can't be used in the "using" clause - only table columns can. Interesting enough, on my server the "distinct on" clause that I originally ran takes 10% of execution time that the query you provided does. Thanks for your help. Jeremy Palmer ---(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
