Re: Loading 500m json files to database

Try to write a stored procedure (probably pl/python) that will accept an array of JSON objects so it will be possible to load data in chunks (by 100-1000 files) which should be faster. > On 23. Mar 2020, at 12:49, Ertan Küçükoğlu > wrote: > > >> On 23 Mar 2020, at 13:20, pinker wrote: >>

Re: Web users as database users?

I used to use a different approach: 1. Create auth() pl/python procedure as follows: create or replace function auth(auser_id integer) returns void as $$ GD['user_id'] = auser_id $$ language plpythonu; This procedure is supposed to be called after a sucesseful authorisation (in a database

Re: Limit transaction lifetime

> If it's a single command you're trying to limit `SET statement_timeout TO > ` should do the trick. This will set only statement timeout but won’t work for long transactions that contain a lot of short statements. > If you want it based on the session's cumulative statement time, off the

Limit transaction lifetime

Is there a way to limit a transaction lifetime in PostgreSQL? I could use `idle_in_transaction_session_timeout` parameter but it applies only to IDLE transactions. However, I want to rollback any transaction that executes more than specified period of time. -- With best regards, Andrei Zhidenkov.

Re: format return of "age" to hh:mm

However, you cannot use to_char() to display the count of days for a given interval. In this case, if your interval is larger than 24 hours, you might use extract(epoch from ) and perform the conversion manually. > On 5. Mar 2020, at 17:07, Ray O'Donnell wrote: > > On 05/03/2020 15:50, David

Re: A question relative to creating an audit table

27, 2020 at 10:30:15PM +0100, Andrei Zhidenkov wrote: >> Why not to pass TG_TABLE_SCHEMA and TG_TABLE_NAME in its arguments? >> >>> On 27. Feb 2020, at 22:28, stan wrote: >>> >>> I ma considering setting up a function, and triggers to put a record in an >

Re: A question relative to creating an audit table

Why not to pass TG_TABLE_SCHEMA and TG_TABLE_NAME in its arguments? > On 27. Feb 2020, at 22:28, stan wrote: > > I ma considering setting up a function, and triggers to put a record in an > audit table when certain tables are altered. I pretty much think I know how > to do this, with one

Re: Force Commit

You can workaround by simulation autonomous transaction using plpython or dblink. Or just performing a commit outside the stored procedure. > On 5. Feb 2020, at 09:06, İlyas Derse wrote: > > I'm writing to you about Commit. I want to do force commit query even if I > have exception. > It's

Re: How to avoid UPDATE on same data in table ?

Have you tried to use built-in suppress_redundant_updates_trigger[1] for this? 1. https://www.postgresql.org/docs/12/functions-trigger.html > On 3. Feb 2020, at 09:27, Condor wrote: > > On 02-02-2020 23:06, Adrian Klaver wrote: >> On

Re: Race condition while creating a new partition

I’m creating a new partition for every second deliberately in order to faster reproduce a bug I have on the live environment. In the live environment a new partitions are being created every one day. More to that, we create new partitions in advance and this procedure is only a backup

Re: Race condition while creating a new partition

453 i would assume both sending the > same command Create Table Partition. Are these two connections from > different computers or the same computer using multi threading?? > > What does Postgresql Log show what is going on?? > > On Mon, Dec 16, 2019 at 5:32 AM Andrei Zhidenkov

Re: Race condition while creating a new partition

47+00'); " > On 15. Nov 2019, at 11:49, Andrei Zhidenkov wrote: > > We use this code in order to automatically create new partitions for a > partitioned table (Postgres 10.6): > > begin > insert into ; > exception when undefined_table then > begin > >

Re: Is there an equivalent to sp_getapplock, sp_releaseapplock in PostgreSql?

I’m not sure what these function exactly do in MSSQL but what in Postgres you probably can use “Advisory Locks” (https://www.postgresql.org/docs/9.4/explicit-locking.html ) in order to achive this. > On 13. Dec 2019, at 13:31, İlyas

Re: Weird seqscan node plan

At this point I disagree. It’s faster to fetch one row using seq scan that using index scan as well as fetching number of consecutive rows is faster via seq scan. Index scan is not always faster. > On 27. Nov 2019, at 04:53, Игорь Выскорко wrote: > > Why planner mistakes in determining the

Re: Weird seqscan node plan

How many tables do you have in your query? If too many, in your case “Genetic Query Optiomiation” might be used (https://www.postgresql.org/docs/10/geqo-pg-intro.html ). > On 26. Nov 2019, at 03:19, Игорь Выскорко wrote: > > Hi all! >

Re: Partitioning large table (140GB)

You can also use extensions like pg_partman or pg_pathman. The last one allows to partition tables smoothly on live environments. > On 20. Nov 2019, at 11:49, Ravi Krishna wrote: > >> In our production, we use postgres 9.5 with streaming replication >> using repmgr, there is a large table of

Race condition while creating a new partition

something? Thank you. — With best regards, Andrei Zhidenkov.