Re: [GENERAL] Replication
On Jun 22, 2009, at 4:53 PM, Gerry Reno wrote: I noticed that the user survey on the community page does not list replication among the choices for development priority. For me, replication is the most important thing that is critically missing from postgresql. We need something as good as MySQL Replication. Both statement-based and row-based replication. And support for Master-Master and full cyclic replication setups. Postgresql is just a toy database without this as far as I am concerned. Regards, Gerry Google postgresql replication. There are multiple replication / clustering options depending on you needs. It's not built in to the DB nor should it be because everyone has different replication needs. The idea of separating replication functionality from the core DB product isn't new. AFAIK IBM has always done this on there big iron based DB2. Granted their cheap replication software costs more then you paid for that server that is running MySQL, and the expensive replication probably costs more then a cabinet worth of MySQL servers. :-) -- Kevin Barnard kevin.barn...@laser2mail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Table inheritance and partitioning
I am making the move to partition a table. I am trying to figure out the best way to migrate data to the partitions. I would prefer to not have down time. Does anybody have advice to give on this? Is there any easy way to determine what records are in the master table and which ones are in child tables? I can think of a few migration ideas doing this. Thanks in advance. -- Kevin Barnard kevin.barn...@laser2mail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Help with row locks on 7.4 to 8.0 migration
I've just upgraded a 7.4 install to 8.0.3 and we've suddenly run into lock issues that were not present in 7.4. I'm look for help on this matter, because I'm a little confused. Downgrading is really not an option at this point, we really really need the 8.0 features on the DB and a dump/restore takes way to long. Here is what happens I get a call from are call agents saying the system is slow. Almost all of there queries are WAITING for a lock. So I query pg_locks for not granted locks. Everything is waiting on a single transaction. OK fine I look at the transaction in question and look at it's query using pg_stat_activity. I reconize the query, kill the process and boom everything is back to life. OK this is great because I know this is the problem. Simple enough but the error message I get is a tad bit confusing. FATAL: terminating connection due to administrator commandCONTEXT: SQL statement SELECT 1 FROM ONLY public.client x WHERE division = $1 FOR UPDATE OF x That's what happens when I kill the SQL that was locking everything. The only problem is that's not the SQL statement that was being run. OK this is probably a trigger or something is my next though. I've searched through the entire schema and don't find this query anywhere or anything that resembles it. I've got to rewrite these query or what ever requires this query. Locking the client table is bad because just about every query needs to reference this table. The statement that is holding everything up is DELETE FROM ONLY demand_sum; INSERT INTO demand_sum (field1...; This is a simple materialized view that is updated every 5 minutes. Is the SELECT 1 statement a rewrite of something or is this a statement that the DELETE statement is waiting for (that wouldn't make sense because the delete has the lock granted)? I am sure that I'm being an absolute idiot over this and missing the obvious. Please copy me on this. I've suppresed getting emails from the list. (I check google groups, but a copy is a tad bit faster) Thank you-- Kevin BarnardGreat Beauty, great strength, and great Riches, are really and truly of no great Use; a right Heart exceeds all. -- Benjamin Franklin
[GENERAL] Vacuum message
I know I've seen this message before but I'm not quite sure what it means. I think it's because I have two vacuums running over each other. Does this sound right? It's comming from 7.4.x server. ERROR: tuple concurrently updated ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Vacuum message
On Thu, 11 Nov 2004 13:56:48 -0500, Tom Lane [EMAIL PROTECTED] wrote: Were they both VACUUM ANALYZEs? There's a known gotcha that if you ANALYZE the same table concurrently in two different sessions, one of them can fail this way when it goes to update the pg_statistic entries. (Harmless, but annoying.) I don't know of any cause for this message from a plain VACUUM though. regards, tom lane That's what it is I have cron jobs running over each other during heavy load. Thanks ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Important Info on comp.databases.postgresql.general
Currently the mailing list is also hosted in a newsgroup at new.postgresql.org. The news group is not Official so it is not carried by all news servers. There are some users who can not participate in a mailing list comfortably for one reason or another. Some of these individuals would like their local USENET provider to carry the news group. Because postgresql isn't official they will not do this. So there is now a movement to make the list official. The extra traffic I believe is coming from the discussion of the USENET people trying to get this done. Most USENET folk are good manor people just like you find on the lists. There are a lot of politics involved in USENET that are not present in your typical mailing list. This is primarily because mailing lists are hosted by the project/group and involve a single mail server where as USENET is many servers and many topics. As near as I can tell the main person pushing for making the list an official news group has inadvertently, or maybe advertently, offended someone with his politics, and/or lack of knowledge of the USENET process. Nothing big but with politics comes grudges etc. The other thing that I have noticed is people seem to get into more flame wars on USENET compared to mailing lists. There are many reasons for this but they are irrelevant. Part of this process of flaming and what not is the jerk forged message to piss people off. In particular I think the forger was attempting to sway the creditability , of the person being forged, to the people that make the USENET decisions. The chatter is there to inform anyone who might be fooled. If this push is successful are we likely to see a few jerks posting on the list via USENET? Yes, but I believe we will see an increase in useful posts from people who would not otherwise participate. Another downside is the email addresses on the list will get spread around more which increase the change of them getting harvested by a spam mer. USENET people tend to get around this by using fake email addresses for USENET that can be modified by a human when the real address is needed. Mailing lists typically don't mask the email address, and since you can't fake an address if you wish to get email, everyone on the list will increase there changes of being spammed, but maybe only slightly. This isn't necessarily a big deal because several people have a separate mailing list address and/or have spam prevention in place. Wow this turned into a bigger message then I intended :-) On Wed, 10 Nov 2004 16:03:48 -0700, Net Virtual Mailing Lists [EMAIL PROTECTED] wrote: Yeah.. I'm with you.. I don't really know what all of this is about - I like the way the Postgres mailing list works as it is Are any of the changes being discussed here going to change the content or how we receive the mailing lists?.. .. The only change I've noticed is that in all the time of reading this list I've not seen jerks posting forged messages like that Certainly not a positive change, but I'm not sure it can be attributed to what is going on... - Greg ??? As a longstanding reader of the pgsql- mailinglists, (including via news.postgresql.org on occasion), all I see is some outsiders trying to help us fix a problem that does not exist. And yes, I have read most of the messages that have passed by in these threads. After all that, I still don't see the benefit. Perhaps that is why these conversations have been carried on almost totally by people who do not post to the pgsql lists. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] FUCK THAT ASSHOLE MIKE COX! HE loves SUCKING COCK!!!
FUCK THAT ASSHOLE MIKE COX! HE loves SUCKING COCK!!! Kevin Barnard ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] [PERFORM] Restricting Postgres
I am generally interested in a good solution for this. So far our solution has been to increase the hardware to the point of allowing 800 connections to the DB. I don't have the mod loaded for Apache, but we haven't had too many problems there. The site is split pretty good between dynamic and non-dynamic, it's largely Flash with several plugins to the DB. However we still can and have been slammed and up to point of the 800 connections. What I don't get is why not use pgpool? This should eliminate the rapid fire forking of postgres instanaces in the DB server. I'm assuming you app can safely handle a failure to connect to the DB (i.e. exceed number of DB connections). If not it should be fairly simple to send a 503 header when it's unable to get the connection. On Thu, 04 Nov 2004 08:17:22 -0500, Martin Foster [EMAIL PROTECTED] wrote: Apache has a global setting for load average limits, the above was just a module which extended the capability. It might also make sense to have limitations set on schema's which can be used in a similar way to Apache directories. While for most people the database protecting itself against a sudden surge of high traffic would be undesirable. It can help those who run dynamically driven sites and get slammed by Slashdot for example. Martin Foster Creator/Designer Ethereal Realms [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL on Linux PC vs MacOS X
OS 10.3 IMHO is more stable then 10.2. I haven't us OS X in a production environment only for development. I have yet to have any problems with it crashing. I haven't really run any tests to load it down but that's only because I never expect to use in production. We have far too many IBM Servers with battery backed up RAID controllers that I do not see a sudden switch to any other platform. If I was a gambling man I would put my money on Linux doing a better job with postgres, but that's mainly because of the better hardware options in regard to disks. If your DB is processor heavy the G5 will most likely out perform x86 processors. If you go with the XRaid I think all bets are off with regards to dollar for dollar PC/Mac comparison.. On Wed, 03 Nov 2004 15:04:03 -0600, Jim Strickland [EMAIL PROTECTED] wrote: Well, the whole reason I have asked this question is because my developer swears by OS X and PostgreSQL. However, I wanted opinions from other people who have possibly used a similar setup so I can make an informed decision. I will certainly keep your advice in mind. I guess the only reason I was asking about the version of OS X and the G5 processor, is because that is all my developer uses and he seems to think they make a great combination, but that seems to be at odds with your experience. Perhaps some others will weigh in with their experiences and I will be able to make a sound decision. Fortunately there is no great rush to decide. Thanks for your help. Jeff Bohmer wrote: I noticed you ran PostgreSQL on a G4. What version of OS X were you running? Is it possible the issues you were facing were fixed with the newer G5 processor? We were using OS X 10.2 in production. We currently use 10.3 for our development machines. I would be shocked if a processor could fix stability issues in an operating system. As for performance, I cannot say how much better PostgreSQL runs on a G5 as we don't have any G5s. In terms of hardware specs, a G4/1.25Ghz should blow away a P3/800. But it didn't for us, and I think that is because Linux/x86 is much more efficient than OS X/ppc. I do not expect that to change with a newer ppc processor. Since your your developers believe a dual G5 to be plenty, you will probably get more than enough performance from an XServe G5 and any comparable 2-way Intel or AMD x86 system. PostgreSQL should handily outperform 4D. If those systems are in your price range, and stability isn't a big concern, you should probably go with the OS you are more familiar with. - Jeff Jeff Bohmer wrote: We use PostgreSQL 7.x on both OS X and Linux. We used to run OS X in production, but due to numerous problems we switched to Linux. OS X was not stable at all, especially under load. It was also a poor performer under load or not. In my tests, a P3/800, 512MB RAM (100MHz bus) was consistently faster at all queries than a G4/1.25GHz, 1.5GB RAM (266MHz bus) for our application. Both machines had single IDE drives. Another thing to consider is that you can only get ATA drives with Apple hardware. SCSI is not available from Apple, and SCSI devices have very poor support under OS X. If a server with ATA drives goes down at the wrong time, you can lose data. This happened to us with our production OS X server last year. An extended power outage ran out the UPS battery, the shutdown script did not stop the server in time, and we had to restore from an earlier backup. For details on why this can happen with ATA drives, see this thread: http://archives.postgresql.org/pgsql-general/2003-10/msg01343.php Overall, PostgreSQL has been rock solid, very fast, and headache-free on Linux. A complete change from OS X. Our main production PostgreSQL server has been up for 234 days now. In that period, the only downtime for PostgreSQL has been for planned upgrades. As a side note, we've also had major problems running multi-threaded servers on OS X which run great (stable and much, much faster) on Linux. - Jeff We currently are running a data intensive web service on a Mac using 4D. The developers of our site are looking at converting this web service to PostgreSQL. We will have a backup of our three production servers at our location. The developers are recommending that I purchase a 2GHz Dual Processor G5 with between 2GB and 4 GB RAM. They say that this configuration would be able to easily run a copy of all three production servers. My question is: has anybody had any experience comparing the performance of PostgreSQL on a G5 Mac versus a PC running Linux? Can anyone tell me if there are any benefits of running PostgreSQL on one platform over the other. Anything that can help me make the best decision would be appreciated. -- James Strickland - MCP IT Manager American Roamer 901-377-8585 http://www.americanroamer.com
Re: [GENERAL] Superuser log-in through a web interface?
You have a conceptual error. When connecting you are connecting to a database. With out the database you are not connecting to anything hence the failure. Typically to do what you are trying to do you would connect to the database template1 unless a database is specified. If you have another DB you can connect to that and do your create commands there are well. On Sat, 30 Oct 2004 20:35:50 -0400, Ken Tozier [EMAIL PROTECTED] wrote: Hello all, I'm trying to create a php form for logging in to postgress with different level passwords and my first test with a superuser isn't working unless I specify a database name. I would like to allow superusers to log in without specifying a database so they can create new users, databases etc from a web interface. Does anyone see what I'm doing wrong in the following example? Thanks for any help, Ken - I defined a super user like so CREATE USER user_name CREATEUSER PASSWORD 'password' Try to log in through a web form like so: User: user_name Password: password but the connection always fails unless I specify a database. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(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] what could cause inserts getting queued up and db locking??
This sounds like a WAL segment recycling issue. I believe 8.0 should relieve some of the stress. I've had this problem before and found that increasing the number of check point segments has helped a little. You still get the a wallop when this happens, increasing the size should make that happen less often. The downside is each of the files, segments, is 16meg. On Tue, 26 Oct 2004 13:27:53 -0400, Brian Maguire [EMAIL PROTECTED] wrote: One observation that we made was right when the statements pile up there is a large increase in the number of disk reads. The entire issue lasts approx. 20 secs and then everything recovers. There will be a backlog of 300+ statements and then all a sudden it seems to get resolved. We though there might be locking, but noticed that there were not any queries in wait mode indicating that no statements were blocked by another statement's lock. Can anyone suggest what may be causing the pile up/locking like occurrences? Brian -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 26, 2004 4:46 AM To: Brian Maguire Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] what could cause inserts getting queued up and db locking?? Brian Maguire wrote: What could cause the database to lock up and queue up all the queries? You'll want to check the lock details (pg_locks: see Monitoring Database Activity in the reference manuals) and also what the system as a whole is doing (vmstat/iostat). I seem to recall some configuration of ext3 could cause bursts of activity with certain write patterns, but don't recall PG being affected by this. There have been discussions of context-switching issues with Xeons IIRC - don't know the details, so check the -performance/hackers archives for details. HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] primary key and existing unique fields
On Tue, 26 Oct 2004 17:05:27 -0700, Robby Russell [EMAIL PROTECTED] wrote: On Tue, 2004-10-26 at 17:26 -0400, Mike Mascari wrote: joking Apparently gamma functions and string theory have little to do with understanding the relational model of data. /joking m.. string theory. :-) Ya you know the theory that states that the Database is really made up of a large amount of strings. Some are even null terminated strings, although most strings really have a quanta that can be found immediate before the string. :-) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Two questions from the boss (SQL:2003 scalability)
On Fri, 22 Oct 2004 14:18:52 -0400, Jan Wieck [EMAIL PROTECTED] wrote: That multiple hosts sounds that he came across the NDB cluster stuff that will become available in MySQL someday. Be aware that this new table handler will to my knowledge NOT support foreign keys. So the enforcement of referential integrity is back to the application, now in a multimaster cluster. I don't think that's a good idea, nor do I think it will be easier to add this later instead of doing it right in the initial design phase, but my way of solving problems is not the way MySQL plans their features. This is the major difference in philosphies between open source projects that are controlled by a company whose profit depends on sales of the product (MySQL AB) and a project that is feature funded by companines that actually need the features they are funding(PostgreSQL) So of course the are selling a feature regardless of it's need to be functional. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Information about storge engine in PostgreSQL
PostgreSQL uses it's own internal storage engine. It doesn not support multiple one. As for splitting files accross partitions this is a feature of version 8.0 called tablespaces. http://developer.postgresql.org/docs/postgres/manage-ag-tablespaces.html Should get you pointed in the right direction. On Thu, 21 Oct 2004 16:27:24 +0200 (CEST), [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hello MySQL has information about several storage engines. MEMORY to handle temporary tables, InnoDB to handle transactions and which also can split its table data over several files/partitions. Splitting of storage is something which according to the following article, PostgreSQL does not support: http://www.devx.com/dbzone/Article/20743 But I cannot verify this due to lack of information. I haven't found any similar information about the storage engine used by PostgreSQL which I think is called Postgres. Do you know of any places where this information can be obtained? Thank you. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] OID and PK/FK KEYS
A better solution is to use the serial data type. OID is depreciated and may go away. http://www.postgresql.org/docs/7.4/static/datatype.html#DATATYPE-SERIAL On 19 Oct 2004 07:54:36 -0700, Raffaele Spizzuoco [EMAIL PROTECTED] wrote: Hi! I'm from Italy, and sorry about my english... I have a question that I know it is already said in the groups but I have however some doubts I have seen it is technically possible to use OID as PRIMARY KEY and as FOREIGN KEY but it is correct to do so for the database's logical integrity? Is it better I use in any case other keys and not oid to avoid the possible wraparound? or the wraparound is an extreme case and so I can use quietly OID as PRIMARY and FOREIGN KEY? Thanks ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] delayed input
Why not do this on the client side? I'm just curious as to the benfit of doing this on the server. On Tue, 19 Oct 2004 11:10:58 -0500, Hicham G. Elmongui [EMAIL PROTECTED] wrote: I need to select all tuples from a table, but need them to be fetched with a constant delay (say 1 sec) between every consecutive tuples. The first idea that came up to my mind is to create a DelayedSeqScan operator, and put delay before returning the scanned tuple. Can I do this functionality using table functions? Regards, --h ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] [PERFORM] Performance on Win32 vs Cygwin
Have you looked at the 7.3 configuration file vs. the 8.0. It's possible that the 7.3 file is tweakled better then the 8.0. Have you anaylzed the tables after loading the data into 8.0 On Thu, 14 Oct 2004 12:01:38 -0500, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, We are experiencing slow performance on 8 Beta 2 Dev3 on Win32 and are trying to determine why. Any info is appreciated. We have a Web Server and a DB server both running Win2KServer with all service packs and critical updates. An ASP page on the Web Server hits the DB Server with a simple query that returns 205 rows and makes the ASP page delivered to the user about 350K. On an ethernet lan a client pc perceives just under 1 sec performance with the following DB Server configuration: PIII 550Mhz 256MB RAM 7200 RPM HD cygwin Postgresql 7.1.3 PGODBC 7.3.2 We set up another DB Server with 8 beta (same Web Server, same network, same client pc) and now the client pc perceives response of just over 3 sec with the following DB server config: PIII 700 Mhz 448MB RAM 7200 RPM HD 8 Beta 2 Dev3 on Win32 running as a service Is the speed decrease because it's a beta? Is the speed decrease because it's running on Win instead of cygwin? We did not install cygwin on the new DB Server. Thanks, Mike ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(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] reusing column labels in select
There might be a better way then this but this works SELECT a, a+1 as b from (SELECT 1+1 as a) as ab; On 16 Oct 2004 11:18:48 -0700, ryan [EMAIL PROTECTED] wrote: Hello, Is there any way to reuse a column label in a select list like this: SELECT 1 + 1 AS a, a + 1 AS b; I vaguely remember being able to do something like this in oracle. Any ideas? Thanks! ~RvR ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Mailing
SELECT trim(leading '0' from to_char(now(), 'HH:MM AM')) I think is what you really want. This gets rid of the nasty leasing 0. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Mailing
Going back to the documents I think Tom's answer of prepending FM is better then mine.Look at table 9-22 for other options On Tue, 5 Oct 2004 10:06:51 -0500, Todd P Marek [EMAIL PROTECTED] wrote: On Oct 5, 2004, at 10:00 AM, Kevin Barnard wrote: SELECT trim(leading '0' from to_char(now(), 'HH:MM AM')) I think is what you really want. This gets rid of the nasty leasing 0. I wasn't even paying attention to the seconds. I was in fact talking about the leading 0. Thanks to everyone and apologies for my oversight of the seconds clause. Todd Marek ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] newby question
Apache/PHP are already prebuilt on Mac OS X. All you need to do is donwload the postgres tarball config and compile. Small notte on the compile. When you compile postgres either turn off readline support or download readline from GNU and compile the static lib. I don't remember off hand but I think an older version of Postgres is compiled into the standard Mac PHP. If not it is fairly easy to recomiple PHP on the Mac. If you have any further questions about compiling ask I can walk you though it if you need. On Fri, 01 Oct 2004 11:45:18 +0100, Richard Huxton [EMAIL PROTECTED] wrote: Bernd Buldt wrote: Howdy! I'd like to set up a database (mostly a bibliography), which I'd like to connect to a webpage such that simple queries to the database can be made by visitors of my homepage. I seem to remember that FileMaker allows for this, but I'd prefer a UNIX-based solution (under MacOS X). Hence my question (before I start digging): Can anyone on the list confirm that this is doable (w/o too much hassles ) with PostGresQL? Thx for your time! Best, Bernd Can't say I've done it with MacOS-X, but Apache+PHP+PostgreSQL are a common combination. Worth checking sourforge.net / freshmeat.net and see if there are any projects doing what you want before starting your own though. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Out of memory errors on OS X
Maybe this is a server vs normal OS X issue. I am postgres on a normal iMac 10.3.5 with no problems, but this is just a developent box so I don't need the server version. All of the servers that I run are Linux/FreeBSD. I don't have access to a Mac server, if I did I would test this myself. On Wed, 29 Sep 2004 23:24:38 -0500, Jeffrey Melloy [EMAIL PROTECTED] wrote: I'll pass it on, though I'm wondering why they would have that problem and others (myself included) don't. Jeff ---(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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Returning recordsets with functions
Use the return type of SETOF and user the RETURN NEXT for each record. If you are already returning a record that's half the battle. http://www.postgresql.org/docs/7.4/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING On Thu, 23 Sep 2004 11:26:15 -0400, Robert Fitzpatrick [EMAIL PROTECTED] wrote: Can someone point me to some more information or perhaps show an example of returning a recordset from a plpgsql function. I'd like to send an argument or arguments to the function, do some queries to return a set of records. I've done several functions that return one value of one type, but nothing that returns a set. -- Robert ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] What is the postgres version of mysql's ON DUPLICATE KEY
UPDATE related_products SET related_counter = related_counter WHERE . only updates if the record exists INSERT (x,y,z) SELECT ?,?,1 WHERE NOT EXISTS (SELECT 1 FROM related_products WHERE .) Inserts if the key does not exist. On Sat, 11 Sep 2004 00:02:26 +0200, Gaetano Mendola [EMAIL PROTECTED] wrote: Nick wrote: I have a table with columns (product_id,related_product_id,related_counter) If product A is related to product B then a record should be created, if the record already exists then the related_counter should be incremented. This is very easy to do with MySQL using INSERT... ON DUPLICATE KEY. Standard or not, it is very usefull. Is there a way to catch the insert error. For example... INSERT INTO related_products (product_id,related_product_id) VALUES (?,?); IF (???error: duplicate key???) THEN UPDATE related_products SET related_counter = related_counter + 1; END IF; -Nick With a rule you can do it easily ( never tried ). Regards Gaetano Mendola ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
