Re: Creating 1 million users in PostgreSQL

Hans-Jürgen Schönig wrote a great post “Creating 1 million users in PostgreSQL”. I just love such things! Well done! Thanks.

But since there is no possibility to leave comments under original post (BTW why?), I’ll create my own post.

  1. When testing such tricks always create temp database. Because I accidentally used… no, no, not the production one.. but anyway. 🙂
    UPD:

    Since when temp database matters when creating cluster-wide role?
    © Ervin Weber

    So, yes. There is no need in test database. My bad! 🙂

  2. Hans-Jürgen used shell, psql and \gexec combination. However, we may do all these steps using psql only

To make a long story short, this is my variant of creating 1 million users in PostgreSQL:

Test=# CREATE DATABASE test_role_db;
CREATE DATABASE
Time: 7947,657 ms

Test=# \c test_role_db
You are now connected to database "test_role_db".

test_role_db=# \timing
Timing is on.

test_role_db=# DO $$
test_role_db$# BEGIN
test_role_db$#   FOR i IN 1..1000000 LOOP
test_role_db$#     EXECUTE 'CREATE ROLE xy' || i;
test_role_db$#   END LOOP;
test_role_db$# END; $$;
DO
Time: 81267,482 ms

On my test environment generating 1 million users in a single transaction takes around 1 minute 21 seconds. This is a little bit longer than Hans-Jürgen’s timing. Suppose this is due to the hardware difference.

Advertisements

Serial-Identity mix

Serial-identity
Serial-identity
As you probably know PostgreSQL 10 introduced IDENTITY columns. They are pretty much like serial columns from the first sight. To learn about differences one may refer to the perfect blog post by Peter Eisentraut.

So I was wondering what if I mix some serial with some identity. So I executed such simple query:

CREATE TABLE foo(
id SERIAL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
);

Of course, I got an error:

ERROR: both default and identity specified for column "id" of table "foo"
SQL state: 42601

“SQL state: 42601” stands for a syntax error. But the interesting thing is that SERIAL, as you know, converted to CREATE SEQUENCE and SET DEFAULT.

So now I know what exactly the reason of error. You cannot have both DEFAULT and IDENTITY for a column.

Creating titles from text (SQL)

Task: get title (or excerpt) from given text (body)
Solution:

SELECT
left(body, 29) ||
COALESCE( substring( substr( body, 30 ) from '(.+?)(\,| )' ), '' ) as title,

Details: Take the first 29 characters, concatenate them with substring started from the 30-th character till the first occurrence of comma or space. Or concatenate with empty string, if pattern doesn’t match (COALESCE).

MERGE in PostgreSQL

Found cool trick how today implement Orable MERGE in PostgreSQL:

Oracle statement:

 

  1. MERGE INTO acme_obj_value d
  2. USING ( SELECT object_id
  3.         FROM   acme_state_tmp
  4.       ) s
  5. ON (d.object_id = s.object_id)
  6.   WHEN matched THEN
  7.     UPDATE SET d.date_value = LEAST(l_dt, d.date_value)
  8.   WHEN NOT matched THEN
  9.     INSERT (d.id, d.object_id, d.date_value)
  10.     VALUES (acme_param_sequence.NEXTVAL, s.object_id, l_dt)

PostgreSQL statement:

 

  1. WITH s AS (
  2.      SELECT object_id
  3.      FROM   acme_state_tmp
  4. ),
  5. upd AS (
  6.      UPDATE acme_obj_value
  7.      SET    date_value = LEAST(l_dt, d.date_value)
  8.      FROM   s
  9.      WHERE  acme_obj_value.object_id = s.object_id
  10.      RETURNING acme_obj_value.object_id
  11. )
  12. INSERT INTO acme_obj_value(id, object_id,  date_value)
  13. SELECT NEXTVAL(‘acme_param_sequence’), s.object_id, l_dt
  14. FROM   s
  15. WHERE  s.object_id NOT IN (SELECT object_id FROM upd)

 

Delphi: Warnigs → Errors

Want to force your dev team to address Compiler Warnings rather than ignoring them?

Elevate Warnings to Errors:

1) Go to Project Options
2) Under Delphi Compiler, select Hints and Warnings
3) Expand the Output warnings item
4) For each important Warning you wish for your developers to address rather than ignore, select the drop-down list and select Error.

Enjoy!

Thanks Simon Stuart for hint!

Delphi+Elevate+Warnings+to+Errors

Get PostgreSQL sources using SVN

As you probably know PostgreSQL sources are managed by Git version control system. Which is great, but for me as SVN user it would be much better to get access to the sources using familiar tools, e.g. TortoiseSVN. Mainly because I don’t need write access, read mode only. There is a workaround for such guys.

First of all, there is a mirror of PostgreSQL sources on the GitHub. And the second, GitHub supports SVN protocol using the bridge to communicate svn commands to GitHub.

So in my case I need only trunk with the latest commits. I’ve created an empty folder and made Checkout to the https://github.com/postgres/postgres/trunk URL.Image

Number into words convertion in PostgreSQL

I was playing with some unit tests for PostgresDAC recently. And one test case was to check TPSQLDataset.Locate routine which allows partial-string and case-sensitive matching options. So I want to have some test result set with integer and text columns. The simplest for me are numbers in both representations: using digits and using words, e.g.

  1. one
  2. two
  3. three
  4. four
  5. etc.

Since I hadn’t much spare time I used nice VALUES command:

VALUES (1, 'one'), (2, 'two'), (3, 'three');

Considering I needed special names for columns I used more complex query:

SELECT col1, col2::varchar(10) FROM 
(VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t(col1, col2);

I did my task well. However I was wondering is there any built-in function in PostgreSQL which may convert integer to such words representation. The answer is: not exactly.

I found some references to cash_words function. But there is no any information (except entry in 7.3 release notes) in the official documents. The use case is quite simple:

SELECT cash_words(gen.i::money) FROM generate_series(1, 10) AS gen(i);
cash_words text

"One dollar and zero cents" "Two dollars and zero cents" "Three dollars and zero cents" "Four dollars and zero cents" "Five dollars and zero cents" "Six dollars and zero cents" "Seven dollars and zero cents" "Eight dollars and zero cents" "Nine dollars and zero cents" "Ten dollars and zero cents"

I’m fine with this result set, who needs only numbers may trim values using standard functions.

PS In case someone wants to create his own implementation using C (or plpgsql, who knows), cash_words function may be found in the \src\backend\utils\adt\cash.c file. It uses num_word private routine.