[GENERAL] Best way to insert a row with two columns having the value based on the same argument generated by another postgres function.
Hi,
I am trying to build a user database. The steps for creating a new user are:
1. Use gen_salt to create a salt.
2. Compute the hash based on the salt and password and store both the hash and
the salt into a new row.
The pl/pgsql would look like this:
CREATE OR REPLACE FUNCTION system.create_enduser(IN emailArg TEXT, IN
passwordArg TEXT, IN nicknameArg TEXT, IN roundsArg int) RETURNS TEXT AS
$$
#print_strict_params on
DECLARE
salt TEXT;
inserted_uuid TEXT;
BEGIN
salt := public.gen_salt('bf', roundsArg);
INSERT INTO system.enduser (email, password_hash, password_salt, nickname,
user_state) VALUES (emailArg, public.crypt(passwordArg, salt), salt,
nicknameArg, 'REGISTERED') RETURNING uuid into inserted_uuid;
RETURN inserted_uuid;
END
$$
LANGUAGE 'plpgsql' VOLATILE
;
Is there a way to do this in a single SQL statement without using a function?
One way I can think of is using trigger, but that still requires another setup.
Appreciate any help.
--
Regards
Ryan
[GENERAL] How to best archetect Multi-Tenant SaaS application using Postgres
Hi, We are trying build a multi tenant application and are debating which approach we should take: (also my understanding is based on that pgbouncer connection pool doesn’t work across different user/database pair): 1. For each tenant, we create a dedicated database and a dedicated user. This option gives best isolation. However, connection pooling won’t work pgbouncer. 2. We put all tenants in a single database, and just use one user. I learned that pgbackup will be probamatic when there are > 30 schemas in a database, so we probably can’t create dedicate schema for each tenant. We are more inclined to choose 1, but don't know how many concurrent connections Postgres can handle for OLTP workload in a 32GB memory, SSD box. Also we would like hear from someone with more postgres experience about the best practice for building multi-tenant application. Thanks Ryan
Re: [GENERAL] How to best archetect Multi-Tenant SaaS application using Postgres
Thanks for the response We are trying build a multi tenant application and are debating which approach we should take: (also my understanding is based on that pgbouncer connection pool doesn’t work across different user/database pair): 1. For each tenant, we create a dedicated database and a dedicated user. This option gives best isolation. However, connection pooling won’t work pgbouncer. Not sure what you meant by saying connection pooling (pgBouncer) does not work ? This is the general approach for building a multi-tenant application. According to https://pgbouncer.github.io/config.html, the connection pooling is based on user/database pair. If each user connects under its own username to its own database, then the connection opened can’t be shared with other users. If I want to make a single server host 1000 tenants at the same time, then there will be at least 1000 connections even I use pgBouncer. How many concurrent connections can be made, will purely depend on number of CPUs (cores) you have available on the database server. Well, certainly 1 would be the way to go to build an multi-tenant application, but, it strongly depends on your application specific requirements and how are you distributing the data across databases and how the users are accessing data across the databases. Yup, I also would like to go with 1, suppose a server with 16 core, 32GB memory, SSD box, how many connections could it handle roughly, e.g. O(100) or O(1000) or O(5000)? -- Regards Ryan On August 1, 2016 at 10:30:48 PM, Venkata Balaji N (nag1...@gmail.com) wrote: On Sun, Jul 31, 2016 at 12:07 PM, Silk Parrot wrote: Hi, We are trying build a multi tenant application and are debating which approach we should take: (also my understanding is based on that pgbouncer connection pool doesn’t work across different user/database pair): 1. For each tenant, we create a dedicated database and a dedicated user. This option gives best isolation. However, connection pooling won’t work pgbouncer. Not sure what you meant by saying connection pooling (pgBouncer) does not work ? This is the general approach for building a multi-tenant application. 2. We put all tenants in a single database, and just use one user. I learned that pgbackup will be probamatic when there are > 30 schemas in a database, so we probably can’t create dedicate schema for each tenant. We are more inclined to choose 1, but don't know how many concurrent connections Postgres can handle for OLTP workload in a 32GB memory, SSD box. Also we would like hear from someone with more postgres experience about the best practice for building multi-tenant application. Again, i am not sure what you meant by saying pgbackup will not work where there are more than 30 schemas ? Which version of PostgreSQL are you using ? How many concurrent connections can be made, will purely depend on number of CPUs (cores) you have available on the database server. Well, certainly 1 would be the way to go to build an multi-tenant application, but, it strongly depends on your application specific requirements and how are you distributing the data across databases and how the users are accessing data across the databases. Regards, Venkata B N Fujitsu Australia
[GENERAL] Unique constraint on field inside composite type.
Hi, I am trying to model a social login application. The application can support multiple login providers. I am thinking of creating a custom type for each provider. e.g. CREATE TYPE system.google_user AS ( email TEXT ); CREATE TYPE system.facebook_user AS ( id TEXT ); And having user table like: CREATE TABLE user ( uuid UUID PRIMARY KEY DEFAULT public.uuid_generate_v4(), google_user system.google_user, facebook_user system.facebook_user, UNIQUE (google_user.email) ); However, the above create table query reports syntax error: ERROR: syntax error at or near "." LINE 10: UNIQUE (google_user.email) Is there a way to create unique constraint on a field inside composite type? -- Regards Ryan
[GENERAL] Guidance on building Foreign Data Wrapper on Windows.
Hi Hackers, I am interested in writing a FDW. I setup my environment on Windows. However, I am not able to figure out how to build FDW successfully. Here is what I have done: -Install the 9.2.2 by using EnterpriseDB installer. Add bin/ to the PATH -I have followed the windows build instruction here: http://www.postgresql.org/docs/9.2/static/install-windows-full.html. I can run and debug it in Visual Studio. But when I tried to build any existing FDW via cygwin, it just tells me this: C:/PostgreSQL/9.2/lib/pgxs/src/makefiels/pgxs.mk: No such file or directory There is no pgxs.mk in that folder. I have also read 35.16 Extension Building Infrastructure http://www.postgresql.org/docs/current/static/extend-pgxs.html. But it doesn't tell how to get PGXS. Since I see that file_fdw is available on windows. I am just wondering what's the right way to build fdw. Thanks in advance. Ryan
