Re: [GENERAL] How to isolate the result of SELECT's?
In all likelihood you do not want to do what you described (lock the tables.) You have to have a basic understanding of MVCC and transaction isolation levels to determine if the default behavior of mvcc + 'read committed' is sufficient. In a lot of cases it is. Kiriakos On Mar 18, 2012, at 7:33 PM, Andre Lopes wrote: > Hi, > > I need to do an operation that I will use some SELECT's and get the > results, but I want to have sure that those tables have not been > changed with INSERT's or UPDATES during the operation. > > Example: > > BEGIN OPERATION > Select field from table1; > ... > Select other_field from table2; > ... > END OPERATION > > How can I lock these tables to assure that the tables are not getting > INSERTS's or UPDATE's during the operation? > > Best Regards, > > -- > 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] Anonymized database dumps
I would store sensitive data encrypted in the database. Check the pgcrypto module. Kiriakos On Mar 18, 2012, at 1:00 PM, Janning Vygen wrote: > Hi, > > I am working on postgresql 9.1 and loving it! > > Sometimes we need a full database dump to test some performance issues with > real data. > > Of course we don't like to have sensible data like bunches of e-mail > addresses on our development machines as they are of no interest for > developers and should be kept secure. > > So we need an anonymized database dump. I thought about a few ways to achieve > this. > > 1. Best solution would be a special db user and some rules which fire on > reading some tables and replace privacy data with some random data. Now doing > a dump as this special user doesn't even copy the sensible data at all. The > user just has a different view on this database even when he calls pg_dump. > > But as rules are not fired on COPY it can't work, right? > > 2. The other solution I can think of is something like > > pg_dump | sed > pgdump_anon > > where 'sed' does a lot of magical replace operations on the content of the > dump. I don't think this is going to work reliable. > > 3. More reliable would be to dump the database, restore it on a different > server, run some sql script which randomize some data, and dump it again. > hmm, seems to be the only reliable way so far. But it is no fun when dumping > and restoring takes an hour. > > Does anybody has a better idea how to achieve an anonymized database dump? > > regards > Janning > > > > > > -- > Kicktipp GmbH > > Venloer Straße 8, 40477 Düsseldorf > Sitz der Gesellschaft: Düsseldorf > Geschäftsführung: Janning Vygen > Handelsregister Düsseldorf: HRB 55639 > > http://www.kicktipp.de/ > > -- > 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] nice'ing the postgres COPY backend process to make pg_dumps run more "softly"
Hi. When pg_dump runs, our application becomes inoperative (too slow). I was going to ask if nice'ing the postgres backend process that handles the COPY would help but I just realized probably the pg_dump takes out locks when it runs and nice'ing it would just make it run longer... However the man page says "pg_dump does not block other users accessing the database (readers or writers)." But if we run a pg_dump, the phone starts ringing, users are complaining that the web app is not working. Would appreciate some pointer to help me reconcile these two apparently contradictory facts. Best, -at -- 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] How to isolate the result of SELECT's?
Hi, > > I need to do an operation that I will use some SELECT's and get the > results, but I want to have sure that those tables have not been > changed with INSERT's or UPDATES during the operation. > > Example: > > BEGIN OPERATION > Select field from table1; > ... > Select other_field from table2; > ... > END OPERATION > > How can I lock these tables to assure that the tables are not getting > INSERTS's or UPDATE's during the operation? > If you set transaction isolation level to repeatable, the tables may undergo changes, but you will get repeatable reads. Explicitly locking the tables is another option. http://www.postgresql.org/docs/current/static/explicit-locking.html Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."
Re: [GENERAL] Zero-length character breaking query?
Thanks Tom; this is at a client site, so I have limited access, but it looks like a REINDEX resolves the issue. Doug -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: March 16, 2012 6:33 AM To: Doug Gorley Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Zero-length character breaking query? Doug Gorley writes: > The table is called tdt_unsent. The field is str_name_l. For demonstration > purposes, the value is "SMITH". > "select * from tdt_unsent where str_name_l = 'SMITH'" returns 0 rows. > "select * from tdt_unsent where str_name_l ~ '^SMITH'" returns 3 rows. > "select * from tdt_unsent where str_name_l ~ '^SMITH$'" returns 0 rows. > "select length(str_name_l) from tdt_unsent where str_name_l ~ '^SMITH'" > returns "5". I'd check EXPLAIN (with the actual problematic string, not SMITH). The planner is probably trying to build an index range condition from the regex pattern --- is it doing the right thing given your locale? If the plan looks okay, maybe you need to reindex whatever index it's using. 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] How to isolate the result of SELECT's?
On 03/18/2012 04:33 PM, Andre Lopes wrote: Hi, I need to do an operation that I will use some SELECT's and get the results, but I want to have sure that those tables have not been changed with INSERT's or UPDATES during the operation. Example: BEGIN OPERATION Select field from table1; ... Select other_field from table2; ... END OPERATION How can I lock these tables to assure that the tables are not getting INSERTS's or UPDATE's during the operation? Rather than replicate the docs in a post, I suggest you take a look at: http://www.postgresql.org/docs/9.1/interactive/mvcc.html It explains the various options you have very well. Best Regards, -- 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] How to isolate the result of SELECT's?
On Sun, Mar 18, 2012 at 6:33 PM, Andre Lopes wrote: > Hi, > > I need to do an operation that I will use some SELECT's and get the > results, but I want to have sure that those tables have not been > changed with INSERT's or UPDATES during the operation. > > Example: > > BEGIN OPERATION > Select field from table1; > ... > Select other_field from table2; > ... > END OPERATION > > How can I lock these tables to assure that the tables are not getting > INSERTS's or UPDATE's during the operation? > > Best Regards,\ > Isn't that what 'begin transaction' and 'commit' are for? -- Mike Nolan
[GENERAL] How to isolate the result of SELECT's?
Hi, I need to do an operation that I will use some SELECT's and get the results, but I want to have sure that those tables have not been changed with INSERT's or UPDATES during the operation. Example: BEGIN OPERATION Select field from table1; ... Select other_field from table2; ... END OPERATION How can I lock these tables to assure that the tables are not getting INSERTS's or UPDATE's during the operation? Best Regards, -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Multi server query
Hy all of you, How do you query _multi servers_ and multi databases on postgresql? Regards
Re: [GENERAL] Ubuntu 11.10 Postgres 9.1.3 is missing pg_resetxlog; where can I get it?
On 03/18/2012 11:41 AM, jgenoese wrote: Greetings. I installed postgresql-9.1 via apt-get on ubuntu 11.10. Was there another Postgres instance already installed on this machine? At first, it wouldn't start because of a missing pg_xlog directory. So I manually created it. Did you initdb a new data directory? Then, it wouldn't start, because of the following: 2012-03-18 13:18:20 EDT LOG: database system was interrupted; last known up at 2012-03-18 12:17:07 EDT 2012-03-18 13:18:20 EDT LOG: creating missing WAL directory "pg_xlog/archive_status" 2012-03-18 13:18:20 EDT LOG: could not open file "pg_xlog/00010001" (log file 0, segment 1): No such file or directory 2012-03-18 13:18:20 EDT LOG: invalid primary checkpoint record 2012-03-18 13:18:20 EDT LOG: could not open file "pg_xlog/00010001" (log file 0, segment 1): No such file or directory 2012-03-18 13:18:20 EDT LOG: invalid secondary checkpoint record 2012-03-18 13:18:20 EDT PANIC: could not locate a valid checkpoint record 2012-03-18 13:18:20 EDT LOG: startup process (PID 3343) was terminated by signal 6: Aborted 2012-03-18 13:18:20 EDT LOG: aborting startup due to startup process failure After searching, I found a recommendation to execute 'pf_resetxlog', but that utility is nowhere to be found, even by using 'find' on the entire HDD. Now I am at a standstill. Where can I find ''pg_resetxlog"? Should be in the $PG_BIN/ with the other commands initdb, psql, creatdb, etc. Before you do that I would spend some time determining exactly what your situation is. The missing pg_xlog error earlier would seem to indicate potentially bigger issues. Many thanks for any help. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Ubuntu-11-10-Postgres-9-1-3-is-missing-pg-resetxlog-where-can-I-get-it-tp5575465p5575465.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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
[GENERAL] Re: Ubuntu 11.10 Postgres 9.1.3 is missing pg_resetxlog; where can I get it?
Problem solved...user error. I can't say what was wrong, bur after the third re-install everything worked. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Ubuntu-11-10-Postgres-9-1-3-is-missing-pg-resetxlog-where-can-I-get-it-tp5575465p5575486.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] How to perform full text search
On 03/18/12 11:45 AM, Andrus wrote: select +case when productname ilike '%red%' then 2 else 0 end +case when productdescription ilike '%red%' then 1 else 0 end +case when productname ilike '%cat%' then 1.7 else 0 end +case when productdescription ilike '%cat%' then 0.7 else 0 end from products order by 1 desc limit 100 This allows to define relevance. Is my solution reasonable ? if you don't mind a full table sequential scan each time you execute that, I suppose. -- 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] How to perform full text search
Parse the entry string into words (aka tokens) and assemble with the and operator. E.g. 'red cat' becomes 'red & cat'. >Then add vector; more info in articles I provide links to later in this note. WHERE to_tsvector ( productname || ' ' || productdescription ) @@ to_tsquery ( 'red & cat' ) Since there were no responces for a while, I went with another solution. Splitted search string to words like you but converted query to select +case when productname ilike '%red%' then 2 else 0 end +case when productdescription ilike '%red%' then 1 else 0 end +case when productname ilike '%cat%' then 1.7 else 0 end +case when productdescription ilike '%cat%' then 0.7 else 0 end from products order by 1 desc limit 100 This allows to define relevance. Is my solution reasonable ? Andrus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Ubuntu 11.10 Postgres 9.1.3 is missing pg_resetxlog; where can I get it?
Greetings. I installed postgresql-9.1 via apt-get on ubuntu 11.10. At first, it wouldn't start because of a missing pg_xlog directory. So I manually created it. Then, it wouldn't start, because of the following: 2012-03-18 13:18:20 EDT LOG: database system was interrupted; last known up at 2012-03-18 12:17:07 EDT 2012-03-18 13:18:20 EDT LOG: creating missing WAL directory "pg_xlog/archive_status" 2012-03-18 13:18:20 EDT LOG: could not open file "pg_xlog/00010001" (log file 0, segment 1): No such file or directory 2012-03-18 13:18:20 EDT LOG: invalid primary checkpoint record 2012-03-18 13:18:20 EDT LOG: could not open file "pg_xlog/00010001" (log file 0, segment 1): No such file or directory 2012-03-18 13:18:20 EDT LOG: invalid secondary checkpoint record 2012-03-18 13:18:20 EDT PANIC: could not locate a valid checkpoint record 2012-03-18 13:18:20 EDT LOG: startup process (PID 3343) was terminated by signal 6: Aborted 2012-03-18 13:18:20 EDT LOG: aborting startup due to startup process failure After searching, I found a recommendation to execute 'pf_resetxlog', but that utility is nowhere to be found, even by using 'find' on the entire HDD. Now I am at a standstill. Where can I find ''pg_resetxlog"? Many thanks for any help. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Ubuntu-11-10-Postgres-9-1-3-is-missing-pg-resetxlog-where-can-I-get-it-tp5575465p5575465.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Anonymized database dumps
Hi, I am working on postgresql 9.1 and loving it! Sometimes we need a full database dump to test some performance issues with real data. Of course we don't like to have sensible data like bunches of e-mail addresses on our development machines as they are of no interest for developers and should be kept secure. So we need an anonymized database dump. I thought about a few ways to achieve this. 1. Best solution would be a special db user and some rules which fire on reading some tables and replace privacy data with some random data. Now doing a dump as this special user doesn't even copy the sensible data at all. The user just has a different view on this database even when he calls pg_dump. But as rules are not fired on COPY it can't work, right? 2. The other solution I can think of is something like pg_dump | sed > pgdump_anon where 'sed' does a lot of magical replace operations on the content of the dump. I don't think this is going to work reliable. 3. More reliable would be to dump the database, restore it on a different server, run some sql script which randomize some data, and dump it again. hmm, seems to be the only reliable way so far. But it is no fun when dumping and restoring takes an hour. Does anybody has a better idea how to achieve an anonymized database dump? regards Janning -- Kicktipp GmbH Venloer Straße 8, 40477 Düsseldorf Sitz der Gesellschaft: Düsseldorf Geschäftsführung: Janning Vygen Handelsregister Düsseldorf: HRB 55639 http://www.kicktipp.de/ -- 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] One more query
On 03/17/2012 07:56 PM, prem tolani wrote: I get below error message> > PG "FATAL:� could not reattach to shared memory (key=5432001, addr=0210): Invalid argument. This messages occurs in windows 7 eventviewer log. Application log / postgresql log do not show anything. It is custom application made in java. If you need more info do let me know. So this is referring to the same problem as your other post. As Tom replied in the other thread this was fixed in 8.4.1 and back ported to 8.2 and 8.3. All recent versions from 8.2+ have the fix. FYI only 8.3+ are currently community supported. Regards, Prem -- 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] why no create variable
On Sun, Mar 18, 2012 at 11:26 PM, wrote: > > Getting the information from a table works. I now have a sessionsettings > table, but I'm making way to many calls to it because there is no global pool > of variable. Not IMO an efficient use of a table. Chances are that table, if you are indeed using it a lot, will be cached. It'll end up fairly cheap. But just out of vague curiosity, I wonder how viable it would be to create functions for all your global variables - each one is thus a function returning its value. ChrisA -- 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] why no create variable
<<< text/html; charset="utf-8": Unrecognized >>> <>
Re: [GENERAL] A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?
On Sat, 17 Mar 2012 10:46:00 -0500, dennis jenkins wrote: Aleksey, a suggestion: The vast majority of the postgresql wire protocol compresses well. If your WAN link is not already compressed, construct a compressed SSH tunnel for the postgresql TCP port in the WAN link. I've done this when rebuilding a 300GB database (via slony) over a bandwidth-limited (2MB/s) VPN link and it cut the replication resync time down significantly. SSH with the HPN patchset[1] would help as well if it's higher latency or if you're CPU limited as it can use multiple threads then. It works wonderfully for me on a 35mbit link. If you have a lower sized link that wouldn't benefit from the HPN patchset anyway it may be worth forcing Blowfish instead of AES to keep the CPU load lower. Hope that helps! [1] http://www.psc.edu/networking/projects/hpn-ssh/ FYI, the HPN patchset is included the base OpenSSH of FreeBSD 9 now. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
