1

I have a stored procedure that does the following :

INSERT INTO schema.my_unique_values
     SELECT DISTINCT id, value
       FROM schema.a_huge_table
      WHERE NOT EXISTS (SELECT 1
                          FROM schema.my_unique_values)

In summary, this query will insert a unique rows into the my_unique_values table from the a_huge_table.

The problem I have is that when I run this query concurrently in a multi-threaded script for a reason, it can insert duplicate rows. I have tried using serializable to avoid this 'phantom reads', but still no luck. My idea is to allow Postgres to only insert non-duplicate rows on unique constraint violation. But is this possible ? My current experience is that when a unique constraint violation is hit, it will cancel the whole transaction, so it will not insert the non-duplicate rows. How can I achieve my goal ?

Note: I am using Greenplum 4.3.11 that is using Postgres 8.2, therefore there are limitation on the query that I can use.

Thanks..

  • I think your phrase "when I run this concurrently" is misleading. Only one query is being executed here. – beldaz Jan 23 '17 at 6:17
  • @beldaz, I edited my question. FYI, this query is being ran in a multi-threaded script for a reason, therefore that query may be ran concurrently. – kelvien Jan 23 '17 at 6:21
  • makes complete sense now.. – beldaz Jan 23 '17 at 6:26
  • Even if Greenplum is based on Postgres (and a really old version of that). It is sufficiently different to Postgres to not warrant the postgresql tag – a_horse_with_no_name Jan 23 '17 at 6:56
1

First of all, I think you need to change slightly your query, because the WHERE NOT EXISTS, as it is now written, would be false as soon as your table has some row. You need to specify a WHERE clause:

INSERT INTO schema.my_unique_values
     SELECT DISTINCT id, value
       FROM schema.a_huge_table a
      WHERE NOT EXISTS 
             (SELECT 1
                FROM schema.my_unique_values m
               WHERE m.id = a.id AND m.value = a.value)

INSERT, either within a transaction, or as a single-statement-transaction, will be atomic. So, if one row fails, everything will fail. In any case, you cannot have phantom reads in SERIALIZABLE transaction isolation levels. If you're under that isolation level, make sure your NOT EXISTS (...) really has the properly written WHERE clause.

As of PostgreSQL version 9.5, there is a new clause that can be part of an INSERT and that looks exactly for your use-case. You could modify your querty to make use of ON CONFLICT DO NOTHING:

INSERT INTO schema.my_unique_values
     SELECT DISTINCT id, value
       FROM schema.a_huge_table a
ON CONFLICT DO NOTHING ;

NOTE: The ON CONFLICT clause applies to each row, not to the INSERT as a whole.

  • Thanks @joanolo. I think I missed the WHERE clause while using the SERIALIZABLE isolation level, which was why I wasn't getting the result I wanted, I will try it again soon. In terms ON CONFLICT DO NOTHING, I am basically stuck with Postgres v8.2, this is the version that a Greenplum DB uses. This DB has a lot of limitation, I figure it's probably due to its parallelism. – kelvien Jan 23 '17 at 0:16
  • 1
    No idea if the execution plan would be any different, but an uncorrelated version of the query would be SELECT id, value FROM schema.a_huge_table a EXCEPT SELECT id, value FROM schema.my_unique_values – beldaz Jan 23 '17 at 6:38
0

Alternative idea, this is a cache table. I'm not a fan of those. What happens if the (id,value) is deleted from schema.a_huge_table?

CREATE MATERIALIZED VIEW schema.my_unique_values AS
  SELECT DISTINCT id, value
  FROM schema.a_huge_table;

Then when you want to refresh it..

REFRESH MATERIALIZED VIEW schema.my_unique_values

You can even refresh it CONCURRENTLY

REFRESH MATERIALIZED VIEW CONCURRENTLY schema.my_unique_values

Refresh the materialized view without locking out concurrent selects on the materialized view. Without this option a refresh which affects a lot of rows will tend to use fewer resources and complete more quickly, but could block other connections which are trying to read from the materialized view. This option may be faster in cases where a small number of rows are affected.

Not to say it's always the perfect solution. But, I generally find it to be a better solution for most things in the range of reasonable.

  • Thanks @Evan. I have to add that I think this might be able to be a neat solution. But, as I said in my previous comment, I am using a Greenplum DB which is from Postgres v8.2. And this DB has a lot of limitation, including, unfortunately the MATERIALIZED table. – kelvien Jan 23 '17 at 0:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.