Re: [HACKERS] Row-level Locks SERIALIZABLE transactions, postgres vs. Oracle

2010-05-13 Thread Anssi Kääriäinen
. -- Anssi Kääriäinen -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] SSI patch version 8

2011-01-11 Thread Anssi Kääriäinen
On 01/10/2011 06:03 PM, Kevin Grittner wrote: Due to popular request (Hey, David's popular, right?), I'm posting a patch for Serializable Snapshot Isolation (SSI), although I don't yet have everything in it that I was planning on submitting before the CF. I will probably be submitting another

Re: [HACKERS] SSI patch version 8

2011-01-11 Thread Anssi Kääriäinen
On 01/10/2011 06:03 PM, Kevin Grittner wrote: Due to popular request (Hey, David's popular, right?), I'm posting a patch for Serializable Snapshot Isolation (SSI), although I don't yet have everything in it that I was planning on submitting before the CF. I will probably be submitting another

Re: [HACKERS] SSI patch version 8

2011-01-11 Thread Anssi Kääriäinen
On 01/11/2011 04:53 PM, Kevin Grittner wrote: Thanks much for testing. You're managing to exercise some code paths I didn't think to test, which is great! I guess this is the up side of having posted yesterday. :-) Glad that I can help. This feature is something that is very important to

Re: [HACKERS] SSI patch version 8

2011-01-14 Thread Anssi Kääriäinen
On 01/14/2011 02:21 AM, Kevin Grittner wrote: I hope you have no objection to having the code you wrote included in the test suite which is part of the patch. Well, if you do, I'll pull it back out and invent something similar... ;-) No objection. - Anssi -- Sent via pgsql-hackers mailing

Re: [HACKERS] SSI patch version 12

2011-01-16 Thread Anssi Kääriäinen
While I haven't tried this patch, I tried to break the version 11 of the patch (some of the work was against earlier versions). In total I have used a full work day just trying to break things, but haven't been able to find anything after version 8. I can verify that the partial index issue is

[HACKERS] REVIEW: Extensions support for pg_dump

2011-01-17 Thread Anssi Kääriäinen
I used the patch from CommitFest application and applied the following commit to fix a known issue: http://git.postgresql.org/gitweb?p=postgresql-extension.git;a=commitdiff;h=d4991d35283ae0ceeb7f9e4203cf6a9dfb5d128d Is the patch in context diff format? Yes. Does the patch apply cleanly? No:

Re: [HACKERS] REVIEW: Extensions support for pg_dump

2011-01-18 Thread Anssi Kääriäinen
On 01/17/2011 07:58 PM, Kääriäinen Anssi wrote: The issue I saw was this: assume you have an extension foo, containing one function, test(). CREATE EXTENSION foo; DROP FUNCTION test(); -- restricted due to dependency ALTER FUNCTION test() RENAME TO test2; DROP FUNCTION test2(); -- not

Re: [HACKERS] REVIEW: Extensions support for pg_dump

2011-01-18 Thread Anssi Kääriäinen
On 01/17/2011 06:53 PM, Dimitri Fontaine wrote: Usability review: The patch implements a way to create extensions. While the patch is labeled extensions support for pg_dump, it actually implements more. It implements a new way to package and install extension, and changes contrib extensions to

Re: [HACKERS] REVIEW: Extensions support for pg_dump

2011-01-18 Thread Anssi Kääriäinen
On 01/18/2011 11:42 AM, Dimitri Fontaine wrote: I've fixed the case by having the code remember the function's extension if any, and restore it along with the other dependencies. The only question here is should CREATE OR REPLACE be allowed. I just realized this could present a new problem. If

Re: [HACKERS] REVIEW: Extensions support for pg_dump

2011-01-18 Thread Anssi Kääriäinen
On 01/18/2011 12:11 PM, Anssi Kääriäinen wrote: The only question here is should CREATE OR REPLACE be allowed. I just realized this could present a new problem. If I am not mistaken, when loading from dump, you suddenly get the extension's version back, not the one you defined in CREATE

Re: [HACKERS] REVIEW: Extensions support for pg_dump

2011-01-18 Thread Anssi Kääriäinen
On 01/18/2011 01:03 PM, Dimitri Fontaine wrote: I'd appreciate a list of yet-to-fix items. What I have is the search_path issue where CREATE EXTENSION foo; can leave it changed for the current session, I intend to fix that later today. Other than that, I have no further already agreed on code

Re: [HACKERS] SSI and Hot Standby

2011-01-21 Thread Anssi Kääriäinen
On 01/21/2011 03:25 AM, Florian Pflug wrote: The COMMIT order in the actual, concurrent, schedule doesn't not necessarily represent the order of the transaction in an equivalent serial schedule. Here's an example T1: BEGIN SERIALIZABLE; -- (Assume snapshot is set here) T1: UPDATE D1 ... ; T2:

Re: [HACKERS] SSI and Hot Standby

2011-01-21 Thread Anssi Kääriäinen
On 01/21/2011 02:21 PM, Florian Pflug wrote: Still, the would dump reflects a database state that *logically* never existed (i.e. not in any serial schedule). If you dump for disaster recovery, you might not care. If you dump to copy the data onto some reporting server you might. best

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

2011-02-03 Thread Anssi Kääriäinen
On 02/02/2011 08:22 PM, Dimitri Fontaine wrote: Either one line in the Makefile or a new file with the \i equivalent lines, that would maybe look like: SELECT pg_execute_sql_file('upgrade.v14.sql'); SELECT pg_execute_sql_file('upgrade.v15.sql'); So well… I don't see how you've made it

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

2011-02-03 Thread Anssi Kääriäinen
On 02/03/2011 12:23 AM, Robert Haas wrote: [..] -- unconditional stuff [..6] -- stuff to do if coming from pre-7 [..] -- some more unconditional stuff [6..12] -- stuff to do if coming from between 6 and 12 [..] -- a few more unconditional things This might be a stupid idea, but how about

Re: [HACKERS] temporal support patch

2012-08-21 Thread Anssi Kääriäinen
I have written one approach to audit tables, available from https://github.com/akaariai/pgsql_shadow_tables The approach is that every table is backed by a similar audit table + some meta information. The tables and triggers to update the audit tables are managed by plpgsql procedures.

[HACKERS] Feature proposal: list role members in psql

2012-03-28 Thread Anssi Kääriäinen
} | {akaariai} - Anssi Kääriäinen -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transient plans versus the SPI API

2011-08-08 Thread Anssi Kääriäinen
On 08/07/2011 12:25 PM, Hannu Krosing wrote: On Sun, 2011-08-07 at 11:15 +0200, Hannu Krosing wrote: On Wed, 2011-08-03 at 15:19 -0400, Tom Lane wrote: Hm, you mean reverse-engineering the parameterization of the query? Yes, basically re-generate the query after (or while) parsing, replacing

Re: [HACKERS] Transient plans versus the SPI API

2011-08-08 Thread Anssi Kääriäinen
On 08/08/2011 01:07 PM, Hannu Krosing wrote: That is why I think it is best done in the main parser - it has to parse and analyse the query anyway and likely knows which constants are arguments to the query As far as I understand the problem, the parsing must transform table references to

Re: [HACKERS] index-only scans

2011-08-16 Thread Anssi Kääriäinen
On 08/14/2011 12:31 AM, Heikki Linnakangas wrote: The same idea could of course be used to calculate the effective cache hit ratio for each table. Cache hit ratio would have the problem of feedback loops, though. Yeah, I'm not excited about making the planner and statistics more dynamic.

Re: [HACKERS] So, is COUNT(*) fast now?

2011-10-31 Thread Anssi Kääriäinen
On 10/31/2011 02:44 PM, Robert Haas wrote: What I think you're probably measuring here (oprofile would tell us for sure) is that once the size of the table goes beyond about half a gigabyte, it will have more than one page in the visibility map. The index-only scan code keeps the most recently

Re: [HACKERS] ALTER EXTENSION UPGRADE, v3

2011-02-11 Thread Anssi Kääriäinen
On 02/11/2011 05:05 AM, Tom Lane wrote: Actually, I was having second thoughts about that while at dinner. What is the value of separating the bootstrap-an-extension-from-old-objects operation into two steps? It's certainly not convenient for users, and I don't see that the intermediate state

Re: [HACKERS] pg_terminate_backend and pg_cancel_backend by not administrator user

2011-02-14 Thread Anssi Kääriäinen
On 02/14/2011 02:10 PM, Torello Querci wrote: I suppose that give the right to the owner db user to terminate or cancel other session connected to the database which it is owner is a good thing. I not see any security problem because this user can cancel or terminate only the session related

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-08 Thread Anssi Kääriäinen
On Tue, 2014-10-07 at 13:33 +0100, Simon Riggs wrote: Is there a way of detecting that we are updating a unique constraint column and then applying the HW locking only in that case? Or can we only apply locking when we have multiple unique constraints on a table? What is the use case of doing

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-08 Thread Anssi Kääriäinen
On Wed, 2014-10-08 at 02:22 -0700, Peter Geoghegan wrote: On Wed, Oct 8, 2014 at 1:25 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Instead of naming the index, you should name the columns, and the system can look up the index or indexes that match those columns. It's not totally

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-08 Thread Anssi Kääriäinen
On Wed, 2014-10-08 at 01:10 -0700, Peter Geoghegan wrote: On Wed, Oct 8, 2014 at 12:41 AM, Anssi Kääriäinen anssi.kaariai...@thl.fi wrote: The MySQL documentation says that you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes[1

Re: [HACKERS] tracking commit timestamps

2014-11-05 Thread Anssi Kääriäinen
On Tue, 2014-11-04 at 23:43 -0600, Jim Nasby wrote: I'm worried about 2 commits in the same microsecond on the same system, not on 2 different systems. Or, put another way, if we're going to expose this I think it should also provide a guaranteed unique commit ordering for a single cluster.

Re: [HACKERS] tracking commit timestamps

2014-11-09 Thread Anssi Kääriäinen
On Sun, 2014-11-09 at 11:57 -0500, Steve Singer wrote: The reason why Jim and myself are asking for the LSN and not just the timestamp is that I want to be able to order the transactions. Jim pointed out earlier in the thread that just ordering on timestamp allows for multiple transactions

Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE}

2014-11-19 Thread Anssi Kääriäinen
On Wed, 2014-11-19 at 16:52 -0800, Peter Geoghegan wrote: Someone mentioned to me privately that they weren't sure that the question of whether or not RETURNING only projected actually inserted tuples was the right one. Also, I think someone else mentioned this a few months back. I'd like to

Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE}

2014-11-20 Thread Anssi Kääriäinen
On Thu, 2014-11-20 at 13:42 -0800, Peter Geoghegan wrote: I am a developer of the Django ORM. Django reports to the user whether a row was inserted or updated. It is possible to know which rows were inserted by returning the primary key value. If something is returned, then it was an

Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE}

2014-12-04 Thread Anssi Kääriäinen
=excluded.other_col where target is distinct from excluded; - Anssi Kääriäinen -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE}

2014-12-04 Thread Anssi Kääriäinen
On Thu, 2014-12-04 at 10:27 -0800, Peter Geoghegan wrote: I think that the standard for adding a new system attribute ought to be enormous. The only case where a new one was added post-Postgres95 was tableoid. I'm pretty sure that others aren't going to want to do it that way. Besides, I'm

Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE}

2014-12-05 Thread Anssi Kääriäinen
On Fri, 2014-12-05 at 00:21 -0800, Peter Geoghegan wrote: On Thu, Dec 4, 2014 at 10:27 PM, Anssi Kääriäinen anssi.kaariai...@thl.fi wrote: For Django's use case this is a requirement. We must inform the user if the save() action created a new row or if it modified an existing one. Can you

Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE}

2014-12-08 Thread Anssi Kääriäinen
On Fri, 2014-12-05 at 10:00 -0800, Josh Berkus wrote: I thought the point of INSERT ... ON CONFLICT update was so that you didn't have to care if it was a new row or not? If you do care, it seems like it makes more sense to do your own INSERTs and UPDATEs, as Django currently does. Django