Fwd: Disable autocommit inside dbeaver

Hi All, Is there a way we can disable autocommit option inside query writing area? Not by choosing auto commit from drop down menu. Thanks, Arun

Syntax

Hi All, What is the difference or use case for below syntaxes? do $$ declare d int; begin RAISE INFO 'Script started at %', CURRENT_TIMESTAMP; update employees set first_name = 'g' where employee_id = 1; get diagnostics d = row_count; raise info 'selected: % rows', d; RAISE INFO 'Script finished

write a sql block which will commit if both updates are successful else it will have to be rolled back

Hi All, Can someone guide me to "write a sql block which will commit if both updates are successful else it will have to be rolled back"? would like to explicitly specify both commit and rollback in code.. I would like to turn off the autocommit then execute the query. Below is a just a starter

Disable script execution in server level when updating via grids

Hello All, Is there a way we can disable grid based updates from the clients in the server? suppose if someone accidentally commits an edit in dbeaver,server shall decline that incoming request. However requests from the query tool should run I have seen some options from the client side. Do

Import csv to temp table

Dear All, Do we have any scripts that create a temp table with column names from the first row of csv files? any functions which we can pass the file name as parameter which loads the data to csv based on the data Thanks, ACDBA

Sample data generator for performance testing

Hi All, Do we have any open source tools which can be used to create sample data at scale from our postgres databases? Which considers data distribution and randomness Regards, Arun

Re: Sample data generator for performance testing

Klaver, wrote: > On 1/2/24 23:23, arun chirappurath wrote: > > Hi All, > > > > Do we have any open source tools which can be used to create sample data > > at scale from our postgres databases? > > Which considers data distribution and randomness > > >

Re: Sample data generator for performance testing

On Wed, 3 Jan, 2024, 23:03 Adrian Klaver, wrote: > On 1/3/24 09:24, arun chirappurath wrote: > > Hi Adrian, > > > > Thanks for your mail. > > > > Is this for all tables in the database or a subset? Yes > > Yes all tables or yes just some tables? > All

Re: Sample data generator for performance testing

Thanks for the insights.. Thanks, Arun On Wed, 3 Jan, 2024, 23:26 Jeremy Schneider, wrote: > On 1/2/24 11:23 PM, arun chirappurath wrote: > > Do we have any open source tools which can be used to create sample data > > at scale from our postgres databases? > > Which consid

Unable to find column

Dear all, I have a table automobile which has a column id. Table consists of id,make,year of manufacturing I use dbeaver for querying.. Select * from automobile provides me results However select id from automobile yields column doesn't exists. I tried double quotes on id As well but same err

Re: Unable to find column

Hi Adrian, \d shows the tables and this id which is a sequence. Regards, Arun On Mon, 15 Jan 2024 at 22:03, Adrian Klaver wrote: > On 1/15/24 08:16, arun chirappurath wrote: > > Dear all, > > > > I have a table automobile which has a column id. > > > >

postgres sql assistance

Dear all, I am an accidental postgres DBA and learning things every day. Apologies for my questions if not properly drafted. I am trying to load data from the temp table to the main table and catch the exceptions inside another table. temp table is cast with the main table data type and trying t

Re: postgres sql assistance

Hi Jim, Thank you so much for the kind review. Architect is pressing for a native procedure to data load. I shall Google ans try to find more suitable one than writing one by myself. Thanks again, Arun On Wed, 17 Jan, 2024, 01:58 Jim Nasby, wrote: > On 1/16/24 6:34 AM, arun chirappur

Unused indexes

Hi All, Do we have a script to get unused indexes for 30 days and once identified do we have an option to disable and enable when required? I sql server we have this option to disable it and need to rebuild it to ensemble it Thanks, Arun

Postgres pg_cron extension

Dear all, I am trying to enable pg_cron extension in RDS postgres and I got to know it will be enabled only in custom parameter group..it can't be enabled in default one. 1. Suppose if we create a custom group for existing postgres 14 databases,will all the existing parameters in default group get

pg_locks-exclusivelock for select queries

Dear all, I am running below query on a database. why is it creating a exclusive lock on a virtualxid? I am running some SELECT queries and its creating an ExclusiveLock in virtualxid? is this normal? SELECT datname, pid, state, query, age(clock_timestamp(), query_start) AS age FROM pg_stat_act

Seq scan vs index scan

Hi All, I have a table named users with index on user name. CREATE TABLE users ( user_id SERIAL PRIMARY KEY, username VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, age INT ); CREATE INDEX idx_username ON users (username); When I try to do below select query it's tak

Re: Seq scan vs index scan

Thanks Tom,David and Chris for detailed opinions Regards, Arun On Sat, 23 Mar 2024 at 09:25, arun chirappurath wrote: > Hi All, > > I have a table named users with index on user name. > > CREATE TABLE users ( > user_id SERIAL PRIMARY KEY, > username VARCHAR(50)

Statistics information.

Dear All, Apologies the way i am asking question as i am more a SQL Server person and a new postgre man.. I have used a query store in SQL server. it provides me option to load statistics data to temp table and get below important information. 1. Last run duration 2. Average time for execution.

Table level restore in postgres

Dear all, I am a new bie in postgres world Suppose I have accidently deleted a table or deleted few rows ,is it safe to drop this table and restore just this table from custom backup to same database? Or should I create a new database and restore it there and then migrate the data? What is

Access issue for system queries

Dear all, I have granted access to pg_read_all_stats and pg_read_allsettings to user..still they are not able to receive results from this query.its empty..we can run SELECT * FROM pg_stat_statements alone..but not below statement..what could be the reason? WITH statements AS ( SELECT * F

Re: Access issue for system queries

Ok, I'll check it out. Thank you. On Sat, 30 Mar, 2024, 10:36 Julien Rouhaud, wrote: > On Sat, Mar 30, 2024 at 12:47 PM arun chirappurath > wrote: > > > > I have granted access to pg_read_all_stats and pg_read_allsettings to > user..still they are not able to

Sql scripts execution

Hi All, What is the generally used open source solution for deploying dml and ddl scripts for monthly release on postgres rds? Can we use github actions to perform the same Thanks, Arun

Execution history of a single query

Hi All, >From pg_stat_statements we can get the overall execution details of queries. Can we get the execution details of a single queryid ? Like today it took 6 seconds,yesterday 5 and so on..just for one query. Thanks, Arun

Execute permission to function

Hi all I am using rds postgres 14. I have created few users and added them to pg_readall_data and pg_write_alldata groups They are able to read all data and do update in tables However they can't execute functions and not able to script out objects from pg_admin Any other role to be added? Th

Table and data comparison

Hi All, Do we have any open-source utility to compare two table data not structure and then provide some sort of reports? Row by row comparison Thanks, Arun