Re: [GENERAL] Constraint to ensure value does NOT exist in another table?
On Thu, Jun 16, 2011 at 6:04 AM, Mike Christensen m...@kitchenpc.com wrote: I know I can setup a FK constraint to make sure Table1.ColA exists in Table2.Key, however what if I want to do the reverse? I want to ensure Table1.ColA does NOT exist in Table2.Key.. Can I do this with any sort of CHECK constraint, trigger, custom function, etc? The most common constraints are provided for, but then after that you have to use triggers. PostgreSQL deliberately doesn't support queries in CHECK constraints for this reason. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Symbols and versioning of binary releases; running a symbol server
On Thu, Jun 16, 2011 at 02:52, Craig Ringer cr...@postnewspapers.com.au wrote: Hi (EnterpriseDB) folks I've been working with someone off list to get some information about a crash they encounter during a batch run. We're generating a crash dump, but I'm having some issues getting matching symbols so I can examine it. One thing that would help with this would be if the EnterpriseDB releases included their build revision in the output of SELECT version(), so it's always clear exactly what build is in use. I've also noticed in this process that the File version on postgres.exe bears no apparent relationship to the EnterpriseDB release number. For example, postgresql 8.4.2-2 has a File Version of 8.4.2-104 while 8.4.2-1 has a file version of (IIRC) 8.4.2-9343 . Is there any way that can be improved? 1) This is not actually an EnterpriseDB thing - those versions and stamps are set by the build system 2) Why -general, and not -hackers? ;) I'll move it... To get to your points: The last digit of the version number is actually the build *day*. It's calculated by the formula: my $d = ($year - 100) . $yday; I have a feeling we've overflowed that field. The value today should'äve been 11166. I think we overflowed it when the year turned 2000, without noticing! The docs claim it's a 16 bit integer though, which should've worked. We could (once we've figured out why it's wrong) put that number in the version string as well. Or some other number - if we can pick a good one. I don't think the EDB installers should have a *different* string than what you'd get if you built the same thing manually... Even better would be to put zipped symbols directories onto the EDB download site, arranged by Pg version. Bonus points for having symlinks from the Or right alongside the downloads themselves. md5sum of postgres.exe to the matching symbols. Better again would be to run a public symbol server with symbols for all builds EnterpriseDB releases: http://chadaustin.me/2009/03/reporting-crashes-in-imvu-creating-your-very-own-symbol-server/ ... so there's no need to play version guessing games, you just point your debugger at the symbol server and it fetches what it needs on demand. Come to think of it, I can probably run a public symbol server myself if the EDB folks don't want to, but it'd be lovely if they were willing to do so because it could be integrated into the release process to ensure symbols were never missing for a build that hit public release. Hmm. That site talks about sharing them over a windows fileshar,e I doubt anybody wants to do that publically. Now, if this can be made to serve off a simple http (or ftp) server, we could probably run a server for it off the infrastructure - but that's assuming someone actually uploads the symbols as builds are created ;) And it requires the server not to be windows, and using simple protocols... -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Constraint to ensure value does NOT exist in another table?
I know I can setup a FK constraint to make sure Table1.ColA exists in Table2.Key, however what if I want to do the reverse? I want to ensure Table1.ColA does NOT exist in Table2.Key.. Can I do this with any sort of CHECK constraint, trigger, custom function, etc? The most common constraints are provided for, but then after that you have to use triggers. PostgreSQL deliberately doesn't support queries in CHECK constraints for this reason. Thanks! I wrote a Trigger for this and it seems to work fairly well.. I kinda figured that was the obvious way, but thought there might be some new 9.x feature that made this sort of thing possible. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] order by and view def.
Hello, There is something strange with the views definitions, I have order by statement amended to the views where the view is ordered by all the columns in the view. Is this a common behavior? Ps: I am using postgresql 8.3 Regards
[GENERAL] streaming replication trigger file
Hi all, I am a bit confused regarding streaming replication. I have two server: A and B. I set up replication successfully. But when the master A goes down, B are not acting as master. My recovery.conf as follows: standby_mode = 'on' primary_conninfo = 'host=10.0.0.91 port=5432 user=postgres' trigger_file = 'C:\\pg\\stopreplication\\standby.txt' restore_command = 'copy \pg\\wal_archive_91\\%f %p' When I manually create the C:\\pg\\stopreplication\\standby.txt' file, then it is working. That is, B is becoming the master. So, my question is, how this trigger file should be created so that B will become master automatically as soon as A goes down?
Re: [GENERAL] Encryption For Specific Column- Where to store the key
2011/6/16 Manuel Gysin manuel.gy...@quantum-bytes.com: From: Pavel Stehule pavel.steh...@gmail.com Hello try to use a security definer functions http://www.postgresql.org/docs/current/static/sql-createfunction.html inside this function you can access to resourcess thats are no available from outer for web user Regards Pavel Stehuke I understand the idea behind it but it does not protect me when someone can dump the whole database. He can simply change the user credentials and can access this function. But anyway thanks for the hint, it's useful to improve security! if attacker can dump a database, then any protection is terrible hard or impossible :( if you store some very good salted hash instead password to database, then access to dump isn't helpful for attacker. Regards Pavel Stehule p.s. any security protections are thin without full control over server. -- 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] streaming replication trigger file
On 06/16/11 1:31 AM, AI Rumman wrote: When I manually create the C:\\pg\\stopreplication\\standby.txt' file, then it is working. That is, B is becoming the master. So, my question is, how this trigger file should be created so that B will become master automatically as soon as A goes down? you need cluster management software, such as Heartbeat (on linux) or Veritas Cluster Service (on various operating systems) configured to detect system failure, and reconfigure the slave to be the master. This software also generally is used to manage things like the shared IP really really REALLY important is what to do when the failed original master is brought back up. it can NOT be allowed to wake up thinking its still a master since its lacking any updates since it went down, instead it has to be reconfigured to be a new slave. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] Invalid byte sequence for encoding UTF8: 0xedbebf
BRUSSER Michael wrote:
Is there a way to find the records with the text field containing
Unicode bytes 0xedbebf?
Unfortunately this is a very old version 7.3.10
This should work on 7.3 (according to the documentation):
SELECT id FROM nlsdata WHERE position('\360\235\204\236'::bytea IN
val::bytea) = 1;
Here val is the column containing the values in question.
Yours,
Laurenz Albe
--
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] streaming replication trigger file
Hi Rumman, Can you send me link to how to create postgres replication on windows. I tried few hours as per following link, but not succeeded. Which version of postgres are you using ? I tried using 9.0. http://www.postgresql.org/docs/8.2/static/high-availability.html Any type of help would be appreciated. Regards, Sanjay Rao On 6/16/2011 2:01 PM, AI Rumman wrote: Hi all, I am a bit confused regarding streaming replication. I have two server: A and B. I set up replication successfully. But when the master A goes down, B are not acting as master. My recovery.conf as follows: standby_mode = 'on' primary_conninfo = 'host=10.0.0.91 port=5432 user=postgres' trigger_file = 'C:\\pg\\stopreplication\\standby.txt' restore_command = 'copy \pg\\wal_archive_91\\%f %p' When I manually create the C:\\pg\\stopreplication\\standby.txt' file, then it is working. That is, B is becoming the master. So, my question is, how this trigger file should be created so that B will become master automatically as soon as A goes down? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] FOREIGN TABLE with dblink
Hi,
is there any way to use the new foreign table feature with dblink?
That's almost clear to me:
CREATE FOREIGN DATA WRAPPER pgsql90;
CREATE SERVER srvlocal90 FOREIGN DATA WRAPPER pgsql90 OPTIONS (hostaddr
'127.0.0.1', dbname 'lotty');
CREATE USER MAPPING FOR pgsql SERVER srvlocal90 OPTIONS (user 'pgsql',
password '');
I would like to replace this
SELECT dblink_connect('myconn', 'srvlocal90');
SELECT * FROM dblink('myconn', 'select * from mytests.fttest') AS t(id int,
myname text);
to something like this:
CREATE FOREIGN TABLE mytests.lnkto90_fttest (
id int,
myname text
)
SERVER srvlocal90 OPTIONS ()
SELECT * FROM mytests.lnkto90_fttest;
Ty
Re: [GENERAL] You could be a PostgreSQL Patch Reviewer!
On 06/15/2011 04:49 PM, Josh Berkus wrote: You do not have to be a C coder to be a patch reviewer. Pretty much all you need to know is: - how to checkout PostgreSQL from Git - how to build PostgreSQL from source - how to apply a patch And you don't even really need to know these things well. If you're on a UNIX system that has git installed, here's a complete example of how to build a custom PostgreSQL that includes a patch for review: cd git clone git://github.com/gregs1104/peg.git export PATH=$HOME/peg:$PATH mkdir pgwork peg init test cd pgwork/src/test patch -p1 ~/mytest.patch . peg build psql Just substitute the name of the patch you're using in the patch step here, and if it applies correctly (one of the first things patch review intends to test for) you're done. This does a local installation of PostgreSQL into a tree under your home directory, with reasonable defaults for all of the parts related to downloading the source code and compiling it. See the documentation for the peg utility at https://github.com/gregs1104/peg for more examples -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us PostgreSQL 9.0 High Performance: http://www.2ndQuadrant.com/books -- 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] streaming replication trigger file
Hi, My main database server was not being replicated on standby server. In standby's logs I am getting : 2011-06-16 16:22:03 IST FATAL: database system identifier differs between the primary and standby 2011-06-16 16:22:03 IST DETAIL: The primary's identifier is 5618757375637716504, the standby's identifier is 5618757422882348667. Does anybody have any idea ? Regards, Sanjay Rao On 6/16/2011 2:50 PM, Sanjay Rao wrote: Hi Rumman, Can you send me link to how to create postgres replication on windows. I tried few hours as per following link, but not succeeded. Which version of postgres are you using ? I tried using 9.0. http://www.postgresql.org/docs/8.2/static/high-availability.html Any type of help would be appreciated. Regards, Sanjay Rao On 6/16/2011 2:01 PM, AI Rumman wrote: Hi all, I am a bit confused regarding streaming replication. I have two server: A and B. I set up replication successfully. But when the master A goes down, B are not acting as master. My recovery.conf as follows: standby_mode = 'on' primary_conninfo = 'host=10.0.0.91 port=5432 user=postgres' trigger_file = 'C:\\pg\\stopreplication\\standby.txt' restore_command = 'copy \pg\\wal_archive_91\\%f %p' When I manually create the C:\\pg\\stopreplication\\standby.txt' file, then it is working. That is, B is becoming the master. So, my question is, how this trigger file should be created so that B will become master automatically as soon as A goes down? -- 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] No implicit index created when adding primary key with ALTER TABLE
Hi Tom 2011/6/15 Tom Lane t...@sss.pgh.pa.us: Stefan Keller sfkel...@gmail.com writes: My explanation is that the message (saying that an index was implicitly created) is simply wrong. The correct explanation is that you're misinterpreting whatever output you're looking at. Pls. don't treat inquirers like this - but thanks for the tip. That's what I did: CREATE TABLE mytable2(id int, name text); ALTER TABLE mytable2 ADD PRIMARY KEY(id); Then I used pgAdminIII to look for the pkey index and there was nothing. That was and still is actually the problem. When I subsequently created an index CREATE INDEX ON mytable2(id); ...or two (:-) CREATE INDEX ON mytable2(id); Postgres silently created additional indexes and pgAdminIII obviously showed these (which is all right) - but still without showing the initial pkey index - which to me is misleading. Every unique or pkey constraint has an underlying index --- the index is the implementation mechanism for the constraint, so this is assuredly so. Some tools that show both constraints and indexes will omit constraint-associated indexes from the listing, since otherwise they'd be showing duplicate information. IMO this decision is actually questionable. It makes no sense to me to suppress the indication if indexes: Either there is one or not, disregarding of constraints. In psql the commands \d+ and \di report indexes too. Yours, Stefan -- 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] You could be a PostgreSQL Patch Reviewer!
Josh Berkus schrieb: You do not have to be a C coder to be a patch reviewer. Pretty much all you need to know is: - how to checkout PostgreSQL from Git - how to build PostgreSQL from source - how to apply a patch If you know those three things, you can help with patch review. Of course, if you do know C, you can be even more help ... and learn the PostgreSQL source in the process. I can build PostgreSQL on different FreeBSD-Versions (7, 8.1, 8.2). Is this in any kind related to the PG-Buildfarm? It sounds like this could be automated very easily. We especially need folks who are able to build PostgreSQL on Windows, as we have several Windows-specific patches and no reviewers for them. If there is an rough descriptions, this should be also possible. Greetings from Germany, Torsten -- http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 verschiedenen Datenbanksystemen abstrahiert, Queries von Applikationen trennt und automatisch die Query-Ergebnisse auswerten kann. -- 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] trouble building user defined agg function with plpython
On Tue, 14 Jun 2011, Tom Lane wrote: Rick Harding rhard...@mitechie.com writes: CREATE OR REPLACE FUNCTION mysum(curr integer, vals group_data) RETURNS integer AS $$ try: curr = curr + vals['weight'] except UnboundLocalError: plpy.notice(UNBOUND) curr = 0 return curr $$ LANGUAGE plpythonu; This function doesn't work when called manually; it's got nothing to do with the aggregate context. You should read the last half of this page: http://www.postgresql.org/docs/9.0/static/plpython-funcs.html regards, tom lane Thanks so much, I had missed that part in the docs about reassigning the function parameters. That helped point me in the right direction. Rick -- 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] Constraint to ensure value does NOT exist in another table?
-Original Message- From: Mike Christensen [mailto:m...@kitchenpc.com] Sent: Thursday, June 16, 2011 1:05 AM To: pgsql-general@postgresql.org Subject: Constraint to ensure value does NOT exist in another table? I know I can setup a FK constraint to make sure Table1.ColA exists in Table2.Key, however what if I want to do the reverse? I want to ensure Table1.ColA does NOT exist in Table2.Key.. Can I do this with any sort of CHECK constraint, trigger, custom function, etc? Thanks! Mike Trigger (with corresponding trigger function) will definitely do the job. Regards, Igor Neyman -- 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] No implicit index created when adding primary key with ALTER TABLE
On Jun 16, 2011, at 6:52 AM, Stefan Keller wrote: IMO this decision is actually questionable. Agreed. One should not have to look at constraints to figure out if there's an index. One should be able to check that directly from the list of indexes. I would think this is really obvious. (But then again, db tools in general aren't really masters of the obvious when it comes to user interface...) -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.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] No implicit index created when adding primary key with ALTER TABLE
On Thursday, June 16, 2011 5:52:43 am Stefan Keller wrote: Hi Tom Then I used pgAdminIII to look for the pkey index and there was nothing. That was and still is actually the problem. When I subsequently created an index CREATE INDEX ON mytable2(id); ...or two (:-) CREATE INDEX ON mytable2(id); Postgres silently created additional indexes and pgAdminIII obviously showed these (which is all right) - but still without showing the initial pkey index - which to me is misleading. Well your initial post was about the index not being created, not about it not being displayed in pgAdmin:) This seems to be a design decision on the part of pgAdmin. As you mention below it does show up in psql. You might want to ping the pgAdmin folks with a feature request. Every unique or pkey constraint has an underlying index --- the index is the implementation mechanism for the constraint, so this is assuredly so. Some tools that show both constraints and indexes will omit constraint-associated indexes from the listing, since otherwise they'd be showing duplicate information. IMO this decision is actually questionable. It makes no sense to me to suppress the indication if indexes: Either there is one or not, disregarding of constraints. In psql the commands \d+ and \di report indexes too. Yours, Stefan -- Adrian Klaver adrian.kla...@gmail.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] Constraint to ensure value does NOT exist in another table?
I've dealt with something similar by using a check constraint and a stored procedure. The check constraint calls a stored procedure, passing it (in your case) the key you want to make sure doesn't exist in some other table. The stored procedures queries that other table for the key and passes back a YES/NO flag that the check constraint detects and acts on (constraint violated or not). I'm not using this to check a prim/foreign key relationship for my app, and the table that the stored procedure is querying is a ref table that is very static. This approach may not be bullet proof for checking key relationships in dynamic tables. I'll let others speak to that. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Igor Neyman Sent: Thursday, June 16, 2011 9:21 AM To: Mike Christensen; pgsql-general@postgresql.org Subject: Re: [GENERAL] Constraint to ensure value does NOT exist in another table? -Original Message- From: Mike Christensen [mailto:m...@kitchenpc.com] Sent: Thursday, June 16, 2011 1:05 AM To: pgsql-general@postgresql.org Subject: Constraint to ensure value does NOT exist in another table? I know I can setup a FK constraint to make sure Table1.ColA exists in Table2.Key, however what if I want to do the reverse? I want to ensure Table1.ColA does NOT exist in Table2.Key.. Can I do this with any sort of CHECK constraint, trigger, custom function, etc? Thanks! Mike Trigger (with corresponding trigger function) will definitely do the job. Regards, Igor Neyman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] Encryption For Specific Column- Where to store the key
On Thu, Jun 16, 2011 at 3:35 AM, Manuel Gysin manuel.gy...@quantum-bytes.com wrote: I can trust the dba. But while someone gain access, he can control everything and could easily extend his rights to dba. An other way with client side encryption/decryption should be possible with deployed certificates and keys, but so only the user has access to the data. With all due respect, I don't think you can trust the dba. People are always the weakest link in any security chain. Don't think of the specific person, but more the role involved and what data that person should be allowed to see. Imagine explaining to your customers that certain of your personnel will have access to credit card numbers or other highly sensitive information. Imagine explaining it to an auditor. OTOH, maybe your security expectations are a little too high -- it's always a tradeoff between security and usability (this is why we don't encrypt addresses). If you are talking about the specific case of credit card number processing, there are strict protocols that have to be followed in order to be able to do that. You must submit to an audit in order to be able to do that (google pci compliance). Password hashing is another thing -- randomly chosen defenses (like repeat hashing or combining algorithms) are, IMNSHO, useless. Better to use salt and, if necessary, do not do the hashing or store the salt on the server. merlin -- 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] Constraint to ensure value does NOT exist in another table?
On 16 June 2011 14:41, Gauthier, Dave dave.gauth...@intel.com wrote: I've dealt with something similar by using a check constraint and a stored procedure. The check constraint calls a stored procedure, passing it (in your case) the key you want to make sure doesn't exist in some other table. The stored procedures queries that other table for the key and passes back a YES/NO flag that the check constraint detects and acts on (constraint violated or not). I'm not using this to check a prim/foreign key relationship for my app, and the table that the stored procedure is querying is a ref table that is very static. This approach may not be bullet proof for checking key relationships in dynamic tables. I'll let others speak to that. Did you use explicit locking? If not, you likely have a race condition. The same applies to any sort of enforcement of business rules inside triggers (or, indeed, check constraints). Check constraints are generally intended to enforce simple, immutable rules (i.e. that only reference the tuple that the rule is enforced on). I would have used a trigger instead. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services -- 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] order by and view def.
salah jubeh s_ju...@yahoo.com writes: There is something strange with the views definitions, I have order by statement amended to the views where the view is ordered by all the columns in the view. Is this a common behavior? Ps: I am using postgresql 8.3 If the views use SELECT DISTINCT, PG versions before 8.4 will force all the DISTINCT columns to appear in the sort list. That's an implementation detail we got rid of in 8.4 ... 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] Constraint to ensure value does NOT exist in another table?
I preferred the check constraint mainly because of the feedback violation messages. Other, more traditional constraints exist on other columns of the table. The names of those constraints contain information about the nature of the violation. I wanted to standardize the way I detected these violations and parse out the information. So I just added check constraints with names that followed the naming convention and always look for traditional constraint violation messages in my perl/DBI script. No, no explicit locking, but as I said, the table being querried is static. -Original Message- From: Peter Geoghegan [mailto:pe...@2ndquadrant.com] Sent: Thursday, June 16, 2011 9:56 AM To: Gauthier, Dave Cc: Igor Neyman; Mike Christensen; pgsql-general@postgresql.org Subject: Re: [GENERAL] Constraint to ensure value does NOT exist in another table? On 16 June 2011 14:41, Gauthier, Dave dave.gauth...@intel.com wrote: I've dealt with something similar by using a check constraint and a stored procedure. The check constraint calls a stored procedure, passing it (in your case) the key you want to make sure doesn't exist in some other table. The stored procedures queries that other table for the key and passes back a YES/NO flag that the check constraint detects and acts on (constraint violated or not). I'm not using this to check a prim/foreign key relationship for my app, and the table that the stored procedure is querying is a ref table that is very static. This approach may not be bullet proof for checking key relationships in dynamic tables. I'll let others speak to that. Did you use explicit locking? If not, you likely have a race condition. The same applies to any sort of enforcement of business rules inside triggers (or, indeed, check constraints). Check constraints are generally intended to enforce simple, immutable rules (i.e. that only reference the tuple that the rule is enforced on). I would have used a trigger instead. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL 9.0 or 9.1 ?
Hello again! (i got my traditional email-address back!) we have been running our infrastructure on 8.3 for quite some years now, and i am thinking it is now time to upgrade all major parts of our system (java, jboss, postgresql). I would tend to be a little radical and go a little optimistic and greedy about it. I have been using 9.0 as a test system with no major flaws for quite some time as well. (but unfortunately without exploiting any of its new features) Till the end of July i must have finished all the migration to the new versions. The migration will involve testing of about 5,458 sql statements and the migration of some heavily customized in house functions, including a version of DBmirror (which is in use for a very specific set of problems) So i am asking what would be better from your perspective to do? Go for 9.1? or stick to 9.0 and try to deploy it and take the most out of it? When is a stable (release) version of 9.1 be available? Has any one faced any issues migrating from 9.0 to 9.1 -- Achilleas Mantzios -- 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] PostgreSQL 9.0 or 9.1 ?
It could be worth considering 9.1. Probably by the time you get production ready version, 9.1 will be already stable (few months I guess). The usual answer to that question is - it will be ready when its ready. -- 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] PostgreSQL 9.0 or 9.1 ?
On Thu, Jun 16, 2011 at 10:06 AM, Achilleas Mantzios ach...@matrix.gatewaynet.com wrote: Till the end of July i must have finished all the migration to the new versions. The migration will involve testing of about 5,458 sql statements and the migration of some heavily customized in house functions, including a version of DBmirror (which is in use for a very specific set of problems) You need to test these things on the exact version you plan to deploy, so not having a final 9.1 will make this pretty hard to do. Granted, the changes going in from now on are not supposed to be new/changed features, but just bug fixes... the final determination of how secure you feel in your testing is up to you. -- 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] PostgreSQL 9.0 or 9.1 ?
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of Grzegorz Jaskiewicz Sent: Thursday, June 16, 2011 11:05 AM To: Achilleas Mantzios Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] PostgreSQL 9.0 or 9.1 ? It could be worth considering 9.1. Probably by the time you get production ready version, 9.1 will be already stable (few months I guess). The usual answer to that question is - it will be ready when its ready. I would also ask, what is your (and your managements) tolerance for risk, and do you actually need any of the new features and/or performance benefits in 9.1? Postgres does have an excellent track record for quality and stability with new releases, but a couple of months in the field isn't really considered stable in most places. Brad. -- 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] Encryption For Specific Column- Where to store the key
From: Pavel Stehule pavel.steh...@gmail.com
Hello
try to use a security definer functions
http://www.postgresql.org/docs/current/static/sql-createfunction.html
inside this function you can access to resourcess thats are no
available from outer for web user
Regards
Pavel Stehuke
I understand the idea behind it but it does not protect me when someone can
dump the whole database.
He can simply change the user credentials and can access this function. But
anyway thanks for the hint, it's useful to improve security!
From: Merlin Moncure mmonc...@gmail.com
securing the backend from the dba is basically impossible. you can
make the client pretty secure, but the only way your encryption can be
reasonably enforced is for both the encryption and decryption to
happen on the client side -- the key cannot and should not be
possessed by anyone who is not trustworthy.
merlin
I can trust the dba. But while someone gain access, he can control everything
and could easily extend his rights to dba.
An other way with client side encryption/decryption should be possible with
deployed certificates and keys, but so only the user has access to the data.
The case is that I store private information like address, telephone, e-mail
and maybe credit card information.
For billing or control of the customer data I need to access to the data, with
a client en/decryption this is *not possible.
(*I'm not sure but I read sometime ago something about a master key which could
decrypt data encrypted with the child keys, this would be a way to solve the
problem. An other way is to save the decryption keys to a safe place and only
load it when access is needed. Else the decryption is made through the client.
(With Java Script this should be easily possible))
Thanks for this hint, it seems a very elegant way to solve my problem! The
deployment seems a problem while we are a open to register project and I fear
we get much support calls about this, but security is it worth. The only big
problem is when the user has lost his key, then it could be complicated.
From: Craig Ringer cr...@postnewspapers.com.au
Make sure you use a salt value and store the salt as well as the
password. If not salted, your passwords will be easily cracked with a
rainbow table if someone manages to dump them.
Password it salted and hashed multiple times. Simple salt + hash is no longer
secure since there so much cpu power in the cloud.
I used crypt('12345', gen_salt('bf', 16)) for it.
Some nice information could be found under
http://codahale.com/how-to-safely-store-a-password/ if anyone is interested in
this topic.
You won't have much luck with that. It only takes one SELECT * FROM
... to bypass your query rate limiting. You could force everything
through stored procedures, but that'll be slow and clumsy.
Good intrusion detection and system monitoring so that you detect
unusual events is probably a safter bet than trying to make your system
fail when load spikes.
Yes, I know. This security measurements are being implemented.
I just try to protect from the worst case scenario, when intrusion detection
fails.
Yep. There you can do per-IP or per-range rate limiting, among other
things, which will help. I'd probably want to do this using an intrusion
detection/prevention system probably running as a reverse http proxy.
I need to read me deeper in this topic, thanks for the hint!
Not much, no. The main benefit to encrypting some data in the database
is to make it harder to use a stolen dump or extracted content. It can't
make it impossible so long as your system can use the content too.
I'd certainly avoid keeping the key in the database.
If you don't need the capability for unattended web front-end re-start,
you can have your web frontend store the key encrypted on disk and
require interactive password entry to decrypt the key before it can
start. It can then keep the key in RAM and forget the password. If your
frontend is utterly compromised and someone has the time to do the
analysis you're still busted, but it'll slow them down a bunch. If you
want to be viewed as a frothing paranoid you can even store the
encrypted key on the web frontend's HDD but on a separate SD card or
thumb drive that requires physical insertion. I wouldn't.
Personally, I'd probably just keep the key on the web frontend machine
either unencrypted or encrypted with a passphrase coded into the
frontend app. At least that way someone who manages to dump some of your
tables or steal a database dump will need to steal - and know they need
to steal - something completely different as well before they can use
the stolen information.
I don't know, somehow I do not like the idea to save it on the frontend.
I think I try to implemented a Java Script based client en/decryption, with
storing the information on creating to a drive which could only be written but
not read.
(Or your idea mentioned some lines further down)
It depends a lot on what the trade-off between
[GENERAL] PostgreSQL 9.0 or 9.1 ?
Hello, we have been running our infrastructure on 8.3 for quite some years now, and i am thinking it is now time to upgrade all major parts of our system (java, jboss, postgresql). I would tend to be a little radical and go a little optimistic and greedy about it. I have been using 9.0 as a test system with no major flaws for quite some time as well. Till the end of July i must have finished all the migration to the new versions. The migration will involve testing of about 5,458 sql statements and the migration of some heavily customized in house functions, including a version of DBmirror (which is in use for a very specific set of problems) So i am asking what would be better by your perspective to do? Go for 9.1? or stick to 9.0? Where is a stable (release) version of 9.1 be available? Has any one faced any issues migrating from 9.0 to 9.1 Achilleas Mantzios
[GENERAL] set returning function with variable argument - possible?
I have a function that returns a set of a defined type, something like: CREATE OR REPLACE FUNCTION f(arg) RETURNS SETOF return_type AS $$ ... $$ LANGUAGE 'plpgsql' STABLE with (isstrict); Ordinarily this function is called: SELECT * FROM f(constant); But what I want to do is more like: SELECT * FROM f(var) WHERE var IN (SELECT var FROM xtable WHERE ...); This fails, as does SELECT * FROM f(var) WHERE var IN (constant); - Is there some syntax that will allow me to perform this operation? Note that there are differing ways for arg to be determined, so I'd rather not merge this operation into f() - I would have to have a number of f()'s, each with its own mechanism for developing its args. - Running Postgresql 8.4.7 on Debian 'squeeze'. Thanks for any pointers to documentation that I have overlooked in my search! -f -- 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] set returning function with variable argument - possible?
Hello I don't know if I understand well, but you can do select (f).* from (select f(var) from xtable where ...) x; Regards Pavel Stehule 2011/6/16 Frank Miles f...@u.washington.edu: I have a function that returns a set of a defined type, something like: CREATE OR REPLACE FUNCTION f(arg) RETURNS SETOF return_type AS $$ ... $$ LANGUAGE 'plpgsql' STABLE with (isstrict); Ordinarily this function is called: SELECT * FROM f(constant); But what I want to do is more like: SELECT * FROM f(var) WHERE var IN (SELECT var FROM xtable WHERE ...); This fails, as does SELECT * FROM f(var) WHERE var IN (constant); - Is there some syntax that will allow me to perform this operation? Note that there are differing ways for arg to be determined, so I'd rather not merge this operation into f() - I would have to have a number of f()'s, each with its own mechanism for developing its args. - Running Postgresql 8.4.7 on Debian 'squeeze'. Thanks for any pointers to documentation that I have overlooked in my search! -f -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] PostgreSQL 9.0 or 9.1 ?
On 06/16/2011 10:06 AM, Achilleas Mantzios wrote: Till the end of July i must have finished all the migration to the new versions. So i am asking what would be better from your perspective to do? Go for 9.1? or stick to 9.0 and try to deploy it and take the most out of it? When is a stable (release) version of 9.1 be available? Has any one faced any issues migrating from 9.0 to 9.1 I would place odds at about 1/3 that 9.1 will be available by the end of July. But you will still need to do testing of your application first before deploying onto that version. Realistically, even the earliest of 9.1 adopters is unlikely to launch before August. As such, there's not very much experience about the migration available yet, either. A large number of the new features in 9.1 aim at making certain types of development easier. The must-have features I am hearing demand for from my customers (who admittedly care more about replication and performance features than most), such that they are postponing some deployments until 9.1 ships because 9.0 just doesn't do what they want, are: -Synchronous replication -Support for MIN/MAX queries against partitioned tables -Feedback mechanism to reduce query conflict resolution when using Hot Standby -Much improved monitoring for replication and Hot Standby queries I'd suggest you take a look at the 9.1 release notes and beta announcement: http://www.postgresql.org/about/news.1313 , http://www.postgresql.org/docs/9.1/static/release-9-1.html And if you don't see a major compelling reason to wait for 9.1, some feature in that list that makes your life a lot easier, you really should just deploy 9.0 and move on. The most critical thing fixed in 9.1 development that may apply to what you're doing--some bug fixes to pg_upgrade--have all been backported to 9.0 now. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us PostgreSQL 9.0 High Performance: http://www.2ndQuadrant.com/books -- 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] PostgreSQL 9.0 or 9.1 ?
On Thu, Jun 16, 2011 at 2:47 AM, Achilleas Mantzios mantzios.ach...@yahoo.com wrote: Hello, we have been running our infrastructure on 8.3 for quite some years now, and i am thinking it is now time to upgrade all major parts of our system (java, jboss, postgresql). I would tend to be a little radical and go a little optimistic and greedy about it. I have been using 9.0 as a test system with no major flaws for quite some time as well. Till the end of July i must have finished all the migration to the new versions. The migration will involve testing of about 5,458 sql statements and the migration of some heavily customized in house functions, including a version of DBmirror (which is in use for a very specific set of problems) So i am asking what would be better by your perspective to do? Go for 9.1? or stick to 9.0? Where is a stable (release) version of 9.1 be available? Has any one faced any issues migrating from 9.0 to 9.1 Are you looking for any features that 9.1 has to offer? If you aren't, it may make your decision easier. Unfortunately there are several 9.1 features that are just awesome. So, where you go from here is going to depend on your risk tolerance and (more importantly) your availability of testing resources. Testing of production-ish workloads during the beta period are very much appreciated by the community, so feel free to give it a shot as long as you understand the risk involved (which are substantial). One big risk with 9.1 early adoption is that you run the risk of having to dump/reload if you go production while in the before the build hits release candidate status (and sometimes, even then). So, if you are running a 24x7 duty cycle that's something to think about. merlin -- 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] You could be a PostgreSQL Patch Reviewer!
Craig, Well, a quick look through https://commitfest.postgresql.org/action/commitfest_view/inprogress suggests that these could use attention: Let me know which one of these you want to take first. I'll put your name by it. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com San Francisco -- 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] You could be a PostgreSQL Patch Reviewer!
I have no knowledge on the subject one way or the other, that's why I'm asking.. If the answer is no, there would be no benefit then that's the answer.. However, it seems like generating UUIDs in Win64 has been broken, perhaps, forever which is why I question if there's a better library to use on the Windows platform (or having a single UUID generator built in for all platforms).. We don't know of a better UUID generator, and barring evidence to the contrary, it seems much more efficient to contribute to the uuid-ossp project than to invent our own. As far as I can tell, the UUID extension is designed to support configurable UUID libraries, so if you could find one which worked well for Windows, it would probably be fairly easy to extend UUID to support it. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com San Francisco -- 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] You could be a PostgreSQL Patch Reviewer!
In response to Joshua Berkus j...@agliodbs.com: I have no knowledge on the subject one way or the other, that's why I'm asking.. If the answer is no, there would be no benefit then that's the answer.. However, it seems like generating UUIDs in Win64 has been broken, perhaps, forever which is why I question if there's a better library to use on the Windows platform (or having a single UUID generator built in for all platforms).. We don't know of a better UUID generator, and barring evidence to the contrary, it seems much more efficient to contribute to the uuid-ossp project than to invent our own. As far as I can tell, the UUID extension is designed to support configurable UUID libraries, so if you could find one which worked well for Windows, it would probably be fairly easy to extend UUID to support it. The Windows system API has functions built into the OS for generating UUIDs, for example: http://msdn.microsoft.com/en-us/library/aa379322 I have almost no experience using this, but it might be worth investigating if PostgreSQL's code is abstract enough to use different implementations and the ossp library is not reliable on Windows. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] getting postgres server on freebsd startup?
I've installed and tested postgresql just fine on FreeBSD 8.2. I've been trying to get postgresql (the server) to start on bootup using /etc/rc.conf system. I'm using the script from the tarball (found in the contrib/start-scripts/freebsd of postreges tarball) I can't seem to get it to work on FreeBSD 8.2 amd64 (I don't think the arch is important here, but you never know). As instructed in the script, I've moved the file to /usr/local/etc/rc.d/postgresql I've added the postgresql_enable=YES to /etc/rc/conf. I know I'm missing some magic here (http://www.freebsd.org/doc/en_US.ISO8859-1/articles/rc-scripting/index.html perhaps?) I've /usr/local/pgsql/bin./postgres --help'd too and can't seem to get traction. Can you please help? I'm sure this is something simple I'm neglecting. Please don't respond with Why don't you just use the ports collection? There's reasons - like: 1) need to build from source, 3) it's for a tutorial, and 3) postgresql90-server isn't building. Respectfully, Jeff. Jeff Hamann, PhD PO Box 1421 Corvallis, Oregon 97339-1421 541-754-2457 jeff.hamann[at]forestinformatics[dot]com jeff.d.hamann[at]gmail[dot]com http://www.forestinformatics.com http://forufus.blogspot.com/ http://seekingalpha.com/author/jeff-d-hamann
Re: [GENERAL] Invalid byte sequence for encoding UTF8: 0xedbebf
-Original Message-
From: Albe Laurenz [mailto:laurenz.a...@wien.gv.at]
Sent: Thursday, June 16, 2011 5:16 AM
To: BRUSSER Michael; pgsql-general@postgresql.org
Subject: RE: [GENERAL] Invalid byte sequence for encoding UTF8: 0xedbebf
BRUSSER Michael wrote:
Is there a way to find the records with the text field containing Unicode
bytes 0xedbebf?
Unfortunately this is a very old version 7.3.10
This should work on 7.3 (according to the documentation):
SELECT id FROM nlsdata WHERE position('\360\235\204\236'::bytea IN val::bytea)
= 1;
Albe, thanks for pointing this out!
I made a minor change, added decode since text cannot be cast to bytea and
tried something like this:
SELECT id FROM myTable WHERE position('\360\235\204\236'::bytea IN
decode(myTextField, 'escape')) != 0
ERROR: decode: Bad input string for type bytea
If I limit query to some healthy records - AND id between 100 and 110 it
works and returns empty result.
So the problem now is that without decode myTextField cannot be converted to
bytea, with decode it breaks on the first 'bad' value.
Maybe this explains why?
testdb=# select decode('\360\235\204\236'::text, 'escape');
ERROR: Unicode = 0x1 is not supported
Another thought is that if I get this to work I may need to search for anything
outside of the standard utf range,
rather than any specific sequence. I am beginning to understand why many people
dealt with this in the dump file,
but I'm not ready to give up yet...
As usual, any ideas are appreciated!
Thanks.
This email and any attachments are intended solely for the use of the
individual or entity to whom it is addressed and may be confidential and/or
privileged.
If you are not one of the named recipients or have received this email in error,
(i) you should not read, disclose, or copy it,
(ii) please notify sender of your receipt by reply email and delete this email
and all attachments,
(iii) Dassault Systemes does not accept or assume any liability or
responsibility for any use of or reliance on this email.
For other languages, go to http://www.3ds.com/terms/email-disclaimer
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgres 8.3.10 Alter Table Waiting issue
Hi Team, I am trying to add column to the table that's hanging. alter table hr_firms add column_name biginit; This is the lock I see in the db relation | 564709 | 586888 | | || | | | | 1/8| 1871 | AccessExclusiveLock | f Version of postgres: 8.3.10 Is it known issue or any fix available? -- With Warm Regards, Pratheeban .T
Re: [GENERAL] Postgres performance and the Linux scheduler
On 16 June 2011 19:10, Simon Windsor simon.wind...@cornfield.me.uk wrote: Can the performance of Postgres be boosted, especially on busy systems, using the none default DEADLINE Scheduler? Probably, but it's a controversial area, and if you want to speed up a busy system, it would be almost the last thing to look at. Changing read-ahead settings would be almost the first thing to look at, as ways of improving the performance of Postgres by tweaking the operating system go. Increasing this parameter from the default is often very effective at speeding up sequential scans. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services -- 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] Constraint to ensure value does NOT exist in another table?
On 16 Jun 2011, at 7:04, Mike Christensen wrote: I know I can setup a FK constraint to make sure Table1.ColA exists in Table2.Key, however what if I want to do the reverse? I want to ensure Table1.ColA does NOT exist in Table2.Key.. Can I do this with any sort of CHECK constraint, trigger, custom function, etc? Thanks! Perhaps it's possible to use a unique constraint in a third table to guarantee those foreign keys can never have the same value. That would probably be more efficient than executing stored procedure code. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4dfa49a012091645294739! -- 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] Postgres 8.3.10 Alter Table Waiting issue
On 16 Jun 2011, at 18:30, Pratheeban Jebasingh Tharmaraj wrote: Hi Team, I am trying to add column to the table that's hanging. alter table hr_firms add column_name biginit; ^^ typo? This is the lock I see in the db relation | 564709 | 586888 | | || | | | | 1/8| 1871 | AccessExclusiveLock | f Version of postgres: 8.3.10 Is it known issue or any fix available? You have some uncommitted transaction (by the process with pid 1871) on that table that's preventing the database from altering the table. Close that session and it should continue. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4dfa4bb912092131128697! -- 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] Postgres performance and the Linux scheduler
On 06/16/2011 02:10 PM, Simon Windsor wrote: Can the performance of Postgres be boosted, especially on busy systems, using the none default DEADLINE Scheduler? Some system reports moderate improvements in throughput, and sometimes larger ones in worst-case latency, when switching from the default to the deadline scheduler. Others report best performance with the noop schedule. Changes here are not effective in a lot of cases though. I wrote an article for the first issue of PostgreSQL Magazine that mentions this, as the last of the most useful things you can tweak on Linux; that's available at http://pgmag.org/ -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us PostgreSQL 9.0 High Performance: http://www.2ndQuadrant.com/books
[GENERAL] Domains as typedefs: Performance and automatic casting
I plan to define two domains with no contraints, sort of typedefs, to work with date-times inside my application: CREATE DOMAIN instant AS timestamp(3) with time zone; CREATE DOMAIN localdatetime AS timestamp(3) without time zone; Two questions: 1. I guess that there is no performance penalty in using such a DOMAIN Am I right? 2. I see that PG does automatic (implicit casting) between TIMESTAMP WITH/WITHOUT TIME ZONE types, it accept one type in place of the other (a dangerous thing, IMO, as the conversion is sensitive to the current timezone) ; the same happens for these DOMAINs . I guess that I must live with this, now way to disallow it... Am I right? Hernán J. González -- 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] Constraint to ensure value does NOT exist in another table?
I know I can setup a FK constraint to make sure Table1.ColA exists in Table2.Key, however what if I want to do the reverse? I want to ensure Table1.ColA does NOT exist in Table2.Key.. Can I do this with any sort of CHECK constraint, trigger, custom function, etc? Thanks! Perhaps it's possible to use a unique constraint in a third table to guarantee those foreign keys can never have the same value. That would probably be more efficient than executing stored procedure code. You'd still have to use a TRIGGER to insert any new or updated values into the third table. Otherwise, you'd have to modify a bunch of code to insert/update the keys into the third table and that somewhat goes against the whole idea of making the database responsible for its own integrity in the first place. What I'm ideally looking for here is a way to ensure the DB cannot possibly exist in this state. Foreign keys let me do that, a trigger (if written correctly) kinda does too so long as the data started out in a valid state and the trigger is always run.. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Installing Fedora 15 hosed my db...
Yesterday I upgraded from fedora 14 to fedora 15... The install process sorta trashed my 8.4 db...here is what I have: postgresql-server-9.0.4-1.fc15.i686 postgresql-pltcl-8.4.4-1PGDG.fc12.i386 postgresql-docs-8.4.4-1PGDG.fc12.i386 postgresql-libs-9.0.4-1.fc15.i686 postgresql-contrib-8.4.4-1PGDG.fc12.i386 postgresql-9.0.4-1.fc15.i686 postgresql-plpython-8.4.4-1PGDG.fc12.i386 postgresql-devel-8.4.4-1PGDG.fc12.i386 I have a couple of other machines that that contain the db so I could do a pg_dumpall to get something to stuff into my db at the appropriate time. I would like to upgrade to 9.0.4... I could not find the contrib module for 9.0.4 module in yumex. I really need the dbink tool to keep the dbs on my machines up to date. Any suggestions on how I can cleanup the mess I created *and* get a complete 9.0.4 installation? Thanks, Jerry -- 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] PostgreSQL 8.4.8 bringing my website down every evening
Hello, I'm still suffering with my Drupal 7.2 site and PostgreSQL 8.4.8 every evening, for example right now. I have tried different combinations for /etc/pgbouncer.ini - for example now I have: [databases] pref = host=/tmp user=pref password=XXX dbname=pref [pgbouncer] logfile = /var/log/pgbouncer.log pidfile = /var/run/pgbouncer/pgbouncer.pid ;listen_addr = 127.0.0.1 listen_port = 6432 unix_socket_dir = /tmp auth_type = md5 auth_file = /var/lib/pgsql/data/global/pg_auth pool_mode = session ; If server was used more recently that this many seconds ago, ; skip the check query. Value 0 may or may not run in immidiately. server_check_delay = 10 max_client_conn = 200 default_pool_size = 20 log_connections = 1 log_disconnections = 1 log_pooler_errors = 1 I have moved Drupal 7.2 and all my PHP scripts (mostly displaying stats for the players of my card game) to use the /tmp and port 6432 (instead of 5432). During off-peak hours the site works ok. But on evenings everything stops. For example pg_top shows (why is everything idle?): last pid: 5215; load avg: 0.65, 1.64, 2.13; up 0+00:46:48 20:16:37 22 processes: 22 sleeping CPU states: 12.4% user, 0.0% nice, 0.3% system, 87.4% idle, 0.1% iowait Memory: 1187M used, 2737M free, 34M buffers, 611M cached Swap: 7812M free PID USERNAME PRI NICE SIZE RES STATE TIME WCPUCPU COMMAND 3303 postgres 160 1170M 137M sleep 3:29 10.92% 20.16% postgres: pref pref [local] idle 5045 postgres 180 1169M 168M sleep 0:49 0.00% 0.00% postgres: pref pref [local] idle 5057 postgres 160 1169M 168M sleep 0:37 0.00% 0.00% postgres: pref pref [local] idle 5000 postgres 160 1169M 168M sleep 0:34 0.00% 0.00% postgres: pref pref [local] idle 5025 postgres 180 1169M 150M sleep 0:31 0.00% 0.00% postgres: pref pref [local] idle 5030 postgres 160 1171M 152M sleep 0:29 0.00% 0.00% postgres: pref pref [local] idle 5046 postgres 180 1169M 168M sleep 0:28 0.00% 0.00% postgres: pref pref [local] idle 5001 postgres 180 1169M 168M sleep 0:24 0.00% 0.00% postgres: pref pref [local] idle 5050 postgres 160 1169M 168M sleep 0:18 0.00% 0.00% postgres: pref pref [local] idle 5047 postgres 160 1169M 168M sleep 0:18 0.00% 0.00% postgres: pref pref [local] idle 5014 postgres 160 1169M 168M sleep 0:15 0.00% 0.00% postgres: pref pref [local] idle 5038 postgres 160 1171M 73M sleep 0:13 0.00% 0.00% postgres: pref pref [local] idle 5024 postgres 190 1169M 168M sleep 0:10 0.00% 0.00% postgres: pref pref [local] idle 5004 postgres 250 1169M 52M sleep 0:10 0.00% 0.00% postgres: pref pref [local] idle 5039 postgres 160 1169M 70M sleep 0:10 0.00% 0.00% postgres: pref pref [local] idle 5059 postgres 230 1169M 150M sleep 0:10 0.00% 0.00% postgres: pref pref [local] idle 5015 postgres 160 1167M 163M sleep 0:08 0.00% 0.00% postgres: pref pref [local] idle 5003 postgres 230 1169M 167M sleep 0:08 0.00% 0.00% postgres: pref pref [local] idle 5013 postgres 250 1169M 150M sleep 0:07 0.00% 0.00% postgres: pref pref [local] idle 5051 postgres 230 1169M 150M sleep 0:07 0.00% 0.00% postgres: pref pref [local] idle 4999 postgres 250 1169M 30M sleep 0:05 0.00% 0.00% postgres: pref pref [local] idle 5216 postgres 160 1166M 4752K sleep 0:00 0.00% 0.00% postgres: pref pref [local] idle In my postgresql.conf I have: max_connections = 50 shared_buffers = 1024MB (the rest unchanged, besides log settings. I can't access logs well enough now, because I'm on bad GPRS connection from Mallorca...) In my httpd.conf I have: IfModule prefork.c StartServers 8 MinSpareServers5 MaxSpareServers 20 ServerLimit 120 MaxClients 120 MaxRequestsPerChild 4000 /IfModule In error_log I see no alarming errors, but I don't understand why does pg_top show idle? In pgbouncer.log I have: 2011-06-16 20:10:54.315 4997 LOG C-0xbab2f40: pref/pref@unix:6432 login attempt: db=pref user=pref 2011-06-16 20:10:56.472 4997 LOG C-0xbab30a0: pref/pref@unix:6432 login attempt: db=pref user=pref 2011-06-16 20:10:58.488 4997 LOG C-0xbab3200: pref/pref@unix:6432 login attempt: db=pref user=pref 2011-06-16 20:10:58.957 4997 LOG C-0xbab3360: pref/pref@unix:6432 login attempt: db=pref user=pref 2011-06-16 20:11:02.264 4997 LOG C-0xbab34c0: pref/pref@unix:6432 login attempt: db=pref user=pref 2011-06-16 20:11:05.782 4997 LOG C-0xbab3620: pref/pref@unix:6432 login attempt: db=pref user=pref 2011-06-16 20:11:09.348 4997 LOG C-0xbab3780: pref/pref@unix:6432 login attempt: db=pref user=pref 2011-06-16 20:11:12.349 4997 LOG C-0xbab38e0: pref/pref@unix:6432 login attempt: db=pref user=pref 2011-06-16 20:11:12.701 4997 LOG C-0xbab3a40: pref/pref@unix:6432 login attempt: db=pref user=pref 2011-06-16 20:11:14.452 4997 LOG C-0xbab3ba0: pref/pref@unix:6432 login attempt: db=pref user=pref
Re: [GENERAL] You could be a PostgreSQL Patch Reviewer!
Radoslaw, https://commitfest.postgresql.org/action/patch_view?id=565 https://commitfest.postgresql.org/action/patch_view?id=579 Actually, I assigned you to 580 instead of 579, because supposedly Noah is reviewing 579. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] PostgreSQL 8.4.8 bringing my website down every evening
2011/6/16 Alexander Farber alexander.far...@gmail.com: Hello, I'm still suffering with my Drupal 7.2 site and PostgreSQL 8.4.8 every evening, for example right now. I have tried different combinations for /etc/pgbouncer.ini - for example now I have: [databases] pref = host=/tmp user=pref password=XXX dbname=pref [pgbouncer] logfile = /var/log/pgbouncer.log pidfile = /var/run/pgbouncer/pgbouncer.pid ;listen_addr = 127.0.0.1 listen_port = 6432 unix_socket_dir = /tmp auth_type = md5 auth_file = /var/lib/pgsql/data/global/pg_auth pool_mode = session you probably want to use transaction mode here, instead of session. -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support -- 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] PostgreSQL 8.4.8 bringing my website down every evening
On 6/16/11, Cédric Villemain cedric.villemain.deb...@gmail.com wrote: 2011/6/16 Alexander Farber alexander.far...@gmail.com: I'm still suffering with my Drupal 7.2 site and PostgreSQL 8.4.8 every evening, for example right now. I have tried different combinations for /etc/pgbouncer.ini - for example now I have: [pgbouncer] logfile = /var/log/pgbouncer.log pidfile = /var/run/pgbouncer/pgbouncer.pid ;listen_addr = 127.0.0.1 listen_port = 6432 unix_socket_dir = /tmp auth_type = md5 auth_file = /var/lib/pgsql/data/global/pg_auth pool_mode = session you probably want to use transaction mode here, instead of session. Is transaction mode better for PHP scripts with persistent connections? I'll try that, thank you (rebooting right now... GPRS sucks) -- 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] PostgreSQL 8.4.8 bringing my website down every evening
On Thu, Jun 16, 2011 at 1:27 PM, Alexander Farber alexander.far...@gmail.com wrote: For example pg_top shows (why is everything idle?): last pid: 5215; load avg: 0.65, 1.64, 2.13; up 0+00:46:48 20:16:37 22 processes: 22 sleeping CPU states: 12.4% user, 0.0% nice, 0.3% system, 87.4% idle, 0.1% iowait Memory: 1187M used, 2737M free, 34M buffers, 611M cached Swap: 7812M free PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND 3303 postgres 16 0 1170M 137M sleep 3:29 10.92% 20.16% postgres: pref pref [local] idle 5045 postgres 18 0 1169M 168M sleep 0:49 0.00% 0.00% postgres: pref pref [local] idle 5057 postgres 16 0 1169M 168M sleep 0:37 0.00% 0.00% postgres: pref pref [local] idle 5000 postgres 16 0 1169M 168M sleep 0:34 0.00% 0.00% postgres: pref pref [local] idle 5025 postgres 18 0 1169M 150M sleep 0:31 0.00% 0.00% postgres: pref pref [local] idle 5030 postgres 16 0 1171M 152M sleep 0:29 0.00% 0.00% What do vmstat 1 and iostat -xd 1 (or equivalent for your OS) say? So here's a healthy not working too hard machine: procs ---memory-- ---swap-- -io -system-- cpu r b swpd free buff cache si sobibo in cs us sy id wa 4 0 0 552768 480820 12144179200 12400 8 1 91 0 5 0 0 556168 480820 12144166400 0 468 4005 4763 11 1 88 0 2 0 0 561660 480820 12144168000 0 164 4032 4555 10 1 88 0 1 0 0 555076 480820 1214416800012 300 4171 4710 5 0 94 0 Note that the in and cs values are pretty reasonable (interrupts and context switches / sec) and idle CPU is ok.0 wait. If those cs and in start climbing and the wait starts climbing your IO is getting saturated. In iostat keep an eye on %util -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Executing prepared statements via bind params
Hi all, I'm seeing a bit strange (some might say inconsistent) behaviour, possibly a bug. First, I prepare a statement by running the query: PREPARE bar (int) AS (SELECT 1, $1, 3) Then I try to use the generic libpq query function PQsendQueryParams with a query of EXECUTE bar(2) and I get back a result set with one record (1, 2, 3) in it. This is fine. But when I try to do the same but pas the 2 as a parameter, (I do EXECUTE bar($1) with $1 bound to 2), I get an error: ERROR: bind message supplies 1 parameters, but prepared statement requires 0 This doesn't make much sense to me. It's the same error you get when trying to run a nonparameterized query like SELECT 1 with $1 bound to anything. Does the query parser somehow miss the fact that there's a placeholder in the EXECUTE statement? I'm attempting to keep my Scheme library's API as small and simple as possible, so I'd like to avoid having a separate procedure for querying and one for executing prepared statements, considering there's also an SQL command for executing prepared statements. Is there a particular reason there are separate functions in libpq (aside from historical accident)? Cheers, Peter -- http://sjamaan.ath.cx -- The process of preparing programs for a digital computer is especially attractive, not only because it can be economically and scientifically rewarding, but also because it can be an aesthetic experience much like composing poetry or music. -- Donald Knuth -- 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] You could be a PostgreSQL Patch Reviewer!
Craig Ringer cr...@postnewspapers.com.au Thursday 16 of June 2011 06:14:13 On 16/06/11 04:49, Josh Berkus wrote: We especially need folks who are able to build PostgreSQL on Windows, as we have several Windows-specific patches and no reviewers for them. Urrrggh. Does that mean I have to volunteer myself? ;-) I kind of regret learning to develop on Windows, because I find it a horrid environment*, but so does everybody else, most of whom have been clever enough not to learn it. They can say I don't know Windows and they're safe. Any chance of flagging patches on the commitfest when they're platform specific? I'm hurting for time but will check out Windows-specific stuff if it's an area the project particularly needs help with. * Except for Visual C++'s debugger, which is a godsend, and so good it's almost worth learning to code on Windows to use. I will never use gdb for c++ again given the choice. gdb is a C debugger with bizarre delusions that it's also a C++ debugger. Most of my C++ code is cross-platform primarily so I can code on Linux, interactively debug on Windows, and use Linux for valgrind memory debugging. -- Craig Ringer I may try to review this patches about XML XPATH (as I know(n) something about XML): https://commitfest.postgresql.org/action/patch_view?id=565 https://commitfest.postgresql.org/action/patch_view?id=579 Regards, Radek -- 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] Installing Fedora 15 hosed my db...
Jerry LeVan jerry.le...@gmail.com writes: Yesterday I upgraded from fedora 14 to fedora 15... The install process sorta trashed my 8.4 db...here is what I have: postgresql-server-9.0.4-1.fc15.i686 postgresql-pltcl-8.4.4-1PGDG.fc12.i386 postgresql-docs-8.4.4-1PGDG.fc12.i386 postgresql-libs-9.0.4-1.fc15.i686 postgresql-contrib-8.4.4-1PGDG.fc12.i386 postgresql-9.0.4-1.fc15.i686 postgresql-plpython-8.4.4-1PGDG.fc12.i386 postgresql-devel-8.4.4-1PGDG.fc12.i386 Any suggestions on how I can cleanup the mess I created *and* get a complete 9.0.4 installation? Well, you could (1) remove all the PGDG RPMs, (2) install the regular Fedora postgresql RPMs, including postgresql-upgrade (3) run service postgresql upgrade (I haven't actually tested postgresql-upgrade in the context of upgrading from 8.4 PGDG RPMs instead of the regular Fedora ones, but in theory it should work. As always, a filesystem backup of your database is a good thing to have in case of disaster.) 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] PostgreSQL 8.4.8 bringing my website down every evening
On 6/16/11, Scott Marlowe scott.marl...@gmail.com wrote: What do vmstat 1 and iostat -xd 1 (or equivalent for your OS) say? So here's a healthy not working too hard machine: procs ---memory-- ---swap-- -io -system-- cpu r b swpd free buff cache si sobibo in cs us sy id wa 4 0 0 552768 480820 12144179200 12400 8 1 91 0 Note that the in and cs values are pretty reasonable (interrupts and context switches / sec) and idle CPU is ok.0 wait. If those cs and in start climbing and the wait starts climbing your IO is getting saturated. In iostat keep an eye on %util It shows (but I have switched pgbouncer from session to transaction mode now and it seems to have helped - Drupal+my scripts are running again and pg_top shows 2-3 SELECTs at the top and the rest is idle): # vmstat 1 procs ---memory-- ---swap-- -io --system-- -cpu-- r b swpd free buff cache si sobibo in cs us sy id wa st 3 1 0 2896644 30748 6177440070 359 350 260 61 1 37 1 0 2 0 0 2875252 30760 61951600 8 140 1333 1231 46 1 53 0 0 2 0 0 2873888 30768 61816000 0 332 1293 1294 59 1 40 1 0 2 0 0 2871904 30768 61992000 016 1257 453 38 0 61 0 0 4 0 0 2872896 30768 61813600 024 1204 574 51 1 48 0 0 2 1 0 2868308 30768 62209600 0 5608 1304 603 66 1 33 0 0 2 0 0 2870628 30780 62068800 0 236 1329 999 53 1 46 2 0 0 0 0 2873356 30792 61681600 088 1247 696 13 1 85 0 0 1 0 0 2873736 30792 61670800 080 1270 745 20 0 80 0 0 0 0 0 2873736 30792 61670800 044 1234 535 10 1 89 0 0 0 0 0 2873800 30804 61670800 0 252 1249 576 13 0 86 0 0 2 0 0 2900708 30804 61693200 072 1340 646 21 1 79 0 0 2 0 0 2898360 30812 61890800 0 160 1549 799 45 0 54 0 0 3 0 0 2895004 30812 62144800 0 0 1622 829 46 2 52 0 0 2 0 0 2896068 30812 61932000 072 1501 1115 50 0 50 0 0 3 0 0 2897804 30820 61719600 048 1445 1044 50 1 49 0 0 1 0 0 2895820 30832 61902400 0 1128 1431 826 44 0 55 0 0 3 0 0 2893292 30844 62121200 088 1353 1379 52 1 47 0 0 2 0 0 2894424 30844 62038000 056 1298 668 63 1 36 0 0 3 0 0 2890580 30852 62433200 0 8 1251 480 60 1 39 0 0 5 0 0 2895696 30860 61868800 080 1304 876 75 1 24 0 0 6 0 0 2890612 30876 62461200 0 3636 1437 610 87 1 12 0 0 4 0 0 2891908 30900 62238800 0 112 1316 960 99 1 0 0 0 3 0 0 2890544 30908 62436000 0 0 1279 1174 97 0 3 0 0 2 0 0 2891908 30912 62386000 0 112 1289 1319 80 1 19 0 0 2 0 0 2896868 30924 61767200 088 1242 764 53 0 47 0 0 1 0 0 2894264 30936 61950400 0 1492 1433 733 44 1 54 0 0 1 0 0 2894884 30944 61682400 084 1556 846 39 1 60 0 0 2 0 0 2894544 30944 61678400 024 1436 710 14 1 85 0 0 3 0 0 2871412 30944 618220001696 1335 1511 48 1 51 0 0 2 0 0 2867444 30944 62233200 0 332 1429 546 61 1 38 0 0 3 0 0 2867196 30956 62198400 0 6684 1522 1033 75 1 24 0 0 And I have RAID1, but it seems to be ok right now: # cat /proc/mdstat Personalities : [raid1] md0 : active raid1 sdb1[1] sda1[0] 1023936 blocks [2/2] [UU] md2 : active raid1 sdb5[1] sda5[0] 277728192 blocks [2/2] [UU] md3 : active raid1 sdb6[1] sda6[0] 185151360 blocks [2/2] [UU] md1 : active raid1 sdb3[1] sda3[0] 20479936 blocks [2/2] [UU] unused devices: none Regards Alex -- 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] Installing Fedora 15 hosed my db...
On Thu, 2011-06-16 at 16:05 -0400, Tom Lane wrote: Jerry LeVan jerry.le...@gmail.com writes: Yesterday I upgraded from fedora 14 to fedora 15... The install process sorta trashed my 8.4 db...here is what I have: postgresql-server-9.0.4-1.fc15.i686 postgresql-pltcl-8.4.4-1PGDG.fc12.i386 postgresql-docs-8.4.4-1PGDG.fc12.i386 postgresql-libs-9.0.4-1.fc15.i686 postgresql-contrib-8.4.4-1PGDG.fc12.i386 postgresql-9.0.4-1.fc15.i686 postgresql-plpython-8.4.4-1PGDG.fc12.i386 postgresql-devel-8.4.4-1PGDG.fc12.i386 Any suggestions on how I can cleanup the mess I created *and* get a complete 9.0.4 installation? Well, you could (1) remove all the PGDG RPMs, (2) install the regular Fedora postgresql RPMs, including postgresql-upgrade (3) run service postgresql upgrade (I haven't actually tested postgresql-upgrade in the context of upgrading from 8.4 PGDG RPMs instead of the regular Fedora ones, but in theory it should work. As always, a filesystem backup of your database is a good thing to have in case of disaster.) regards, tom lane Oh snap! I had excluded postgresql from the fedora repo because I had been using pgdg repo. When I enabled postgresql in the repo all of the missing rpms magically appeared in the yumex update section :0 Jerry -- 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] PostgreSQL 8.4.8 bringing my website down every evening
On Thu, Jun 16, 2011 at 2:08 PM, Alexander Farber alexander.far...@gmail.com wrote: It shows (but I have switched pgbouncer from session to transaction mode now and it seems to have helped - Drupal+my scripts are running again and pg_top shows 2-3 SELECTs at the top and the rest is idle): # vmstat 1 procs ---memory-- ---swap-- -io --system-- -cpu-- r b swpd free buff cache si so bi bo in cs us sy id wa st 3 1 0 2896644 30748 617744 0 0 70 359 350 260 61 1 37 1 0 2 0 0 2875252 30760 619516 0 0 8 140 1333 1231 46 1 53 0 0 2 0 0 2873888 30768 618160 0 0 0 332 1293 1294 59 1 40 1 0 2 0 0 2871904 30768 619920 0 0 0 16 1257 453 38 0 61 0 0 4 0 0 2872896 30768 618136 0 0 0 24 1204 574 51 1 48 0 0 2 1 0 2868308 30768 622096 0 0 0 5608 1304 603 66 1 33 0 0 2 0 0 2870628 30780 620688 0 0 0 236 1329 999 53 1 46 2 0 I assume that this is with things working properly. Keep an eye on it when the server is running slow to see what's changing. So far it looks like that one change may have made a difference but I wouldn't assume you're completely out of the woods just yet. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgres performance and the Linux scheduler
Hi Can the performance of Postgres be boosted, especially on busy systems, using the none default DEADLINE Scheduler? Simon Simon Windsor Eml: mailto:simon.wind...@cornfield.org.uk simon.wind...@cornfield.org.uk Tel: 01454 617689 Mob: 07590 324560 There is nothing in the world that some man cannot make a little worse and sell a little cheaper, and he who considers price only is that man's lawful prey.
Re: [GENERAL] Executing prepared statements via bind params
Peter Bex peter@xs4all.nl writes: But when I try to do the same but pas the 2 as a parameter, (I do EXECUTE bar($1) with $1 bound to 2), I get an error: Why would you do that, rather than executing the prepared statement directly with PQexecPrepared? Interposing an EXECUTE doesn't do anything but add parsing overhead. The reason this particular case doesn't work is that utility statements (including EXECUTE) don't have any support for $n parameters. 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] Executing prepared statements via bind params
On Thu, Jun 16, 2011 at 05:19:41PM -0400, Tom Lane wrote: Peter Bex peter@xs4all.nl writes: But when I try to do the same but pas the 2 as a parameter, (I do EXECUTE bar($1) with $1 bound to 2), I get an error: Why would you do that, rather than executing the prepared statement directly with PQexecPrepared? I'm writing a Scheme language binding and currently I simply don't have any bindings for this function, and a user of this library was experimenting with some optimizations for his code and ran into this. I was kind of hoping to avoid having too many special-purpose functions and since there's also an SQL EXECUTE function, PQexecPrepared seemed a bit redundant. Interposing an EXECUTE doesn't do anything but add parsing overhead. Is this parsing overhead of an EXECUTE statement (which should be very short and simple) *that* significant compared to the savings you get when preparing a complex SQL statement which is executed many times? The reason this particular case doesn't work is that utility statements (including EXECUTE) don't have any support for $n parameters. Why not, is it simply something nobody ever needed? It seems rather logical to be able to replace any literal by an equivalent parameter in any SQL statement. I should probably add support for PQexecPrepared at some point but even then, as a user, I'd probably expect this to be possible for reasons of symmetry and regularity. It might also make it easier for certain types of generated SQL. Cheers, Peter -- http://sjamaan.ath.cx -- The process of preparing programs for a digital computer is especially attractive, not only because it can be economically and scientifically rewarding, but also because it can be an aesthetic experience much like composing poetry or music. -- Donald Knuth -- 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] interval hour to minute or interval day to minute
Noah Misch wrote: On Sun, Apr 17, 2011 at 04:55:51PM +0100, Jack Douglas wrote: I discovered the 'fields' option of 'interval', but i can't figure out from the docs how it is supposed to work. Are hour to minute and day to minute really the same thing? And if not, in what circumstances are they treated differently? As of version 8.4, they behave identically. The code has this comment, some form of which probably belongs in the documentation: /* * Our interpretation of intervals with a limited set of fields is * that fields to the right of the last one specified are zeroed out, * but those to the left of it remain valid. Thus for example there * is no operational difference between INTERVAL YEAR TO MONTH and * INTERVAL MONTH. In some cases we could meaningfully enforce that * higher-order fields are zero; for example INTERVAL DAY could reject * nonzero month field. However that seems a bit pointless when we * can't do it consistently. (We cannot enforce a range limit on the * highest expected field, since we do not have any equivalent of * SQL's interval leading field precision.) * * Note: before PG 8.4 we interpreted a limited set of fields as * actually causing a modulo operation on a given value, potentially * losing high-order as well as low-order information. But there is * no support for such behavior in the standard, and it seems fairly * undesirable on data consistency grounds anyway. Now we only * perform truncation or rounding of low-order fields. */ I am lost on how we could mention that in the docs. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Installing Fedora 15 hosed my db...
On Jun 16, 2011, at 5:16 PM, Dagan McGregor wrote: On Thu, 16 Jun 2011 15:01:30 -0400, Jerry LeVan wrote: Yesterday I upgraded from fedora 14 to fedora 15... The install process sorta trashed my 8.4 db...here is what I have: postgresql-server-9.0.4-1.fc15.i686 postgresql-pltcl-8.4.4-1PGDG.fc12.i386 postgresql-docs-8.4.4-1PGDG.fc12.i386 postgresql-libs-9.0.4-1.fc15.i686 postgresql-contrib-8.4.4-1PGDG.fc12.i386 postgresql-9.0.4-1.fc15.i686 postgresql-plpython-8.4.4-1PGDG.fc12.i386 postgresql-devel-8.4.4-1PGDG.fc12.i386 [...] Any suggestions on how I can cleanup the mess I created *and* get a complete 9.0.4 installation? Just curious, why do you have Fedora 12 packages in the package list, if you're running Fedora 14? If you've been upgrading from Fedora 12, that would be part of the problem. Fedora doesn't update between older versions all that well. I'd suggest, if you do have a data back-up, it would be easier to do a clean install of Fedora 15. Dagan As I noted earlier today, I had made postgresql 'invisible' to yum in the fedora repo... When I made it visible all of the missing f15 postgresql rpms showed up in yumex. When I installed the missing rpms the older fedora rpms vanished and I was able to do as Tom suggested: service postgresql pg_upgrade and the conversion was successful :) I am now running version Pg 9.0.4. Jerry -- 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] set returning function with variable argument - possible?
The only way to feed another relation into a function is to place the function in the SELECT list. If you then want to expand the result of the function call you alias the function result and then .* against it. WITH feeder AS ( SELECT f(var) AS result FROM xtable WHERE ... ) SELECT (feeder.result).* FROM feeder ; A similar query form: SELECT f(var).* FROM xtable WHERE ... Will cause f to be executed once for every column that f would expand into. For SELECT only functions this causes a performance hit (though you do get the correct results) but if the function executes any INSERTS (or other DDL) the result of executing each statement multiple times in the same transaction can either cause the function to simply fail or, even worse, succeed but cause unintended effects. Supposedly, due to sub-query inlining, the following form is not truly safe: SELECT (sub.field).* FROM ( SELECT f(var) AS result FROM xtable WHERE ... ) sub David J. -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of Frank Miles Sent: Thursday, June 16, 2011 12:18 AM To: pgsql-general@postgresql.org Subject: [GENERAL] set returning function with variable argument - possible? I have a function that returns a set of a defined type, something like: CREATE OR REPLACE FUNCTION f(arg) RETURNS SETOF return_type AS $$ ... $$ LANGUAGE 'plpgsql' STABLE with (isstrict); Ordinarily this function is called: SELECT * FROM f(constant); But what I want to do is more like: SELECT * FROM f(var) WHERE var IN (SELECT var FROM xtable WHERE ...); This fails, as does SELECT * FROM f(var) WHERE var IN (constant); - Is there some syntax that will allow me to perform this operation? Note that there are differing ways for arg to be determined, so I'd rather not merge this operation into f() - I would have to have a number of f()'s, each with its own mechanism for developing its args. - Running Postgresql 8.4.7 on Debian 'squeeze'. Thanks for any pointers to documentation that I have overlooked in my search! -f -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ncoding Table Name and Filed Name
Hello there, I request you to give your commends on the following, 1. We have planned to cipher our table name and filed name [the necessary data will be ciphered with predefined functions with 'key'] 2. This cipher functions will be our own code 3. Using these functions we can mange the applications We are expecting following advantages through this, Unauthorized use either by the 1. DB administrator 2. ex-developer Or 3. Any body My question is, DO i face any negative project management problems by doing this? Pleas share your experience on this aspect and commend our idea. Thank you Vikram A
Re: Fw: [GENERAL] PostgreSQL 9.0 or 9.1 ?
Thanx, i think i'll just stick with 9.0 and try to take full advantage of it and when we are comfortable with all those features then move to 9.1 From: Merlin Moncure mmonc...@gmail.com To: Achilleas Mantzios mantzios.ach...@yahoo.com Cc: pgsql-general@postgresql.org pgsql-general@postgresql.org Sent: Thursday, June 16, 2011 7:12 PM Subject: Re: [GENERAL] PostgreSQL 9.0 or 9.1 ? On Thu, Jun 16, 2011 at 2:47 AM, Achilleas Mantzios mantzios.ach...@yahoo.com wrote: Hello, we have been running our infrastructure on 8.3 for quite some years now, and i am thinking it is now time to upgrade all major parts of our system (java, jboss, postgresql). I would tend to be a little radical and go a little optimistic and greedy about it. I have been using 9.0 as a test system with no major flaws for quite some time as well. Till the end of July i must have finished all the migration to the new versions. The migration will involve testing of about 5,458 sql statements and the migration of some heavily customized in house functions, including a version of DBmirror (which is in use for a very specific set of problems) So i am asking what would be better by your perspective to do? Go for 9.1? or stick to 9.0? Where is a stable (release) version of 9.1 be available? Has any one faced any issues migrating from 9.0 to 9.1 Are you looking for any features that 9.1 has to offer?� If you aren't, it may make your decision easier.� Unfortunately there are several 9.1 features that are just awesome.� So, where you go from here is going to depend on your risk tolerance and (more importantly) your availability of testing resources.� Testing of production-ish workloads during the beta period are very much appreciated by the community, so feel free to give it a shot as long as you understand the risk involved (which are substantial). One big risk with 9.1 early adoption is that you run the risk of having to dump/reload if you go production while in the before the build hits release candidate status (and sometimes, even then).� So, if you are running a 24x7 duty cycle that's something to think about. merlin -- Achilleas Mantzios -- 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] PostgreSQL 9.0 or 9.1 ?
Thanx brad, i think 9.0 would be the most wise decision for the time being. Στις Thursday 16 June 2011 18:29:16 γράψατε: -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of Grzegorz Jaskiewicz Sent: Thursday, June 16, 2011 11:05 AM To: Achilleas Mantzios Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] PostgreSQL 9.0 or 9.1 ? It could be worth considering 9.1. Probably by the time you get production ready version, 9.1 will be already stable (few months I guess). The usual answer to that question is - it will be ready when its ready. I would also ask, what is your (and your managements) tolerance for risk, and do you actually need any of the new features and/or performance benefits in 9.1? Postgres does have an excellent track record for quality and stability with new releases, but a couple of months in the field isn't really considered stable in most places. Brad. -- Achilleas Mantzios -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
