Re: [GENERAL] Simple Atomic Relationship Insert
On 1/16/2015 2:41 AM, Jim Nasby wrote: On 1/15/15 10:57 PM, Roxanne Reid-Bennett wrote: try this: (if you still get deadlocks, uncomment the advisory lock [thanks Daniel] and try again) Logically I suppose it might run faster to do the select, then insert if. I almost always write these as insert first - because it's the more restrictive lock. CREATE OR REPLACE FUNCTION select_hometown_id(hometown_name VARCHAR) RETURNS INTEGER AS $BODY$ DECLARE v_id integer; BEGIN --perform pg_advisory_xact_lock(hashtext(hometown_name)); BEGIN insert into hometowns (name) select hometown_name where not exists (select id from hometowns where name = hometown_name) returning id into v_id; That has a race condition. The only safe way to do this (outside of SSI) is using the example code at http://www.postgresql.org/docs/devel/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING And if the advisory lock is used? That presumably creates an exclusive lock on the asset hometown_name. [in most examples given Portland, OR.] Would not any other process that runs (this function) on the same asset have to wait for this specific transaction to commit or roll back - blocking the race condition? Roxanne (sorry, I was out of town) -- 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] Partitioning
Hello, Thanks for the reply. I can write queries which get index scans, but they are still slow. Each index is about 2.5GB, I suspect I am trying to read a these into memory in entirety. Perhaps there is no way to tune this? Cheers, james On Monday, 19 January 2015, Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp wrote: Hello, an 2015 14:13:37 +1100, James Sewell james.sew...@lisasoft.com javascript:; wrote in cankgpbs8gypq3tqgkdjtd+n-w1rkq5uo97h3tuhg5ewakr6...@mail.gmail.com javascript:; Sadly not ... I still hit all the tables. | 5.9.4. Partitioning and Constraint Exclusion http://www.postgresql.org/docs/9.4/static/ddl-partitioning.html Constraint exclusion is a mechanism to omit tables that are known to have no hit by the query *beforehand* execution. So the criteria cannot rely on out of the query itself (and CHECK constraints, of course). Your query uses the result of the WITH-clause-query in the WHERE clause which is unknown to the planner so constraint exclusion does not work. JOINs don't change the situation. On Mon, Jan 19, 2015 at 1:54 PM, John R Pierce pie...@hogranch.com javascript:; wrote: On 1/18/2015 5:58 PM, James Sewell wrote: WITH idlist as (SELECT id from othertable) SELECT id from mastertable WHERE id = idlist.id); select mt.id, ... from mastertable mt join othertable ot on mt.id= ot.id; might optimize better. As the result, the query inevitably scans all the tables, but not necessariry in sequqntial scans or simple index scans. The suggestion above seeems showing the notation which the planner can find the better plans on that premise. For example, if you have an index on id of one of the two tables, (and some other conditions match, of course) index only scan will be selected for it and the suggested query will give you a seemingly better plan than your query. regards, -- Kyotaro Horiguchi NTT Open Source Software Center -- James Sewell, PostgreSQL Team Lead / Solutions Architect __ Level 2, 50 Queen St, Melbourne VIC 3000 *P *(+61) 3 8370 8000 *W* www.lisasoft.com *F *(+61) 3 8370 8099 -- -- The contents of this email are confidential and may be subject to legal or professional privilege and copyright. No representation is made that this email is free of viruses or other defects. If you have received this communication in error, you may not copy or distribute any part of it or otherwise disclose its contents to anyone. Please advise the sender of your incorrect receipt of this correspondence.
Re: [GENERAL] Partitioning
Sadly not ... I still hit all the tables. Cheers, James Sewell, PostgreSQL Team Lead / Solutions Architect __ Level 2, 50 Queen St, Melbourne VIC 3000 *P *(+61) 3 8370 8000 *W* www.lisasoft.com *F *(+61) 3 8370 8099 On Mon, Jan 19, 2015 at 1:54 PM, John R Pierce pie...@hogranch.com wrote: On 1/18/2015 5:58 PM, James Sewell wrote: WITH idlist as (SELECT id from othertable) SELECT id from mastertable WHERE id = idlist.id); select mt.id, ... from mastertable mt join othertable ot on mt.id= ot.id; might optimize better. -- john r pierce 37N 122W somewhere on the middle of the left coast -- -- The contents of this email are confidential and may be subject to legal or professional privilege and copyright. No representation is made that this email is free of viruses or other defects. If you have received this communication in error, you may not copy or distribute any part of it or otherwise disclose its contents to anyone. Please advise the sender of your incorrect receipt of this correspondence.
Re: [GENERAL] Partitioning
Hello, an 2015 14:13:37 +1100, James Sewell james.sew...@lisasoft.com wrote in cankgpbs8gypq3tqgkdjtd+n-w1rkq5uo97h3tuhg5ewakr6...@mail.gmail.com Sadly not ... I still hit all the tables. | 5.9.4. Partitioning and Constraint Exclusion http://www.postgresql.org/docs/9.4/static/ddl-partitioning.html Constraint exclusion is a mechanism to omit tables that are known to have no hit by the query *beforehand* execution. So the criteria cannot rely on out of the query itself (and CHECK constraints, of course). Your query uses the result of the WITH-clause-query in the WHERE clause which is unknown to the planner so constraint exclusion does not work. JOINs don't change the situation. On Mon, Jan 19, 2015 at 1:54 PM, John R Pierce pie...@hogranch.com wrote: On 1/18/2015 5:58 PM, James Sewell wrote: WITH idlist as (SELECT id from othertable) SELECT id from mastertable WHERE id = idlist.id); select mt.id, ... from mastertable mt join othertable ot on mt.id= ot.id; might optimize better. As the result, the query inevitably scans all the tables, but not necessariry in sequqntial scans or simple index scans. The suggestion above seeems showing the notation which the planner can find the better plans on that premise. For example, if you have an index on id of one of the two tables, (and some other conditions match, of course) index only scan will be selected for it and the suggested query will give you a seemingly better plan than your query. regards, -- Kyotaro Horiguchi NTT Open Source Software Center -- 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] Partitioning
On 1/18/2015 5:58 PM, James Sewell wrote: WITH idlist as (SELECT id from othertable) SELECT id from mastertable WHERE id = idlist.id http://idlist.id); select mt.id, ... from mastertable mt join othertable ot on mt.id=ot.id; might optimize better. -- john r pierce 37N 122W somewhere on the middle of the left coast
[GENERAL] Partitioning
Hello, I am using partitioning with around 100 sub-tables. Each sub-table is around 11GB and partitioned on the 'id' column. I have an index on the id column on each sub-table. Is it possible to get a query like the following working using constraint exclusion, or am I doomed to do index/sequential scans of every sub-table? I want to select all rows which have an id which is in another query, so something like: WITH idlist as (SELECT id from othertable) SELECT id from mastertable WHERE id = idlist.id); I am guessing that I am not getting constraint exclusion to work as the planner doesn't know the outcome of my subquery at plan time? Any tricks I am overlooking? James Sewell, PostgreSQL Team Lead / Solutions Architect __ Level 2, 50 Queen St, Melbourne VIC 3000 *P *(+61) 3 8370 8000 *W* www.lisasoft.com *F *(+61) 3 8370 8099 -- -- The contents of this email are confidential and may be subject to legal or professional privilege and copyright. No representation is made that this email is free of viruses or other defects. If you have received this communication in error, you may not copy or distribute any part of it or otherwise disclose its contents to anyone. Please advise the sender of your incorrect receipt of this correspondence.
Re: [GENERAL] SSO Windows-to-unix
Anybody have an help on this topic? Thanks Jeremy From: Guillaume Lelarge [guilla...@lelarge.info] Sent: Wednesday, 14 January 2015 8:20 p.m. To: Jeremy Palmer Cc: PostgreSQL General; raghuchenn...@gmail.com Subject: Re: [GENERAL] SSO Windows-to-unix Le 14 janv. 2015 05:22, Jeremy Palmer jpal...@linz.govt.nzmailto:jpal...@linz.govt.nz a écrit : I think PgAdmin is just a client that uses libpq and does not specifically help with SSO. You're definitely right about that. From: Raghu Ram [mailto:raghuchenn...@gmail.commailto:raghuchenn...@gmail.com] Sent: Tuesday, 13 January 2015 10:22 p.m. To: Jeremy Palmer Subject: Re: [GENERAL] SSO Windows-to-unix On Tue, Jan 13, 2015 at 5:15 AM, Jeremy Palmer jpal...@linz.govt.nzmailto:jpal...@linz.govt.nz wrote: Hi All, I'm just investigating the option for configuring SSO for windows clients connecting to a PostgreSQL 9.3 server installed on Ubuntu 14.04. Our windows environment uses a Windows 2012 domain controller. The best information I could find on this subject was http://www.hagander.net/talks/Deploying%20PostgreSQL%20in%20a%20Windows%20Enterprise.pdf, but I see it's dated 2008. Could someone confirm that this is still the best how-to guide for this subject and if there are any other considerations with newer versions of PostgreSQL? Many thanks, Jeremy You can use Graphical User interface tool i.e PgAdmin-III to connect PostgreSQL Database running on Ubuntu Server. http://www.pgadmin.org/ Thanks Regards Raghu Ram This message contains information, which may be in confidence and may be subject to legal privilege. If you are not the intended recipient, you must not peruse, use, disseminate, distribute or copy this message. If you have received this message in error, please notify us immediately (Phone 0800 665 463 or i...@linz.govt.nzmailto:i...@linz.govt.nz) and destroy the original message. LINZ accepts no responsibility for changes to this email, or for any attachments, after its transmission from LINZ. Thank 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] Partitioning
On 1/18/2015 11:13 PM, James Sewell wrote: Each index is about 2.5GB, I suspect I am trying to read a these into memory in entirety. an 11GB table with a (presumably integer) primary key requires an 2.5GB index ? 100 of these would need 250GB of shared_buffers to stay resident, not likely. -- john r pierce 37N 122W somewhere on the middle of the 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] sslcompression / PGSSLCOMPRESSION not behaving as documented?
On Fri, Jan 16, 2015 at 08:41:54AM -0800, Adrian Klaver wrote: Yes that would seem to be the issue: https://launchpad.net/ubuntu/trusty/+source/openssl/+changelog openssl (1.0.1e-3ubuntu1) Disable compression to avoid CRIME systemwide (CVE-2012-4929). FWIW, it's likely that the next version of TLS (version 1.3, see[1]) will no longer support compression at all. The concensus appears to be that this is the wrong level to be applying compression. Since the only way to get compression currently in Postgres is via TLS, perhaps we should look at supporting compression natively in future protocol versions. It will take a while for TLS 1.3 to be deployed so there's time, but PostgreSQL protocol revisions go at a similar pace. Have a nice day, [1] https://github.com/tlswg/tls13-spec -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
[GENERAL] PG user group in the Kuala Lumpur area?
Hi, I was looking for a PG user group around KL. I know there is one in Singapore. As it happens, Chris Travers, the PG contact for Malaysia is a friend of mine. So, I asked him. He wasn't aware of one either. However, he very much appreciated the idea of founding one. I know there are lots of PG users in the area. But is there enough demand for a user group? If you are interested, please contact me. My idea behind this whole thing is to eventually have a regular PG conference South East Asia. I have been to PGconf.eu several times and I know from experience that it is a great opportunity to learn new stuff, meet people and also have much fun. I think esp. Malaysia is a good place for such an event. There are many people out there that could never come to PGconf.eu or similar in the US and in many other places because of their passport. Getting a visa to Malaysia is possible for almost everyone. I don't know about North Korea, but there are many Iranians around here. About myself, I am German, currently traveling back and forth between Germany and Malaysia. Torsten -- 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] Can pg_restore produce create or replace commands
On 01/17/2015 06:01 PM, Berend Tober wrote: Thomas Kellerer wrote: Berend Tober wrote on 17.01.2015 19:05: I often work with the output of pg_restore from a custom format dump file. ... Most often, I'm refactoring functions and so don't really want to drop the function but rather want to do a create or replace function ... To me this sounds as if you are doing it the wrong way round. Possibly. But if the revision control system and the production data base disagree, then which one do you believe? I guess this depends on what you see as disagree. It is entirely possible that the latest version(say testing) of code in the VCS is not the same as the code in the production database. That is what tags are for, a way to mark a point in time(development) to refer to. Having a tag in the VCS that matches a state of the production database allows one to make a comparison and be confident that the version control code is the correct code. This of course requires an agreed upon method of applying changes and tagging code. If you search -general you will find previous discussions on this, for example: http://www.postgresql.org/message-id/CAPTJ3=cj5kb0y9duaa6rqh8yhqb5mssn1fvrfumgqltoq1+...@mail.gmail.com To manage (refactor) your functions, you should have the current code stored in a version control system, update the code there an then apply it to the target database. Extracting the code from the database in order to do refactoring is like disassembling a program each time you want to apply a bugfix. The code in the vcs would then contain the necessary create or replace (btw you still need to drop the function if you change the parameters) --- This email is free from viruses and malware because avast! Antivirus protection is active. http://www.avast.com -- Adrian Klaver adrian.kla...@aklaver.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 user group in the Kuala Lumpur area?
On 15/01/18 23:12, Torsten Förtsch wrote: Hi, I was looking for a PG user group around KL. I know there is one in Singapore. As it happens, Chris Travers, the PG contact for Malaysia is a friend of mine. So, I asked him. He wasn't aware of one either. However, he very much appreciated the idea of founding one. I know there are lots of PG users in the area. But is there enough demand for a user group? If you are interested, please contact me. My idea behind this whole thing is to eventually have a regular PG conference South East Asia. I have been to PGconf.eu several times and I know from experience that it is a great opportunity to learn new stuff, meet people and also have much fun. I think esp. Malaysia is a good place for such an event. There are many people out there that could never come to PGconf.eu or similar in the US and in many other places because of their passport. Getting a visa to Malaysia is possible for almost everyone. I don't know about North Korea, but there are many Iranians around here. About myself, I am German, currently traveling back and forth between Germany and Malaysia. I've yet to meet someone from Malaysia, but there's a SE Asia PostgreSQL group on Facebook which I seem to have been added to recently: https://www.facebook.com/groups/PGSQL.sg/?fref=ts The last Japan PG conference had an international track which was mainly Asian, and there's talk of setting up a PGasia conference. (Personally I'm British but kind of from Germany, now in Japan). Regards Ian Barwick -- Ian Barwick http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, RemoteDBA, 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] WAL supported extension
On Sun, Jan 18, 2015 at 9:15 AM, Michael Paquier michael.paqu...@gmail.com wrote: On Sat, Jan 17, 2015 at 11:18 PM, Kouhei Sutou k...@cozmixng.org wrote: (Is this mailing list right mailing list for asking this question...?) Hackers would have been fine as well. Is there any plan to implement PostgreSQL API to implement WAL supported extension? Not that I know of, the last discussion I recall on the matter being this one: http://www.postgresql.org/message-id/capphfdsxwzmojm6dx+tjnpyk27kt4o7ri6x_4oswcbyu1rm...@mail.gmail.com -- We are eager for development of this API. Michael -- 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] WAL supported extension
Oleg Bartunov wrote: We are eager for development of this API. Yeah, me too actually :) Oleg, are there plans on your side to do something in this area for 9.6? -- Michael -- 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] WAL supported extension
On Sun, Jan 18, 2015 at 2:01 PM, Michael Paquier michael.paqu...@gmail.com wrote: Oleg Bartunov wrote: We are eager for development of this API. Yeah, me too actually :) Oleg, are there plans on your side to do something in this area for 9.6? Yes, Alexander Korotkov will continue working on this. Are you coming Moscow for pgconf.ru ? We'll have a good time to discuss this. Oleg -- Michael
Re: [GENERAL] WAL supported extension
Hi, In CAB7nPqTjutzT==wHNrx5=4feRM9O=5-ph1acavrvut4wabj...@mail.gmail.com Re: [GENERAL] WAL supported extension on Sun, 18 Jan 2015 15:15:51 +0900, Michael Paquier michael.paqu...@gmail.com wrote: (Is this mailing list right mailing list for asking this question...?) Hackers would have been fine as well. Thanks for your advice. I'll use hackers mailing list at the next time. Is there any plan to implement PostgreSQL API to implement WAL supported extension? Not that I know of, the last discussion I recall on the matter being this one: http://www.postgresql.org/message-id/capphfdsxwzmojm6dx+tjnpyk27kt4o7ri6x_4oswcbyu1rm...@mail.gmail.com Thanks for the information. It's what I want. Thanks, -- kou -- 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] Surrogate pairs in UTF-8
Martijn van Oosterhout klep...@svana.org writes: On Fri, Jan 16, 2015 at 08:16:47AM -0600, Dave Rosckes wrote: I have written a test program using postgres that creates a string with a surrogate pair. I then insert that string into a varchar property in a table. I then execute a select statement to pull the string out. But when I evaluate the string the lead char of the pair is correct, but the following pair value is mangled. I run this exact same code using DB2 and it works just fine. Is this a postgres limitation, or is there a specific way surrogate pairs need to be handled? Sounds odd. Can you provide actual queries showing the problem (and server version). Surrogate pairs are illegal in UTF-8, per its specification at http://www.faqs.org/rfcs/rfc3629.html You're supposed to encode the underlying code point, not a surrogate pair (those are a UTF-16ism). So if what you passed in was actually a surrogate pair, it should have failed encoding validity check, or possibly have gotten converted to the underlying single Unicode character depending on exactly what code path is involved. 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
[GENERAL] Any changes in Java and PGSQL 9.4?
Just noticed that a column field of type bigint containing a null, becomes a 0 when retrieving it from the resultset using JDBC. If I run the same application against my old 9.3 database, the value retrieved is a null Regards, BTJ -- --- Bjørn T Johansen b...@havleik.no --- Someone wrote: I understand that if you play a Windows CD backwards you hear strange Satanic messages To which someone replied: It's even worse than that; play it forwards and it installs Windows --- -- 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] Any changes in Java and PGSQL 9.4?
Bjørn T Johansen wrote on 18.01.2015 20:20: Just noticed that a column field of type bigint containing a null, becomes a 0 when retrieving it from the resultset using JDBC. If I run the same application against my old 9.3 database, the value retrieved is a null You need to show us some code. getInt() or getLong() can never return null because primitives can't be null in Java. -- 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] Any changes in Java and PGSQL 9.4?
On Sun, 18 Jan 2015 20:20:35 +0100 Bjørn T Johansen b...@havleik.no wrote: Just noticed that a column field of type bigint containing a null, becomes a 0 when retrieving it from the resultset using JDBC. If I run the same application against my old 9.3 database, the value retrieved is a null Regards, BTJ Sorry, my fault... I tried running against my old database with my workaround to set the value to null.. Maybe it's a new Spring feature as am using a newer version of Spring also BTJ -- 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] Any changes in Java and PGSQL 9.4?
On Sun, 18 Jan 2015 20:25:48 +0100 Thomas Kellerer spam_ea...@gmx.net wrote: Bjørn T Johansen wrote on 18.01.2015 20:20: Just noticed that a column field of type bigint containing a null, becomes a 0 when retrieving it from the resultset using JDBC. If I run the same application against my old 9.3 database, the value retrieved is a null You need to show us some code. getInt() or getLong() can never return null because primitives can't be null in Java. Yes, you are absolutely correct.. I was fooled by the capital L in getLong but I see that it returns long.. I guess I just hadn't triggered this problem before... BTJ -- 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] Surrogate pairs in UTF-8
On Fri, Jan 16, 2015 at 08:16:47AM -0600, Dave Rosckes wrote: I have written a test program using postgres that creates a string with a surrogate pair. I then insert that string into a varchar property in a table. I then execute a select statement to pull the string out. But when I evaluate the string the lead char of the pair is correct, but the following pair value is mangled. I run this exact same code using DB2 and it works just fine. Is this a postgres limitation, or is there a specific way surrogate pairs need to be handled? Sounds odd. Can you provide actual queries showing the problem (and server version). Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] Alternatives to a unique indexes with NULL
On Sat, Jan 17, 2015 at 02:03:34PM +0100, Andreas Kretschmer wrote: Peter Hicks peter.hi...@poggs.co.uk wrote: All, I have a Rails application on 9.3 in which I want to enforce a unique index on a set of fields, one of which includes a NULL-able column. According to http://www.postgresql.org/docs/9.3/static/indexes-unique.html, btree indexes can't handle uniqueness on NULL columns, so I'm looking for another way to achieve what I need. somethink like that? : test=# create table peter_hicks (id int); CREATE TABLE Time: 1,129 ms test=*# create unique index idx_1 on peter_hicks ((case when id is null then 'NULL' else '' end)) where id is null; CREATE INDEX Time: 14,803 ms Note: COALESCE is probably the better choice here. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
