Re: [GENERAL] pg_dump | pg_sql: insert commands and foreign key constraints
On 2008-09-23 19:03, William Garrison wrote: I have several .SQL files created from pg_dump, and I find that when I feed them into psql that I get tons of foreign key errors because the INSERT statements in the dump are not in the correct order. After reading the docs, mailing lists, and googling, I see posts saying this problem was fixed back in the 7.x days. It is not fixed and is sometimes not possible to fix for data only dumps. Since I did a data only dump, I think my only option is to create the schema, manually disable all the constraints, then restore, then re-enable the constraints. Much easier: 1. Create a schema with all constraints etc. 2. Dump this empty database with pg_dump with default options to empty_database.sql. 3. Split empty_database.sql file to 2 files - tables.sql and constraints.sql - all constraints will be at the end of empty_database.sql 4. drop database, create empty one, import tables.sql, import your data-only backup, import constraints.sql. Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Slony vs Longiste
Jason Long wrote: I need to set up master vs slave replication. My use case is quite simple. I need to back up a small but fairly complex(30 MB data, 175 tables) DB remotely over T1 and be able to switch to that if the main server fails. The switch can even be a script run manually. Can someone either comment in as much detail as possible or point me to a comparison of Slony vs Longiste. Or some other option I have not heard of? Three questions you need to ask yourself. 1. How heavily updated is the database? 2. How often do you change the database's schema? 3. Are there other databases in the installation? If #1 is very heavy then you'll want to do some testing with any solution you use. If #2 is a lot then you'll want to consider WAL shipping as mentioned below. Slony can handle schema changes, but you'll need to process them through its own script. I'm afraid I can't comment on Londiste. If you just want a backup and the answer to #3 is no, look at WAL shipping (see the various archive_xxx config settings in the manual and google a bit). From what I read Longiste is easy to set up while I got a quote for Slony setup for 5-10k. Unless your requirements are strange, that seems a little high, even assuming USD as a currency. Of course, if you want support and maintenance that will tend to make things mount. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] select row value from column's oid
Hi guys. I'd like to make a sql request able to get both row value and column name of a table for a specific id. Since I need the column name in my process, i first got interested in a way to get this info from pg tables with this request : SELECT a.attname as column_name FROM pg_catalog.pg_attribute a WHERE a.attnum 0 AND NOT a.attisdropped AND a.attrelid = ( SELECT c.oid FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname = 'my_table_name_here' AND pg_catalog.pg_table_is_visible(c.oid) ) This is working fine but i would like this same request to select both my column_name AND my row_value; so i thought : let's add a JOIN statement and select my row value depending on my specific id and my column name or OID or whatever i can find in my pg_attribute table ... but i can't find a way to do it! Any idea? :) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_dump of non nublic schema causes problems on restore.
Appologies if this has already been fixed, but I have come across a problem with pg_dump when dumping a single non-public schema. (This is on Windows Pg 8.2). It did not cause any major problems because I obviously made a backup of the database before I tried a restore, and managed to fix the backup to run in the right order. The problems is when creating a dump of a single (non-public) schema, with the create option. pg_dump -n myschema -c -Fp -f myschema.sql In the sql file I get: SET search_path = myschema, pg_catalog; -- Drop all tables etc. DROP SCHEMA myschema; CREATE SCHEMA myschema; CREATE TABLE table1 (...) etc. The problem here is that table1 gets created in the public schema, not myschema, presumably because the search path is no longer valid after the drop schema. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump of non nublic schema causes problems on restore.
Howard Cole [EMAIL PROTECTED] writes: The problem here is that table1 gets created in the public schema, not myschema, presumably because the search path is no longer valid after the drop schema. This is fixed in 8.3. I'm not real sure why the fix wasn't back-patched... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)
I'm not sure what the policy is on putting stuff in the docs, but how about putting that in the relevant place, as well as a note about the other option; using C and SPI. Added to TODO under features not wanted: Incomplete itemObfuscated function source code (not wanted) Obfuscating function source code has minimal protective benefits because anyone with super-user access can find a way to view the code. To prevent non-super-users from viewing function source code, remove SELECT permission on pg_proc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [ADMIN] 8.3.4 rpms for Opensuse10.3 64bit
Hi Peter, On Tue, 2008-09-23 at 23:09 +0300, Peter Eisentraut wrote: SLES builds have been broken for a while. I have not analyzed that yet. Bugs and patches welcome. https://projects.commandprompt.com/public/pgcore/repo/rpm/suse/8.3/SLES-10/postgresql.spec (it is using self-signed cert, so please ignore SSL warnings) I applied a few cosmetic changes, too. This version builds on SLES 10.2 cleanly. I also found the reason why libpgport.a is removed (see spec file for that). If you don't have any objections, I'm inclined to remove that part from spec, so that we can compile Slony-I, too. This is not a very good spec file for me, but at least it compiles. Regards, -- Devrim GÜNDÜZ, RHCE devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Error in ALTER DATABASE command
--- On Tue, 9/23/08, William Garrison [EMAIL PROTECTED] wrote: From: William Garrison [EMAIL PROTECTED] Subject: [GENERAL] Error in ALTER DATABASE command To: Postgres General List pgsql-general@postgresql.org Date: Tuesday, September 23, 2008, 3:49 PM In Postgresql 8.2.9 on Windows, you cannot rename a database if the name contains mixed case. To replicate: 1) Open the pgadmin tool. 2) Create a database named MixedCase (using the UI, not using a query window or using PSQL) 3) Open a query window, or use PSQL to issue the following command ALTER DATABASE MixedCase RENAME TO anything_else; PostgreSQL will respond with: ERROR: database mixedcase does not exist SQL state: 3D000 This does not happen if you create the database using a manual query in pgadmin, or if you use psql. Both of those tools will create the database as mixedcase instead of MixedCase I am using: PostgreSQL 8.2.9 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special) I guess for now, I have to dump and reload my database. :( Postgresql seems to force many things to lower case. Is it a bug that the admin tool lets you create a database with mixed case names? Or is it a bug that you cannot rename them thereafter? error i dont think so, teh pgadmin create the object whit the double quote () implicit. Rename the database ALTER DATABASE MixedCase RENAME TO mixedcase -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)
On Wed, Sep 24, 2008 at 02:12:19PM +, Glyn Astill wrote: I'm not sure what the policy is on putting stuff in the docs, but how about putting that in the relevant place, as well as a note about the other option; using C and SPI. C is not magic obfuscation gear. Anybody with a debugger can expose what it's doing. There have been math papers showing that it's impossible to hide the functionality of a piece of software based only on the ability to run it, so the entire prospect of obscuring the software's functionality when people can send arbitrary inputs to it is one of those known-impossible problems like the halting problem. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] case expression
Can anyone tell me why this will not work? select *, CASE WHEN postcode ilike '%OO%' THEN '' END from addresses where studentid=1234 and addresstype='C' There are postcodes like this: OO00 0OO Regards Garry -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)
David Fetter wrote: On Wed, Sep 24, 2008 at 02:12:19PM +, Glyn Astill wrote: I'm not sure what the policy is on putting stuff in the docs, but how about putting that in the relevant place, as well as a note about the other option; using C and SPI. C is not magic obfuscation gear. Anybody with a debugger can expose what it's doing. There have been math papers showing that it's I bet 'strings' shows all the SQL queries in a C object file too. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)
On Wed, Sep 24, 2008 at 08:05:18AM -0700, David Fetter wrote: C is not magic obfuscation gear. Anybody with a debugger can expose what it's doing. There have been math papers showing that it's impossible to hide the functionality of a piece of software based only on the ability to run it, so the entire prospect of obscuring the software's functionality when people can send arbitrary inputs to it is one of those known-impossible problems like the halting problem. To be fair, one of the points that others are trying to make is not secure this function for real but secure this function enough to make it a little costly. Sure, someone with a debugger and probably not much work could figure out what the function is. If all you're trying to do is make it expensive for dodgy software shops to re-use your code, however, this is probably enough: the sort of person who thinks re-using someone else's undocumented code is easier than writing it from scratch is probably not going to go to the trouble of really learning the code via debugging tools. As a defence against criminally lazy developers, compliled C code is probably good enough. (Of course, clever non-C code is probably also enough, in my opinion, but obviously others disagree.) A -- Andrew Sullivan [EMAIL PROTECTED] +1 503 667 4564 x104 http://www.commandprompt.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump | pg_sql: insert commands and foreign key constraints
Oh good. That's almost what I did: I made a schema only dump, then a data only dump with --inserts. Then I commented-out the constraints from the schema. Then I loaded the data. Unfortunately, the INSERT statements take 24 hours instead of 4 hours to restore. When you say the default options - what format does that write? Should I have used -Fp to make a plain text backup but not --inserts? Then it would be doing a COPY instead of an INSERT and maybe that would be faster. Oh well. Tomasz Ostrowski wrote: On 2008-09-23 19:03, William Garrison wrote: I have several .SQL files created from pg_dump, and I find that when I feed them into psql that I get tons of foreign key errors because the INSERT statements in the dump are not in the correct order. After reading the docs, mailing lists, and googling, I see posts saying this problem was fixed back in the 7.x days. It is not fixed and is sometimes not possible to fix for data only dumps. Since I did a data only dump, I think my only option is to create the schema, manually disable all the constraints, then restore, then re-enable the constraints. Much easier: 1. Create a schema with all constraints etc. 2. Dump this empty database with pg_dump with default options to empty_database.sql. 3. Split empty_database.sql file to 2 files - tables.sql and constraints.sql - all constraints will be at the end of empty_database.sql 4. drop database, create empty one, import tables.sql, import your data-only backup, import constraints.sql. Regards Tometzky
Re: [GENERAL] PDF Documentation for 8.3?
Am 2008-09-21 11:52:44, schrieb Sven Marcel Buchholz: Hello, what is wrong with this PDF? http://www.postgresql.org/files/documentation/pdf/8.3/postgresql-8.3-A4.pdf I was not able to download ANY PDFs I am sitting here @home behind my TP570 and I am connected over GSM (Bouygues Telecom) to the Internet and if I klick the PDF links, the conection timed out. Thanks, Greetings and nice Day/Evening Michelle Konzack Systemadministrator 24V Electronic Engineer 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 +49/177/935194750, rue de Soultz MSN LinuxMichi +33/6/61925193 67100 Strasbourg/France IRC #Debian (irc.icq.com) signature.pgp Description: Digital signature
Re: [GENERAL] Slony vs Longiste
Richard Huxton wrote: Jason Long wrote: I need to set up master vs slave replication. My use case is quite simple. I need to back up a small but fairly complex(30 MB data, 175 tables) DB remotely over T1 and be able to switch to that if the main server fails. The switch can even be a script run manually. Can someone either comment in as much detail as possible or point me to a comparison of Slony vs Longiste. Or some other option I have not heard of? Three questions you need to ask yourself. 1. How heavily updated is the database? 2. How often do you change the database's schema? 3. Are there other databases in the installation? If #1 is very heavy then you'll want to do some testing with any solution you use. If #2 is a lot then you'll want to consider WAL shipping as mentioned below. Slony can handle schema changes, but you'll need to process them through its own script. I'm afraid I can't comment on Londiste. If you just want a backup and the answer to #3 is no, look at WAL shipping (see the various archive_xxx config settings in the manual and google a bit). From what I read Longiste is easy to set up while I got a quote for Slony setup for 5-10k. Unless your requirements are strange, that seems a little high, even assuming USD as a currency. Of course, if you want support and maintenance that will tend to make things mount. The database has 10-20 concurrent users so updates are not very heavy. The schema changes very frequently. There are not other databases in the installation. This quote included initial setup, failure testing, and scripts that were to automate setup and manage the installation. It did not include support and maintenance.
Re: [GENERAL] PDF Documentation for 8.3?
At 4:12am -0400 on Wed, 24 Sep 2008, Michelle Konzack wrote: http://www.postgresql.org/files/documentation/pdf/8.3/postgresql-8.3-A4.pdf I was not able to download ANY PDFs I am sitting here @home behind my TP570 and I am connected over GSM (Bouygues Telecom) to the Internet and if I klick the PDF links, the conection timed out. Hmm, it works splendidly here (Chapel Hill, NC). I wonder if it's too large for your particular network setup? It's more than 16 MB. Do you have another network connection you could try? You also might try getting it in chunks. I've found the 'wget -c url' (or curl --continue) paradigm invaluable for downloading large files over slow, inconsistent, or otherwise flaky networks. Kevin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Debian packages for Postgres 8.2
Markus Wanner wrote: Hi, I'm running several productive servers on Debian etch (stable) with Postgres 8.2 which has been in lenny (testing) and made available for etch through the backports project [1]. Unfortunately, they discontinued maintaining 8.2 and switched to 8.3 in testing and thus also for the backports. As I don't currently want to switch to 8.3 due to the involved downtime and upgrading troubles involved. So I've compiled up to date Debian packages for Postgres 8.2.10. You can get them (maybe just temporarily) from here: http://www.bluegap.ch/debian, I'm providing packages as etch-backports for amd64 and i386. Upgrading from earlier 8.2 backports should work just fine. I'm trying to convince the backports people to re-add Postgres 8.2. As soon as that happens my own repository will probably disappear again. Please drop me a note if you are interested in 8.2 for etch. (Postgres 8.3 should become available via the backports within a few days, I guess). I still have interest and I'm actually actively using it. Its a shame, as the postgresql community still support 8.2 and probably more rely on it. Besides this, Debian's tools are well polished and support many versions side-by-side. The good question would be for what reason they have removed the backports package? Maybe shortage on maintainers? - Joris -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Debian packages for Postgres 8.2
Joris Dobbelsteen wrote: The good question would be for what reason they have removed the backports package? Maybe shortage on maintainers? As a matter of policy, backports are made from Debian testing. Continued maintenance of PG 8.2 packages is not really backporting, since there is nothing to backport from. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Slony vs Longiste
On Wednesday 24 September 2008 12:34:17 Jason Long wrote: Richard Huxton wrote: Jason Long wrote: I need to set up master vs slave replication. My use case is quite simple. I need to back up a small but fairly complex(30 MB data, 175 tables) DB remotely over T1 and be able to switch to that if the main server fails. The switch can even be a script run manually. Can someone either comment in as much detail as possible or point me to a comparison of Slony vs Longiste. Or some other option I have not heard of? Three questions you need to ask yourself. 1. How heavily updated is the database? 2. How often do you change the database's schema? 3. Are there other databases in the installation? If #1 is very heavy then you'll want to do some testing with any solution you use. If #2 is a lot then you'll want to consider WAL shipping as mentioned below. Slony can handle schema changes, but you'll need to process them through its own script. I'm afraid I can't comment on Londiste. If you just want a backup and the answer to #3 is no, look at WAL shipping (see the various archive_xxx config settings in the manual and google a bit). From what I read Longiste is easy to set up while I got a quote for Slony setup for 5-10k. Unless your requirements are strange, that seems a little high, even assuming USD as a currency. Of course, if you want support and maintenance that will tend to make things mount. The database has 10-20 concurrent users so updates are not very heavy. The schema changes very frequently. There are not other databases in the installation. This quote included initial setup, failure testing, and scripts that were to automate setup and manage the installation. It did not include support and maintenance. Are you planning on hiring someone to do it, or are you going to do it yourself, because the prices of the solution is completely orthogonal to which is the better fit technically. In your case, since you do a lot of DDL changes, I'd go with londiste over slony if I had to pick from those two. However, given the requirements you laid out, PITR is probably your best option (this is what Richard alluded too), and certainly the one I would recommend you try first. -- Robert Treat http://www.omniti.com/ Database: Scalability: Consulting -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Oracle and Postgresql
On Aug 31, 2008, at 8:44 PM, David Fetter wrote: What they want to have is a huge entity they can blame when everything goes wrong. They're not interested in the actual response times or even in the much more important time-to-fix because once they've blamed Oracle, they know the responsibility is no longer on their shoulders. The usual individual developer, open source community, and small company attitude is one that prefers to employ intelligent staff and give them a lot of responsibility (with varying degrees of success). They would rather spend a week refactoring code for performance, or experimenting with another language or database, sending an employee to training or conferences, contribute patches to open source projects, etc. They will try to make a well thought-out decision up front when possible, and often this results in an early choice for PostgreSQL, especially because it fits within any budget. When these people end up working in larger companies with different mindsets, they sometimes are successful at getting PostgreSQL utilized through resources like Command Prompt, EnterpriseDB, Greenplum, and so on, to replace the vendor support that Oracle comes with. They might start off with MySQL as a first database, but once learning about PostgreSQL, will invest lots of time into porting if they understand the advantages (this can be evidenced all the time by people communication in the #postgresql IRC channel), and will put a lot of effort into doing things the architecturally best way over time rather than just slopping together bandaided bits and poor lazy table design. Others are lazy, go with some popular name of something free they hear, and end up as Red Hat/PHP/MySQL shops, with a huge pile of random crappy free apps bandaided together - hoping to make some quick cash. If they end up with PostgreSQL it's not a thought-out decision (well or otherwise), and they use it very irresponsibly and then everyone will blame PostgreSQL for all their problems simply because it's not the most common name. There's a perception here that MySQL is better for them because it's more popular, has more random free crappy apps available for it, and they don't care much about the added reliability of PostgreSQL (often they'll run with fsync=off), as they're a rickety shop anyways. They'll also have a perception that Oracle is some magical holy grail simply because it is so out of their reach during early development, but as they become profitable enough, the idea of buying Oracle becomes very exciting. The corporate attitude is one that prefers things to be as encompassing, bundled, automatic, and self-maintaining as possible. They prefer Oracle because they provide a wide array of inbuilt replication, backup, and administrative functionality - things like raw device management (Oracle has implemented a couple different filesystems as well), early integration with Java when it was all the rage, tons of complicated shinies for permission control that managers don't really comprehend but think they like and need and are using correctly. These are typically Java shops, with no diversity at all - you won't find a single perl or bash script lying around - they'll load up a slow common Java framework for even the simplest task. Code quality tends to be pretty decent, but there is heavy denial of any problems and fixes are slow and obstructed by managerial complexity and too much influence from Sales. :P Another similar example can be found with ZFS or VxFS versus traditional simple filesystems. ZFS has few tuning options and their use is discouraged. It does a lot of things automatically and there is a favoring of defaults over workload-specific tuning and administrative overhead. It builds in every filesystem-related thing (the filesystem itself, a logical volume manager, clustering tools, and even it's own ZFS-specific filesystem cache daemon) into a single manager-friendly bundle. You can't get the same levels of performance out of it as you can by carefully tuning other systems, but that tuning requires hiring intelligent staff and this seems to be amazingly challenging for large corporations, and they'd rather pay some middle-level manager a salary worth 5 developers, and have him buy and assemble big packaged solutions instead. PostgreSQL can't really take over the corporate market - Oracle and DB2 will always be around too. :) To do that we'd need to do a lot of unwanted things to the code, build in much more unecessary complexity that most will never use, reduce flexibility and options in the process, spange up incredible amounts of well-placed marketing dollars and slowly get more acceptance by proving years of experience at an increasing number of corporate PostgreSQL users. I worked with PostgreSQL at Cingular - and the simple fact is that it was not
Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)
C is not magic obfuscation gear. Anybody with a debugger can expose what it's doing. There have been math papers showing that it's impossible to hide the functionality of a piece of software based only on the ability to run it, so the entire prospect of obscuring the software's functionality when people can send arbitrary inputs to it is one of those known-impossible problems like the halting problem. And the first word in the title is obfuscated, not encrypted, secured or anything else... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Oracle and Postgresql
On Wed, Sep 24, 2008 at 1:02 PM, Casey Allen Shobe [EMAIL PROTECTED] wrote: A knowledgeable PostgreSQL DBA can make significantly more than an Oracle DBA as they're a scarcer resource and generally higher quality on average. But it may be harder for them to find work - they may end up having to move, telecommute, or commute a longer distance simply because there are less PostgreSQL shops. It also means a much higher probability of working for a small-medium company versus a corporation. An Oracle DBA can be a lot lazier, and lean on the vendor a lot more. There are open Oracle DBA positions everywhere, and it is very easy for them to find another job, so learning a lot and focusing on doing a good job are not important to them. In the corporate environment in which most of these jobs are, they are responsible for far less in their job role, whereas the PostgreSQL DBA tends to end up responsible for a lot more pieces of the puzzle. These two paragraphs really ring true for me. I've yet to meet an oracle dba who was the jack of all trades that being a postgresql DBA requires. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Returning NEW in an on-delete trigger
Jeff Davis wrote: On Thu, 2008-09-18 at 15:04 -0400, Tom Lane wrote: This does seem like a bit of a gotcha for someone who writes RETURN NEW instead of RETURN OLD or vice versa, but I'm not sure how much we can do about that. Lots of people like to write triggers that fire on multiple event types, so we couldn't throw a syntax error for such a reference. A runtime error for a use of the variable might be possible, but a quick look at the code doesn't make it look easy. Here's a doc patch that may clear up some of the confusion. Thanks, patch applied. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] case expression
Garry Saddington [EMAIL PROTECTED] writes: Can anyone tell me why this will not work? select *, CASE WHEN postcode ilike '%OO%' THEN '' END from addresses ... Define not work. What are you expecting it to do versus what really happens? Right offhand it looks like the CASE will return either an empty string or a NULL, which doesn't seem particularly useful ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Slony vs Longiste
Robert Treat wrote: On Wednesday 24 September 2008 12:34:17 Jason Long wrote: Richard Huxton wrote: Jason Long wrote: I need to set up master vs slave replication. My use case is quite simple. I need to back up a small but fairly complex(30 MB data, 175 tables) DB remotely over T1 and be able to switch to that if the main server fails. The switch can even be a script run manually. Can someone either comment in as much detail as possible or point me to a comparison of Slony vs Longiste. Or some other option I have not heard of? Three questions you need to ask yourself. 1. How heavily updated is the database? 2. How often do you change the database's schema? 3. Are there other databases in the installation? If #1 is very heavy then you'll want to do some testing with any solution you use. If #2 is a lot then you'll want to consider WAL shipping as mentioned below. Slony can handle schema changes, but you'll need to process them through its own script. I'm afraid I can't comment on Londiste. If you just want a backup and the answer to #3 is no, look at WAL shipping (see the various archive_xxx config settings in the manual and google a bit). From what I read Longiste is easy to set up while I got a quote for Slony setup for 5-10k. Unless your requirements are strange, that seems a little high, even assuming USD as a currency. Of course, if you want support and maintenance that will tend to make things mount. The database has 10-20 concurrent users so updates are not very heavy. The schema changes very frequently. There are not other databases in the installation. This quote included initial setup, failure testing, and scripts that were to automate setup and manage the installation. It did not include support and maintenance. Are you planning on hiring someone to do it, or are you going to do it yourself, because the prices of the solution is completely orthogonal to which is the better fit technically. In your case, since you do a lot of DDL changes, I'd go with londiste over slony if I had to pick from those two. However, given the requirements you laid out, PITR is probably your best option (this is what Richard alluded too), and certainly the one I would recommend you try first. I am looking at a combination of hiring someone for setup and advice and them maintaining it myself. I agree PITR is probably a good fit. How far time wise would could the fall behind the live server and what would affect that? Anything else I should consider if I go PITR? The default size of WAL segment files is 16 MB. Since my entire DB is only 30 MB(will grow quickly as I am going to add internal document storage soon). Will I need to recompile Postgres to reduce this? What will be the repercussions of reducing the size?
Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)
Andrew Sullivan [EMAIL PROTECTED] writes: On Wed, Sep 24, 2008 at 08:05:18AM -0700, David Fetter wrote: C is not magic obfuscation gear. ... To be fair, one of the points that others are trying to make is not secure this function for real but secure this function enough to make it a little costly. Agreed, but there seems no particular need to have such a feature in core Postgres. An add-on PL could accomplish that task just as well; perhaps more so, if you don't make the add-on available to all and sundry. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Slony vs Longiste
Hi SkyTools contains in addition to Londiste and PgQ also walmgr.py that we use quite often for inside colo switchovers. Between colocations we use londiste because of lower bandwith requirements. walmgr.py --help usage: WALShipping manager. walmgr INI COMMAND [-n] Master commands: setup Configure PostgreSQL for WAL archiving sync Copies in-progress WALs to slave syncdaemon Daemon mode for regular syncing stop Stop archiving - de-configure PostgreSQL periodic Run periodic command if configured. Slave commands: boot Stop playback, accept queries pause Just wait, don't play WAL-s continue Start playing WAL-s again Common commands: listbackupsList backups. backup Copies all master data to slave. Will keep backup history if slave keep_backups is set. EXPERIMENTAL: If run on slave, creates backup from in-recovery slave data. restore [set][dst] Stop postmaster, move new data dir to right location and start postmaster in playback mode. Optionally use [set] as the backupset name to restore. In this case the directory is copied, not moved. Internal commands: xarchive archive one WAL file (master) xrestore restore one WAL file (slave) xlock Obtain backup lock (master) xrelease Release backup lock (master) xrotateRotate backup sets, expire and archive oldest if necessary. xpurgewals Remove WAL files not needed for backup (slave) On Wed, Sep 24, 2008 at 10:12 PM, Robert Treat [EMAIL PROTECTED] wrote: On Wednesday 24 September 2008 12:34:17 Jason Long wrote: Richard Huxton wrote: Jason Long wrote: I need to set up master vs slave replication. My use case is quite simple. I need to back up a small but fairly complex(30 MB data, 175 tables) DB remotely over T1 and be able to switch to that if the main server fails. The switch can even be a script run manually. Can someone either comment in as much detail as possible or point me to a comparison of Slony vs Longiste. Or some other option I have not heard of? Three questions you need to ask yourself. 1. How heavily updated is the database? 2. How often do you change the database's schema? 3. Are there other databases in the installation? If #1 is very heavy then you'll want to do some testing with any solution you use. If #2 is a lot then you'll want to consider WAL shipping as mentioned below. Slony can handle schema changes, but you'll need to process them through its own script. I'm afraid I can't comment on Londiste. If you just want a backup and the answer to #3 is no, look at WAL shipping (see the various archive_xxx config settings in the manual and google a bit). From what I read Longiste is easy to set up while I got a quote for Slony setup for 5-10k. Unless your requirements are strange, that seems a little high, even assuming USD as a currency. Of course, if you want support and maintenance that will tend to make things mount. The database has 10-20 concurrent users so updates are not very heavy. The schema changes very frequently. There are not other databases in the installation. This quote included initial setup, failure testing, and scripts that were to automate setup and manage the installation. It did not include support and maintenance. Are you planning on hiring someone to do it, or are you going to do it yourself, because the prices of the solution is completely orthogonal to which is the better fit technically. In your case, since you do a lot of DDL changes, I'd go with londiste over slony if I had to pick from those two. However, given the requirements you laid out, PITR is probably your best option (this is what Richard alluded too), and certainly the one I would recommend you try first. -- Robert Treat http://www.omniti.com/ Database: Scalability: Consulting -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Oracle and Postgresql
On Sep 4, 2008, at 7:40 PM, Robert Treat wrote: It is not as simple as Oracles database link syntax. Setting up a connection involves a couple of sql looking commands, and once you setup a connection to a remote database, you can reference a table with something like select * from [EMAIL PROTECTED] There's no way a function oriented solution can match that imho. I have long thought that what would be really useful is a standard way for third-party modules to extend or override the SQL language support within PostgreSQL itself without needing to be integrated in core. E.g. it should be possible for all of EnterpriseDB's Oracle-compatible SQL changes to exist as a separate module, somebody could change the behavior of a select to default ordering to imitate Oracle etc. It should be possible for a replication engine to add syntax for options specific to it. Contrib modules like dblink could install SQL-like command support. This would be both invaluable for compatibility efforts and probably raise the amount of 3rd party stuff that actually gets used (currently, many places I've seen avoid Slony because they fear having to use the commandline scripts it comes with, and if you want to manipulate Slony from the database itself, oftentimes this means you have to use pl/perlu or another untrusted language. Don't get me wrong, functions are great too. :) But currently the above means that a lot of risk is introduced and you have to put a lot of faith in the perl code - an exploit poses a lot of risk. If Slony exposed it's own data to PG via custom SQL extensions, this would be more secure by design. Cheers, -- Casey Allen Shobe Database Architect, The Berkeley Electronic Press -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] problem with custom_variable_classes
Hi,
I have a pl/pgSQL function[1] to calculate row numbers (based on [2]).
It uses a custom variable. This was working earlier, but is breaking now
saying it can't recognise the variable. The custom_variable_classes is
set in the postgresql.conf.
pgdb001= select rownum('') from generate_series(1,10);
ERROR: unrecognized configuration parameter olap.rownum_name
pgdb001= show custom_variable_classes;
custom_variable_classes
-
olap
(1 row)
I am using PostgreSQL 8.3.3 on CentOS 5.2 x64.
TIA,
Malcolm
[1]
CREATE OR REPLACE FUNCTION rownum(in_code TEXT)
RETURNS INT4
LANGUAGE plpgsql
as $BODY$
DECLARE
current_id TEXT;
current_rownum INT4;
settings_id TEXT;
BEGIN
current_id := statement_timestamp()::TEXT || in_code;
settings_id := current_setting('olap.rownum_name');
IF settings_id IS DISTINCT FROM current_id THEN
PERFORM set_config('olap.rownum_name',
current_id::TEXT, false);
current_rownum := 0;
ELSE
current_rownum :=
current_setting('olap.rownum_count')::INT4;
END IF;
current_rownum := current_rownum + 1;
PERFORM set_config('olap.rownum_count', current_rownum::TEXT,
false);
RETURN current_rownum;
END;
$BODY$;
[2]
http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-sum-in-one-query/
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] problem with custom_variable_classes
On Wed, Sep 24, 2008 at 12:15:41PM -0400, Malcolm Studd wrote:
I have a pl/pgSQL function[1] to calculate row numbers (based on [2]).
It uses a custom variable. This was working earlier, but is breaking now
saying it can't recognise the variable. The custom_variable_classes is
set in the postgresql.conf.
pgdb001= select rownum('') from generate_series(1,10);
ERROR: unrecognized configuration parameter olap.rownum_name
pgdb001= show custom_variable_classes;
custom_variable_classes
-
olap
(1 row)
settings_id := current_setting('olap.rownum_name');
it looks like a bug to me.
it did work in 8.2, and it doesn't in 8.3, or in 8.4.
while manual in 8.4 still states:
When custom_variable_classes is set, the server will accept definitions of
arbitrary variables within each specified class..
so it should work, but it doesn't:
# show custom_variable_classes ;
custom_variable_classes
-
depesz
(1 row)
# select current_setting('depesz.xxx');
ERROR: unrecognized configuration parameter depesz.xxx
of course i still can set a value, and then get it:
# select set_config('depesz.xxx', 'xx', false);
set_config
xx
(1 row)
# select current_setting('depesz.xxx');
current_setting
-
xx
(1 row)
but it means that there is currently no way to check if there is value in this
- i.e. if it has been initialized.
i would suggest to return NULL when calling current_setting on unknown
variable.
this is not how it worked in 8.2 (it returned empty string), but NULL is (in my
opinion) better choice.
strangely - name of current_setting function nor custom_variable_classes
doesn't show in any release docs, which makes the change somewhat mysterious.
Best regards,
depesz
--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: [EMAIL PROTECTED] / aim:depeszhdl / skype:depesz_hdl / gg:6749007
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] case expression
On Wednesday 24 September 2008 21:03, Tom Lane wrote: Garry Saddington [EMAIL PROTECTED] writes: Can anyone tell me why this will not work? select *, CASE WHEN postcode ilike '%OO%' THEN '' END from addresses ... Define not work. What are you expecting it to do versus what really happens? Right offhand it looks like the CASE will return either an empty string or a NULL, which doesn't seem particularly useful ... regards, tom lane It does work but returns a column called case. How can I return the case column as 'postcode'? Regards garry -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] case expression
On Wed, Sep 24, 2008 at 3:22 PM, Garry Saddington [EMAIL PROTECTED] wrote: select *, CASE WHEN postcode ilike '%OO%' THEN '' END from addresses It does work but returns a column called case. How can I return the case column as 'postcode'? You have to give an Alias to this column name CASE WHEN postcode ilike '%OO%' THEN '' END AS postcode -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] case expression
On 24/09/2008 23:22, Garry Saddington wrote: Garry Saddington [EMAIL PROTECTED] writes: CASE WHEN postcode ilike '%OO%' THEN '' END from addresses It does work but returns a column called case. How can I return the case column as 'postcode'? ...case when postcode ilike '%OO%' then '' end as postcode, ... BTW, should you have an else clause in there? - What happens when the comparison fails? Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] problem with custom_variable_classes
On Thu, Sep 25, 2008 at 12:38 AM, hubert depesz lubaczewski
[EMAIL PROTECTED] wrote:
but it means that there is currently no way to check if there is value in this
- i.e. if it has been initialized.
now you have to initialize this variable once per session before usage:
SELECT set_config('olap.rownum_name', false);
Regards,
Taras Kopets
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)
the sort of person who thinks re-using someone else's undocumented code is easier than writing it from scratch is probably not going to go to the trouble of really learning the code via debugging tools. Fixed that for you: the sort of person who thinks re-using someone else's undocumented code is easier than writing it from scratch is probably not going to be able to learn the code via debugging tools. -- Scott Ribe [EMAIL PROTECTED] http://www.killerbytes.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] case expression
BTW, should you have an else clause in there? - What happens when the comparison fails? As Tom said, a null value would be returned.
Re: [GENERAL] case expression
Garry Saddington wrote: It does work but returns a column called case. How can I return the case column as 'postcode'? select CASE WHEN postcode ilike '%OO%' THEN '' END as postcode from addresses -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] problem with custom_variable_classes
On Wed, Sep 24, 2008 at 07:33:27PM -0400, Tom Lane wrote: It will accept *definitions*, yes. I can't imagine why you'd think it's a good idea to not throw error when asked for an unknown variable's value. because this is how it worked. i'm not saying it was good. it worked that way, and introducing such change made some code (rownum in this example) not working. i understand that postgresql is more about correctness than end-user niceness, which has some obvious benefits, but i think - in such cases at least a one-line information in release docs wouldn't be out of place. The whole custom-variable thing is being abused far beyond what the facility was intended for, anyway. i think it's great idea. somebody thought about cool thing for modules, other people found other uses for it. right now they are being effectively punished for creativeness (unfortunatelly i can't say it is me, as i got the variable trick from somebody else). best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: [EMAIL PROTECTED] / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
