[GENERAL] Different transaction log for database/schema
Hello, I would like kindly ask for an advice whether and how the transaction log behavior could be configured. I would like to have possibility to differentiate transaction logs for two databases or schema. I would need such configuration for my testing environment where I need to run simultaneously different tests using transactions. And I need to separate those tests for being sure that they do not interfere with each other. I'm using XA transactions in Java. Is there some way how to reach such behavior? Or is it possible to define where to store prepared transaction for a current session? Thank you for any hint or explanation how the things work in PostgreSQL Regards Ondrej Chaloupka
Re: [GENERAL] Different transaction log for database/schema
>where I need to run simultaneously different tests using transactions Are you running tests simultaneously on different databases? i.e. access to different databases within one transaction. If not -- after each transaction you can do pg_switch_xlog() then new transaction log will be created and next transaction details will store in new log file. make sure that you set proper configuration parameter to keep maximum transactions logs. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Different-transaction-log-for-database-schema-tp5764604p5764622.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] Insert result does not match record count
No triggers, no rules. It's just a very boring, vanilla table. I have had plenty of cases where the inserts fail because many of the data types are different in the new table, and there is some junk that fails the cast. And even though the insert result seems to indicate that it only inserted some of the rows, when I do a count of the records after the insert, I find the same number of records in the new as in the old table. So it appears to be working correctly, but it's unsettling to see the insert result not match. Do I trust the count, or the insert result? Could this be a sign of some kind of corruption? Not sure how worried I should be. Cheers, Natalie On Jul 19, 2013, at 6:14 PM, Tom Lane wrote: > Natalie Wenz writes: >> I am moving some data from one table to another in 9.2.4, and keep seeing >> this strange scenario: >> ... >> So, my counts from the old and new tables match, but the result returned >> from the insert statement is sometimes a completely different number. (But >> not always.) I've checked my date ranges very carefully to make sure they >> match. > > Perhaps you've got triggers or rules on the target table that are > suppressing some inserts? > > 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] Insert result does not match record count
On Fri, Jul 19, 2013 at 3:20 PM, Natalie Wenz wrote: > Hi all, > > I am moving some data from one table to another in 9.2.4, and keep seeing > this strange scenario: > > insert into newtable select data from oldtable where proc_date >= x and > proc_date < y; > > INSERT 0 78551642 > > select count(*) from newtable where proc_date >= x and proc_date < y; >count > --- > 4373518938 It looks to me like the status report is 32 bits and overflowed. 4,373,518,938 - 2^32 = 78,551,642 Cheers, Jeff -- 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] Insert result does not match record count
So, in an attempt to see if it was a fluke, I picked one of the date ranges I was getting a different count for, and deleted the records, and then ran the insert again. Interestingly, the delete result matched the insert result (which was the same the second time as it was the first). I'll run a count on the records again, but it will take several hours. I'll post that information when I have it! On Jul 22, 2013, at 10:44 AM, Natalie Wenz wrote: > No triggers, no rules. It's just a very boring, vanilla table. I have had > plenty of cases where the inserts fail because many of the data types are > different in the new table, and there is some junk that fails the cast. And > even though the insert result seems to indicate that it only inserted some of > the rows, when I do a count of the records after the insert, I find the same > number of records in the new as in the old table. So it appears to be working > correctly, but it's unsettling to see the insert result not match. Do I trust > the count, or the insert result? Could this be a sign of some kind of > corruption? Not sure how worried I should be. > > Cheers, > Natalie > > > On Jul 19, 2013, at 6:14 PM, Tom Lane wrote: > >> Natalie Wenz writes: >>> I am moving some data from one table to another in 9.2.4, and keep seeing >>> this strange scenario: >>> ... >>> So, my counts from the old and new tables match, but the result returned >>> from the insert statement is sometimes a completely different number. (But >>> not always.) I've checked my date ranges very carefully to make sure they >>> match. >> >> Perhaps you've got triggers or rules on the target table that are >> suppressing some inserts? >> >> 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] Different transaction log for database/schema
Hello 2013/7/22 Ondrej Chaloupka : > Hi Sachin, > > thank you for the idea. I didn't know about the function. > Just this probably won't work for my use case. I do not access to different > databases in one transaction. I access different databases simultaneously > each with it's own xa transaction. What I understand the switch_xlog servers > for using new file for transaction log. What I would understand is supposed > to be used e.g. for cleaning. > > In my case it's normal that the two tests uses database (their own databases > but under one PostgreSQL server) and each one creates an record in the log. > E.g. when first test would call the switch_xlog and continue with work then > the second test would start and call the switch_xlog then the both tests > will still use the same transaction log despite the fact that it would be > some new one. In case that I understand the function right. > > I would need to force of using different files based on > user/schema/database. Is there such possibility? No, it is not possible in one PostgreSQL database cluster. You can create more than one PostgreSQL clusters on one computer. Regards Pavel > > Thank you > Ondra > > > -- Původní zpráva -- > Od: sachin kotwal > Datum: 22. 7. 2013 > Předmět: Re: [GENERAL] Different transaction log for database/schema > > >>where I need to run simultaneously different tests using transactions > > Are you running tests simultaneously on different databases? > i.e. access to different databases within one transaction. > > If not -- > > after each transaction you can do pg_switch_xlog() then new transaction log > will be created and next transaction details will store in new log file. > > make sure that you set proper configuration parameter to keep maximum > transactions logs. > > > > > > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Different-transaction-log-for-database-schema-tp5764604p5764622.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 -- 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] Index for Levenshtein distance (better format)
Hi Janek, Il 21/07/2013 16:46, Janek Sendrowski ha scritto: Hi, Im searching for a suitable Index for my query witch compares Strings with the Levenshtein distance. I read that a prefix index would fit, but I dont know how to build it. I only know that its supported by Gist. I couldn't find an instructions so I hope someone can help me. Consider this simple example: suppose you have a table "table1" with a column "string" of type TEXT, and you are interested to find "string" in your table equal to 'ciao' basing your query in Levenshtein distance. Then, improve your query creating an index based on gist. First of all, create extensions to use levenshtein() function, and gist indexing for types different from PostGIS objects: CREATE EXTENSION fuzzystrmatch; CREATE EXTENSION btree_gist; You can check that all works fine trying a query like: SELECT string FROM table1 WHERE levenshtein(string,'ciao') = 0; which finds also "string" equal to 'ciao'. Create now the index to improve this query: CREATE INDEX lev_idx ON table1 USING GIST(levenshtein(string,'ciao')); And relaunch the same query. I made a simple check with a very simple table1 example with ten raws using EXPLAIN ANALYSE to exploit the performances: query time changes from 1.077 ms to 0.677 ms after gist index creation. Hope it helps, Giuseppe. -- Giuseppe Broccolo - 2ndQuadrant Italy PostgreSQL Training, Services and Support giuseppe.brocc...@2ndquadrant.it |www.2ndQuadrant.it -- 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] odd locking behaviour
On Sun, Jul 21, 2013 at 9:15 PM, Pavel Stehule wrote: > hello > > It can be artefact of RI implementation. > > see > http://michael.otacoo.com/postgresql-2/postgres-9-3-feature-highlight-for-key-share-and-for-no-key-update/ > > Try to 9.3, please, where RI uses more gently locks It still behaves this way in 9.4dev. >> >> On Mon, Jul 8, 2013 at 9:54 AM, pg noob wrote: >>> >>> >>> Thank you for the responses. Is it a bug? I don't think so. While PostgreSQL of course strives for maximum concurrency, it makes no guarantee that it uses the weakest theoretically possible locking in all possible cases. But it is kind of unfortunate that updating the same row twice causes a lock escalation when it is not obvious it should do so, because as you found that makes avoiding deadlocks quite difficult. I'm rather surprised it doesn't block at the first update of the 2nd session, rather than waiting for the 2nd update of that session. Anyway, when the 2nd session re-updates the same row in the same transaction, it uses a 'multixact' to record this. Doing that apparently defeats some locking optimization that takes place under simpler cases. Sorry, that probably isn't the definitive answer you were hoping for. Cheers, Jeff -- 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] Different transaction log for database/schema
Hi Sachin, thank you for the idea. I didn't know about the function. Just this probably won't work for my use case. I do not access to different databases in one transaction. I access different databases simultaneously each with it's own xa transaction. What I understand the switch_xlog servers for using new file for transaction log. What I would understand is supposed to be used e.g. for cleaning. In my case it's normal that the two tests uses database (their own databases but under one PostgreSQL server) and each one creates an record in the log. E.g. when first test would call the switch_xlog and continue with work then the second test would start and call the switch_xlog then the both tests will still use the same transaction log despite the fact that it would be some new one. In case that I understand the function right. I would need to force of using different files based on user/schema/ database. Is there such possibility? Thank you Ondra -- Původní zpráva -- Od: sachin kotwal Datum: 22. 7. 2013 Předmět: Re: [GENERAL] Different transaction log for database/schema ">where I need to run simultaneously different tests using transactions Are you running tests simultaneously on different databases? i.e. access to different databases within one transaction. If not -- after each transaction you can do pg_switch_xlog() then new transaction log will be created and next transaction details will store in new log file. make sure that you set proper configuration parameter to keep maximum transactions logs. -- View this message in context: http://postgresql.1045698.n5.nabble.com/ Different-transaction-log-for-database-schema-tp5764604p5764622.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] Different transaction log for database/schema
On 7/22/2013 9:33 AM, Ondrej Chaloupka wrote: thank you for the idea. I didn't know about the function. Just this probably won't work for my use case. I do not access to different databases in one transaction. I access different databases simultaneously each with it's own xa transaction. What I understand the switch_xlog servers for using new file for transaction log. What I would understand is supposed to be used e.g. for cleaning. postgres servers don't really have a 'transaction log' in the same sense as Oracle etc. they have a 'write-ahead log' which works quite differently. in the context of postgres's WAL logs, your question doesn't make any sense at all. -- 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] Different transaction log for database/schema
> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of John R Pierce > Sent: Monday, July 22, 2013 2:32 PM > To: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Different transaction log for database/schema > > On 7/22/2013 9:33 AM, Ondrej Chaloupka wrote: > > thank you for the idea. I didn't know about the function. > > Just this probably won't work for my use case. I do not access to > > different databases in one transaction. I access different databases > > simultaneously each with it's own xa transaction. What I understand > > the switch_xlog servers for using new file for transaction log. What I > > would understand is supposed to be used e.g. for cleaning. > > postgres servers don't really have a 'transaction log' in the same sense > as Oracle etc. they have a 'write-ahead log' which works quite > differently. > > in the context of postgres's WAL logs, your question doesn't make any sense > at all. > > > > -- > john r pierce 37N 122W > somewhere on the middle of the left coast > Oracle doesn't have transaction log either, MVCC implemented through Redo Logs and Undo tablespaces, and those are per database, not per schema. On the other hand, MS SQL Server has Transaction Logs, and they are per database. Still, I don't quite understand, what goal original poster is trying to achieve. 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] Insert result does not match record count
I had just started to wonder if it was something that when you responded. I checked, and every case that I had where the numbers were wonky checked out when I subtracted 2^32. Whew! Thanks, Jeff! On Jul 22, 2013, at 11:20 AM, Jeff Janes wrote: > On Fri, Jul 19, 2013 at 3:20 PM, Natalie Wenz wrote: >> Hi all, >> >> I am moving some data from one table to another in 9.2.4, and keep seeing >> this strange scenario: >> >> insert into newtable select data from oldtable where proc_date >= x and >> proc_date < y; >> >> INSERT 0 78551642 >> >> select count(*) from newtable where proc_date >= x and proc_date < y; >> count >> --- >> 4373518938 > > It looks to me like the status report is 32 bits and overflowed. > > 4,373,518,938 - 2^32 = 78,551,642 > > Cheers, > > Jeff > > > -- > 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] maintenance_work_mem and CREATE INDEX time
Hello, While understanding the effect of maintenance_work_mem on time taken by CREATE INDEX, I observed that for the values of maintenance_work_mem less than the value for which an internal sort is performed, the time taken by CREATE INDEX increases as maintenance_work_increases. My guess is that for all those values an external sort is chosen at some point and larger the value of maintenance_work_mem, later the switch to external sort would be made causing CREATE INDEX to take longer. That is a smaller value of maintenance_work_mem would be preferred for when external sort is performed anyway. Does that make sense? -- Amit Langote -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
