How to find the hits on the databases and tables in Postgres
Hi, Any one can please help me out How to monitor the Hits on database and how many hits on each user tables Through query. Is there any other tools for that so it can full fill my requirement for it . Thanks,
Re: How to find the hits on the databases and tables in Postgres
Hi Adrian Klaver, Thanks for the fast response . But here is the issue is here we have 200+ databases and 5 servers so cannot manually runs this command all ways is there any 3rd party tool for that which would give me the hits on DB and tables in it so that it would be so help full for me. now currently prepared a scripts for that which goes and does for DB butr after that i have much manually work with it so is there any third party tool for that Thanks, On Fri, May 4, 2018 at 6:51 PM, Adrian Klaver wrote: > On 05/04/2018 04:44 AM, nikhil raj wrote: > >> Hi, >> Any one can please help me out >> >> How to monitor the Hits on database and how many hits on each user >> tables Through query. >> Is there any other tools for that so it can full fill my requirement for >> it . >> > > https://www.postgresql.org/docs/10/static/monitoring-stats.html > > https://www.postgresql.org/docs/10/static/monitoring-stats. > html#PG-STAT-DATABASE-VIEW > > https://www.postgresql.org/docs/10/static/monitoring-stats. > html#PG-STAT-DATABASE-VIEW > > pg_stat_user_tables Same as pg_stat_all_tables, except that only user > tables are shown. > > >> Thanks, >> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Why is my Postgre server went in recovery mode all in sudden
Hi Team, I dont have any idea why did my Postgres server crash and it says timestamp=2018-05-07 00:34:11.209 EDT,user=,db=,app=,client=, transaction-ID=0,session_start_timestamp=2018-04-26 10:08:19 EDT,SQL_state=0LOG: worker process: parallel worker for PID 2864 (PID 4476) exited with exit code 0 timestamp=2018-05-07 00:34:11.209 EDT,user=,db=,app=,client=, transaction-ID=0,session_start_timestamp=2018-04-26 10:08:19 EDT,SQL_state=0LOG: terminating any other active server processes timestamp=2018-05-07 00:34:11.209 EDT,user=postgres,db=Ozalo, app=[unknown],transaction-ID=0,session_start_timestamp=2018-05-07 00:32:46 EDT,SQL_state=0LOG: duration: 0.331 ms bind : SELECT "repository"."c_token" AS "token", "repository"."c_path" AS "path" FROM t_e20so1_repository AS "repository" INNER JOIN t_e20so1_document_bigint AS "documentbigint" ON "repository"."c_repositoryid" = "documentbigint"."c_value" WHERE "documentbigint"."c_documentid" = 201989 AND "documentbigint"."c_fieldid" = 'b035afc8-439f-4f2c-a9ae- e054052511fd' timestamp=2018-05-07 00:34:11.210 EDT,user=postgres,db= Ozalo,app=[unknown], transaction-ID=0,session_start_timestamp=2018-05-07 00:32:46 EDT,SQL_state=0LOG: duration: 0.061 ms execute : SELECT "repository"."c_token" AS "token", "repository"."c_path" AS "path" FROM t_zs01_sys_dm AS "repository" INNER JOIN t_e20so1_document_bigint AS "documentbigint" ON "repository"."c_repositoryid" = "documentbigint"."c_value" WHERE "documentbigint"."c_documentid" = 201989 AND "documentbigint"."c_fieldid" = 'b035afc8-439f-4f2c-a9ae- e054052511fd' after Some time i was receiving this error timestamp=2018-05-07 00:34:11.218 EDT,user=postgres,db= Ozalo ,app=[unknown],transaction-ID=0,session_start_timestamp=2018-05-07 00:34:10 EDT,SQL_state=57P02WARNING: terminating connection because of crash of another server process timestamp=2018-05-07 00:34:11.218 EDT,user=postgres,db= Ozalo ,app=[unknown],transaction-ID=0,session_start_timestamp=2018-05-07 00:34:10 EDT,SQL_state=57P02DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. timestamp=2018-05-07 00:34:11.218 EDT,user=postgres,db= Ozalo ,app=[unknown],transaction-ID=0,session_start_timestamp=2018-05-07 00:34:10 EDT,SQL_state=57P02HINT: In a moment you should be able to reconnect to the database and repeat your command. timestamp=2018-05-07 00:34:11.357 EDT,user=,db=,app=,client=, transaction-ID=0,session_start_timestamp=2018-04-26 10:08:20 EDT,SQL_state=57P02DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. timestamp=2018-05-07 00:34:11.357 EDT,user=,db=,app=,client=, transaction-ID=0,session_start_timestamp=2018-04-26 10:08:20 EDT,SQL_state=57P02HINT: In a moment you should be able to reconnect to the database and repeat your command. timestamp=2018-05-07 00:34:11.379 EDT,user=postgres,db= Ozalo,app=[unknown],,transaction-ID=0,session_start_timestamp=2018-05-07 00:34:11 EDT,SQL_state=57P03FATAL: the database system is in recovery mode timestamp=2018-05-07 00:34:11.381 EDT,user=postgres,db= Ozalo,app=[unknown],transaction-ID=0,session_start_timestamp=2018-05-07 00:34:11 EDT,SQL_state=57P03FATAL: the database system is in recovery mode what is the reason it corrupted share memory ? what is meant by The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. ? how much of share memory if its consume it will crash Please can any one help me in this or else what is the reason of crash of DB server Current using 10.3 Current Config max_connections = 5000 shared_buffers = 7680MB effective_cache_size = 23040MB maintenance_work_mem = 1920MBmin_wal_size = 1GBmax_wal_size = 2GBcheckpoint_completion_target = 0.7wal_buffers = 16MBdefault_statistics_target = 100random_page_cost = 1.1effective_io_concurrency = 200max_worker_processes = 16max_parallel_workers_per_gather = 8max_parallel_workers = 16work_mem = 196kB Thanks
Re: Why is my Postgre server went in recovery mode all in sudden
1) What OS and version? ans: windows 2012R2 2) System memory size is ? ans: 32GB 3) What is session_start_timestamp tracking? In other words what does it match up to here: ans: This is the format to timestamp=%m,user=%u,db=%d,app=%a,client=%h,transaction- ID=%x,session_start_timestamp=%s,SQL_state=%e The time when the session is session_started _start_timestamp 4) What is the process that started 2018-04-26 10:08:19? ans: Its an backed process of Postgre started parallel worker 5) The query in the log started at 2018-05-07 00:32:46, what is it doing? ans : query running from an agent if any processing is going on the front end some of the query will run On Thu, May 10, 2018 at 5:10 AM, Adrian Klaver wrote: > On 05/09/2018 11:31 AM, nikhil raj wrote: > >> Hi Team, >> >> I dont have any idea why did my Postgres server crash and it says >> >> timestamp=2018-05-07 00:34:11.209 EDT,user=,db=,app=,client=,tra >> nsaction-ID=0,session_start_timestamp=2018-04-26 10:08:19 >> EDT,SQL_state=0LOG: worker process: parallel worker for PID 2864 (PID >> 4476) exited with exit code 0 >> timestamp=2018-05-07 00:34:11.209 EDT,user=,db=,app=,client=,tra >> nsaction-ID=0,session_start_timestamp=2018-04-26 10:08:19 >> EDT,SQL_state=0LOG: terminating any other active server processes >> timestamp=2018-05-07 00:34:11.209 EDT,user=postgres,db=Ozalo,app >> =[unknown],transaction-ID=0,session_start_timestamp=2018-05-07 00:32:46 >> EDT,SQL_state=0LOG: duration: 0.331 ms bind : SELECT >> "repository"."c_token" AS "token", "repository"."c_path" AS "path" >> >> FROM >> >> t_e20so1_repository AS "repository" INNER JOIN >> t_e20so1_document_bigint AS "documentbigint" ON >> "repository"."c_repositoryid" = "documentbigint"."c_value" >> >>WHERE "documentbigint"."c_documentid" = 201989 >> >> AND "documentbigint"."c_fieldid" = 'b035afc8-439f-4f2c-a9ae-e0540 >> 52511fd' >> timestamp=2018-05-07 00:34:11.210 EDT,user=postgres,db= >> Ozalo,app=[unknown],transaction-ID=0,session_start_timestamp=2018-05-07 >> 00:32:46 EDT,SQL_state=0LOG: duration: 0.061 ms execute : >> SELECT "repository"."c_token" AS "token", "repository"."c_path" AS "path" >> FROM >> t_zs01_sys_dm AS "repository" INNER JOIN t_e20so1_document_bigint AS >> "documentbigint" ON "repository"."c_repositoryid" = >> "documentbigint"."c_value" >>WHERE "documentbigint"."c_documentid" = 201989 >> AND "documentbigint"."c_fieldid" = 'b035afc8-439f-4f2c-a9ae-e0540 >> 52511fd' >> >> >> after Some time i was receiving this error >> >> >> timestamp=2018-05-07 00:34:11.218 EDT,user=postgres,db= Ozalo >> ,app=[unknown],transaction-ID=0,session_start_timestamp=2018-05-07 >> 00:34:10 EDT,SQL_state=57P02WARNING: terminating connection because of >> crash of another server process >> timestamp=2018-05-07 00:34:11.218 EDT,user=postgres,db= Ozalo >> ,app=[unknown],transaction-ID=0,session_start_timestamp=2018-05-07 >> 00:34:10 EDT,SQL_state=57P02DETAIL: The postmaster has commanded this >> server process to roll back the current transaction and exit, because >> another server process exited abnormally and possibly corrupted shared >> memory. >> timestamp=2018-05-07 00:34:11.218 EDT,user=postgres,db= Ozalo >> ,app=[unknown],transaction-ID=0,session_start_timestamp=2018-05-07 >> 00:34:10 EDT,SQL_state=57P02HINT: In a moment you should be able to >> reconnect to the database and repeat your command. >> >> timestamp=2018-05-07 00:34:11.357 EDT,user=,db=,app=,client=,tra >> nsaction-ID=0,session_start_timestamp=2018-04-26 10:08:20 >> EDT,SQL_state=57P02DETAIL: The postmaster has commanded this server >> process to roll back the current transaction and exit, because another >> server process exited abnormally and possibly corrupted shared memory. >> timestamp=2018-05-07 00:34:11.357 EDT,user=,db=,app=,client=,tra >> nsaction-ID=0,session_start_timestamp=2018-04-26 10:08:20 >> EDT,SQL_state=57P02HINT: In a moment you should be able to reconnect to >> the database and repeat your command. >> timestamp=2018-05-07 00:34:11.379 EDT,user=postgres,db= >> Ozalo,app=[unknown],,transaction-ID=0,session_start_timestamp=2018-05-07 >> 00:34:11 EDT,SQL_state=57P03FATAL: the database system
Re: Why is my Postgre server went in recovery mode all in sudden
Hi Adrian Klaver, No its like it opens connection and once the query gets execute it goes to idle connection and again idle connection time out i kept it for 2 mins . It never happen again. need few answers why will share buffer crashes ? Thanks, On Thu, May 10, 2018 at 7:28 PM, Adrian Klaver wrote: > On 05/10/2018 04:42 AM, nikhil raj wrote: > >> 1) What OS and version? >> >> ans: windows 2012R2 >> >> 2) System memory size is ? >> >> ans: 32GB >> >> 3) What is session_start_timestamp tracking? In other words what does it >> match up to here: >> ans: This is the format to >> timestamp=%m,user=%u,db=%d,app=%a,client=%h,transaction-ID=% >> x,session_start_timestamp=%s,SQL_state=%e >>The time when the session is session_started _start_timestamp >> >> 4) What is the process that started 2018-04-26 10:08:19? >> >> ans: Its an backed process of Postgre started parallel worker >> >> 5) The query in the log started at 2018-05-07 00:32:46, what is it doing? >> ans : query running from an agent if any processing is going on the front >> end some of the query will run >> > > So there is process that opens a connection, leaves it open and then > periodically runs queries? > > Has the database shutdown happened again? > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
pg_stat_statements in the query column I am not getting the exact query
Hello I am using postgres 10.3 version current facing an issue related query tracking whenever I run SELECT * FROM pg_stat_statements in the query column I am not getting the exact query with values with variable but instead I am getting $1, $2 in the query example- "update ss set number=$1 where number =$2" How can I get the whole exact query and what changes should I do it? "update ss set number=245 where number =bill" Thanks.
Having the issue in while selecting the data and feltering in order by.
Hi ALL,
I have recently migrated to windows postgres server to linux postgres
server.
while i run this query in windows PG server
SELECT *
FROM (VALUES ('a'), ('___b1'), ('_a1'),('a2'),('a3'),('a5'), ('a2')) t
(val) order by val
I am getting the out put in this sort order
[image: 10.250.200.184 - Remote Desktop Connection 2019-08-08 14.46.46.png]
Same when i run this in linux machine i am getting this out in different
sort order on the same query.
[image: 10.250.200.184 - Remote Desktop Connection 2019-08-08 14.42.27.png]
database "cdf_100_1313" does not exist
Hi All,
The DB is already there and getting the error of the DB does not exist
where I execute it via shell.
DBLIST=/usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d postgres -q
-t -c "SELECT datname from pg_database where datname in
('CDF_100_1313')"/usr/lib/postgresql/11/bin/psql
-p 5432 -U postgres -d $DBLIST -c "GRANT CONNECT ON DATABASE "$DBLIST" TO
cpupdate"
ERROR: database "cdf_100_1313" does not exist
Re: database "cdf_100_1313" does not exist
Hi Karsten,
yup you are right can you help in i tried by removing quotion and also
adding extra having same issue.
On Mon, Sep 9, 2019 at 5:25 PM Karsten Hilbert
wrote:
> On Mon, Sep 09, 2019 at 05:16:20PM +0530, nikhil raj wrote:
>
> >
> > The DB is already there and getting the error of the DB does not exist
> > where I execute it via shell.
> > DBLIST=/usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d postgres -q
> > -t -c "SELECT datname from pg_database where datname in
> > ('CDF_100_1313')"/usr/lib/postgresql/11/bin/psql
> > -p 5432 -U postgres -d $DBLIST -c "GRANT CONNECT ON DATABASE "$DBLIST" TO
> > cpupdate"
> >
> > ERROR: database "cdf_100_1313" does not exist
>
> Likely a quoting issue.
>
> Karsten Hilbert
> --
> GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
>
>
>
Re: database "cdf_100_1313" does not exist
Hi Dan,
Still facing the same issue.
/usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d $DBLIST -c "GRANT
CONNECT ON DATABASE $DBLIST TO cpupdate"
ERROR: database "cdf_100_1313" does not exist
On Mon, Sep 9, 2019 at 6:38 PM Dan Livingston
wrote:
>
> I think you need to add parens around the command to populate DBLIST and
> remove the quotes in the GRANT command.
>
> In bash this works for me:
>
>
> DBLIST=$(/usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d postgres
> -q -t -c "SELECT datname from pg_database where datname in ('tempdb')")
> /usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d $DBLIST -c "GRANT
> CONNECT ON DATABASE $DBLIST TO dbuser1"
>
>
>
>
>
> On Mon, Sep 9, 2019 at 5:58 AM nikhil raj wrote:
>
>> Hi Karsten,
>>
>> yup you are right can you help in i tried by removing quotion and also
>> adding extra having same issue.
>>
>> On Mon, Sep 9, 2019 at 5:25 PM Karsten Hilbert
>> wrote:
>>
>>> On Mon, Sep 09, 2019 at 05:16:20PM +0530, nikhil raj wrote:
>>>
>>> >
>>> > The DB is already there and getting the error of the DB does not exist
>>> > where I execute it via shell.
>>> > DBLIST=/usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d postgres
>>> -q
>>> > -t -c "SELECT datname from pg_database where datname in
>>> > ('CDF_100_1313')"/usr/lib/postgresql/11/bin/psql
>>> > -p 5432 -U postgres -d $DBLIST -c "GRANT CONNECT ON DATABASE "$DBLIST"
>>> TO
>>> > cpupdate"
>>> >
>>> > ERROR: database "cdf_100_1313" does not exist
>>>
>>> Likely a quoting issue.
>>>
>>> Karsten Hilbert
>>> --
>>> GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
>>>
>>>
>>>
Re: database "cdf_100_1313" does not exist
Hi Rob, Please find the out put. Name | Owner | Encoding | Collate |Ctype| Access privileges --+--+--+-+-+--- CDF_10_11 | cpuser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | cpuser=CTc/cpuser CDF_History | cpuser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | cpuser=CTc/cpuser CDF_100_1313 | cpuser | UTF8 | en_US.UTF-8 | en_US.UTF-8 | cpuser=CTc/cpuser On Mon, Sep 9, 2019 at 6:58 PM Rob Sargent wrote: > > > On Sep 9, 2019, at 7:16 AM, nikhil raj wrote: > > Hi Dan, > > Still facing the same issue. > > /usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d $DBLIST -c "GRANT > CONNECT ON DATABASE $DBLIST TO cpupdate" > ERROR: database "cdf_100_1313" does not exist > > >>>> Can we see the output of psql’s \l ? >
Re: database "cdf_100_1313" does not exist
Hi All, just did few modification of the shell command still having the same issue. Having the issue with the Grant command only its not taking the parameters. /usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d $DBLIST -c "GRANT CONNECT ON DATABASE \"$DBLIST\" TO cpupdate" ERROR: database " CDF_100_1313" does not exist *** For the above one we have its taking space in the Databases name starting.* /usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d $DBLIST -c "GRANT CONNECT ON DATABASE "$DBLIST" TO cpupdate" psql: warning: extra command-line argument "ASD_100_1313 TO cpupdate" ignored ERROR: syntax error at end of input LINE 1: GRANT CONNECT ON DATABASE ^ On Mon, Sep 9, 2019 at 7:47 PM Tom Lane wrote: > > On Sep 9, 2019, at 7:16 AM, nikhil raj wrote: > >> /usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d $DBLIST -c > "GRANT CONNECT ON DATABASE $DBLIST TO cpupdate" > >> ERROR: database "cdf_100_1313" does not exist > > This still isn't quoted properly. It's tricky since double-quote is > special to both the shell and SQL. You need something like > > /usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d "$DBLIST" -c "GRANT > CONNECT ON DATABASE \"$DBLIST\" TO cpupdate" > > regards, tom lane >
Re: database "cdf_100_1313" does not exist
Hi Adrian,
DBLIST=/usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d postgres -q
-t -c "SELECT datname from pg_database where datname in
('CDF_100_1313')"/usr/lib/postgresql/11/bin/psql
-p 5432 -U postgres -d $DBLIST -c "GRANT CONNECT ON DATABASE \"$DBLIST\" TO
cpupdate"
only in the grant its taking space rest in -d its perfectly all right.
On Mon, Sep 9, 2019 at 8:04 PM Adrian Klaver
wrote:
> On 9/9/19 7:30 AM, nikhil raj wrote:
> > Hi All,
> >
> > just did few modification of the shell command still having the same
> > issue. Having the issue with the Grant command only its not taking the
> > parameters.
> >
> > /usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d $DBLIST -c
> > "GRANT CONNECT ON DATABASE \"$DBLIST\" TO cpupdate"
> > ERROR: database " CDF_100_1313" does not exist
>
> You are not showing how you are currently generating DBLIST. From the
> looks of it you have a leading space in the database name:
>
> " CDF_100_1313"
>
>
> > *
> > *
> > *** For the above one we have its taking space in the Databases name
> > starting.*
> >
> > /usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d $DBLIST -c "GRANT
> > CONNECT ON DATABASE "$DBLIST" TO cpupdate"
> > psql: warning: extra command-line argument "ASD_100_1313 TO cpupdate"
> > ignored
> > ERROR: syntax error at end of input
> > LINE 1: GRANT CONNECT ON DATABASE
> >^
> >
> >
> >
> > On Mon, Sep 9, 2019 at 7:47 PM Tom Lane > <mailto:t...@sss.pgh.pa.us>> wrote:
> >
> > > On Sep 9, 2019, at 7:16 AM, nikhil raj > <mailto:nikhilraj...@gmail.com>> wrote:
> > >> /usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d $DBLIST
> > -c "GRANT CONNECT ON DATABASE $DBLIST TO cpupdate"
> > >> ERROR: database "cdf_100_1313" does not exist
> >
> > This still isn't quoted properly. It's tricky since double-quote is
> > special to both the shell and SQL. You need something like
> >
> > /usr/lib/postgresql/11/bin/psql -p 5432 -U postgres -d "$DBLIST" -c
> > "GRANT CONNECT ON DATABASE \"$DBLIST\" TO cpupdate"
> >
> > regards, tom lane
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
Re: Changing default ../data/ directory
Hi Rich Shepard, Step 1-: Stop the postgres services and change the path of the data directory in postgres.conf file. Step 2-: rsync the flie to new path. And start the service. Step 3-: connect to postgres and check command output from psql shell SHOW data_directory; should see now data path. On Sat, 4 Jan 2020, 8:50 pm Rich Shepard, wrote: > I run Slackware (currently -14.2/x86_64) and postgres-11.5. The data > directory has always been located in /var/lib/pgsql//data. This > data directory is located in the / partition on a 240G SSD. > > There's another 2T HDD with a /data partition and I want to both move the > current /var/lib/pgsql/data cluster to /data/pgsql/11 and have all future > databases use that location. > > The PGDATA environment variable used to be used for this (but I never did > get it satisfactorily working). Web searches show changing initdb or, > perhaps, postgresql.conf (there is a /usr/share/postgresql.conf.sample but > no postgresql.conf). > > As this is all new to me I want to learn how to: > > 1) Define a postgresl.conf and learn where it should be located. > > 2) Move all current databased in /var/lib/pgsql/11/data to /data/pgsql/11. > > 3) Create all new databases in /data/pgsql/11 by default. > > I'm sure it's in the manual yet I'm unsure just where to start reading. > > Rich > > >
Time zone "GST" not recognized.
Hi ALL, Required help with the time zone. i am getting error on the GST time line. is tht a bug or postgres doesn't support the GST time zone. select CURRENT_TIMESTAMP AT TIME ZONE 'GST' ERROR: time zone "GST" not recognized SQL state: 22023 Postgres -:11.2 Ubuntu:-18.04
Re: Time zone "GST" not recognized.
Hi Adrian, Thanks for the quick response. Please can you help me over here So Postgres is not supporting GST ( Gulf Standard Time) time zone . As if we have a entries of few things with timestamp with time zone from application end. example :- In 2 column we are entering these details but when client from GST time zone i am getting 21:41:39 GST [image: image.png] but which i insert into the databases is showing empty column. On Fri, Jan 24, 2020 at 11:09 PM Adrian Klaver wrote: > On 1/24/20 9:19 AM, Adrian Klaver wrote: > > On 1/24/20 8:59 AM, nikhil raj wrote: > >> Hi ALL, > >> > >> Required help with the time zone. i am getting error on the GST time > >> line. is tht a bug or postgres doesn't support the GST time zone. > >> > >> > >> select CURRENT_TIMESTAMP AT TIME ZONE 'GST' > >> ERROR: time zone "GST" not recognized SQL state: 22023 > > > > Try GMT: > > > > test_(postgres)# select CURRENT_TIMESTAMP AT TIME ZONE 'GMT'; > >timezone > > > > 2020-01-24 17:17:52.754861 > > Aah, is this what you are looking for?: > > https://24timezones.com/time-zone/gst > > It seems to be an alias for UTC+04, though not recognized by IANA. That > would be: > > Asia/Dubai or Asia/Muscat > > Unless you are talking about: > > https://24timezones.com/time-zone/gst_georgia > > > > > For list of recognized abbreviations: > > > > https://www.postgresql.org/docs/11/view-pg-timezone-abbrevs.html > > > >> > >> > >> Postgres -:11.2 > >> Ubuntu:-18.04 > >> > > > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Getting a error on creating a partition table index 12.2.
HI ALL, While creating the index on the partition table i am getting error on it saying *ERROR: cannot specify default tablespace for partitioned relations SQL state: 0A00.* Query is * CREATE INDEX t_e20so1_doi_c_doid_idxON public.t_e20so1_doi USING btree(i_doid ASC NULLS LAST)TABLESPACE pg_default;* Note:- but the same query is executed in the 12.1 version.its working fine.
Re: Getting a error on creating a partition table index 12.2.
Hi Adrian, On that table nothing was happening just created the table and later o was creating the index and i was getting this error. Please can you tell me is this the draw back of that in 12.2 version. On Fri, 6 Mar 2020, 12:04 am Adrian Klaver, wrote: > On 3/5/20 10:04 AM, nikhil raj wrote: > > HI ALL, > > > > While creating the index on the partition table i am getting error on > > it saying > > > > *ERROR: cannot specify default tablespace for partitioned relations SQL > > state: 0A00.* > > > > Query is > > > > *CREATE INDEX t_e20so1_doi_c_doid_idx > > ON public.t_e20so1_doi USING btree > > (i_doid ASC NULLS LAST) > > TABLESPACE pg_default;* > > * > > * > > * > > * > > Note:- but the same query is executed in the 12.1 version.its working > fine. > > Well this ERROR appeared here: > > > https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;f=src/backend/commands/indexcmds.c;h=87259588d0ab0b8e742e30596afa7ae25caadb18 > > Thu, 25 Apr 2019 06:20:23 -0800 (10:20 -0400) > > That would encompass 12.1 also. > > Are you doing anything else to public.t_e20so1_doi prior to the above? > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: Getting a error on creating a partition table index 12.2.
Ok, thanks for the clarification. On Fri, Mar 6, 2020 at 12:31 AM Adrian Klaver wrote: > On 3/5/20 10:48 AM, nikhil raj wrote: > > Hi Adrian, > > > > On that table nothing was happening just created the table and later o > > was creating the index and i was getting this error. > > > > Please can you tell me is this the draw back of that in 12.2 version. > > > > Unfortunately I don't have answer for you on that. Someone with more > knowledge of the internals will have to comment. > > For now the solution would seem to be not to specify the TABLESPACE if > you want to use the default. > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Tempory table is not getting created inside Function in postgres.
HI Team,
This is the Function I have created successfully but while executing it
throughs an error temp table doesn't exist.
But the same when I execute it not inside the function from **drop temp
table to end insert select ** it work fine
Please can any one help me why in the function i am not able to create the
temp table. what is alternative
`-- FUNCTION: api.post_publish_Roster()
-- DROP FUNCTION IF EXISTS api."post_publish_Roster"();
CREATE OR REPLACE FUNCTION api."post_publish_Roster"(
)
RETURNS void
LANGUAGE 'sql'
COST 100
VOLATILE PARALLEL UNSAFE
AS $BODY$
DROP TABLE IF EXISTS ROSTER_TABLE;
CREATE TEMP TABLE ROSTER_TABLE AS
SELECT ROSTER_ID,
LINK_ID,
PAYNUMBER,
USERNAME,
LINE_POSITION,
CREWNAME,
WEEKNUMBER,
WEEKSTARTDATE,
WEEKENDDATE
FROM CREW_LINKS.LINKS_MAP
CROSS JOIN LATERAL GET_WEEKS('2023-02-12',
'2023-03-04') AS WEEKDATA
WHERE ROSTER_ID = 234
AND WEEKDATA.WEEKNUMBER in
(SELECT MIN(WEEKNUMBER)
FROM GET_WEEKS('2023-02-12',
'2023-03-04'));
DO $$
DECLARE
weekstart INTEGER;
weekend INTEGER ;
BEGIN
select min(weeknumber) into weekstart from get_weeks('2023-02-12',
'2023-03-04');
select max(weeknumber) into weekend from get_weeks('2023-02-12',
'2023-03-04') ;
WHILE weekstart < weekend LOOP
INSERT INTO roster_table
SELECT roster_id, link_id, paynumber, username, line_position+1
AS line_position , crewname,rt.weeknumber+1 AS weeknumber
,w.weekstartdate,w.weekenddate
FROM roster_table rt
INNER JOIN
(select * from get_weeks('2023-02-12', '2023-03-04'))w
ON w.weeknumber=rt.weeknumber+1
WHERE rt.weeknumber=weekstart;
update roster_table rw
set line_position=(select min(line_position) from roster_table )
where weeknumber=weekstart+1 and line_position =(select
MAX(line_position) from roster_table ) ;
weekstart := weekstart + 1;
END LOOP;
END $$;
WITH COMBIN AS
(SELECT R.DEPOT,
R.GRADE,
R.VALID_FROM,
R.VALID_TO,
RD.ROWNUMBER,
RD.SUNDAY,
RD.MONDAY,
RD.TUESDAY,
RD.WEDNESDAY,
RD.THURSDAY,
RD.FRIDAY,
RD.SATURDAY,
RD.TOT_DURATION
FROM CREW_ROSTER.ROSTER_NAME R
JOIN CREW_ROSTER.DRAFT RD ON R.R_ID = RD.R_ID
WHERE R.R_ID = 234),
div AS
(SELECT DEPOT,
GRADE,
VALID_FROM,
VALID_TO,
ROWNUMBER,
UNNEST('{sunday,
monday,
tuesday,
wednesday,
thursday,
friday,
saturday }'::text[]) AS COL,
UNNEST(ARRAY[ SUNDAY :: JSON,
MONDAY :: JSON,
TUESDAY :: JSON,
WEDNESDAY :: JSON,
THURSDAY :: JSON,
FRIDAY :: JSON,
SATURDAY:: JSON]) AS COL1
FROM COMBIN),
DAY AS
(SELECT date::date,
TRIM (BOTH TO_CHAR(date, 'day'))AS DAY
FROM GENERATE_SERIES(date '2023-02-12', date '2023-03-04',interval '1
day') AS T(date)), FINAL AS
(SELECT *
FROM div C
JOIN DAY D ON D.DAY = C.COL
ORDER BY date,ROWNUMBER ASC), TT1 AS
(SELECT ROWNUMBER,date,COL,
(C - >> 'dia_id') :: UUID AS DIA_ID,
(C - >> 'book_on') ::TIME AS BOOK_ON,
(C - >> 'turn_no') ::VARCHAR(20) AS TURN_NO,
(C - >> 'Turn_text') ::VARCHAR(20) AS TURN_TEXT,
(C - >> 'book_off') :: TIME AS BOOK_OFF,
(C - >> 'duration') ::interval AS DURATION
FROM FINAL,
JSON_ARRAY_ELEMENTS((COL1)) C),
T1 AS
(SELECT ROW_NUMBER() OVER (ORDER BY F.DATE,F.ROWNUMBER)AS R_NO,
F.DEPOT,
F.GRADE,
F.VALID_FROM,
F.VALID_TO,
F.ROWNUMBER,
F.COL,
F.COL1,
F.DATE,
F.DAY,
T.DIA_ID,
T.BOOK_ON,
T.TURN_NO,
T.TURN_TEXT,
T.BOOK_OFF,
T.DURATION
FROM TT1 T
FULL JOIN FINAL F ON T.ROWNUMBER = F.ROWNUMBER
AND T.DATE = F.DATE
AND T.COL = F.COL),
T2 AS
(SELECT *,
GENERATE_SERIES(WEEKSTARTDATE,
WEEKENDDATE, interval '1 day')::date AS D_DATE
FROM ROSTER_TABLE
ORDER BY D_DATE,
LINE_POSITION)
INSERT INTO CREW_ROSTER.PUBLISH_ROSTER
(PAYNUMBER,DEPOT,GRADE,R_ID,ROSTER_DATE,DAY,TURNNO,TURNNO_TEXT,BOOK_ON,BOOK_OFF,DURATION,DIAGRAM_ID,INSERTION_TIME)
SELECT PAYNUMBER,DEPOT,GRADE,ROSTER_ID, date, DAY,TURN_NO, TURN_TEXT,
BOOK_ON, BOOK_OFF, DURATION, DIA_ID,NOW()
FROM T1
INNER JOIN T2 ON T2.D_DATE = T1.DATE
AND T2.LINE_POSITION = T1.ROWNUMBER
ORDER BY D_DATE,
LINE_POSITION ASC$BODY$;
ALTER FUNCTION api."post_publish_Roster"()
OWNER TO postgres;
GRANT EXECUTE ON FUNCTION api."post_publish_Roster"() TO PUBLIC;
GRANT EXECUTE ON FUNCTION api."post_publish_Roster"() TO postgres;
GRANT EXECUTE ON FUNCTION api."post_publish_Roster"() TO readonlyrole;
GRANT EXECUTE ON FUNCTION api."post_publish_Roster"() TO readwriterole;
`
It throws this error
[image: pgAdmin.png]
Re: Tempory table is not getting created inside Function in postgres.
Hi Tom, The same query is executed outside the function its working properly means just the qurey from drop temp table to insert but when i keep the query inside a function its through me the error an after temp ra how creation only i am using the select statment still its not exectuing the create statment or not storing the temp table. On Tue, 28 Feb, 2023, 1:34 am Tom Lane, wrote: > nikhil raj writes: > > This is the Function I have created successfully but while executing it > > throughs an error temp table doesn't exist. > > You won't be able to do it like that in a SQL-language function, because > the whole function body is parsed and parse-analyzed in one go. So the > later query referencing ROSTER_TABLE fails because it's parsed before > the CREATE TABLE executes. (Improving that has been on the to-do list > for a couple of decades, so don't hold your breath...) I suggest putting > the whole thing, not just part of it, into plpgsql. > > regards, tom lane >
Re: Tempory table is not getting created inside Function in postgres.
HI All, This for the help plpgsql and rest everything just move under declare and now everything works fine. Thanks for the help. On Tue, Feb 28, 2023 at 2:10 AM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Feb 27, 2023 at 1:17 PM nikhil raj wrote: > >> >> The same query is executed outside the function its working properly >> means just the qurey from drop temp table to insert but when i keep the >> query inside a function its through me the error an after temp ra how >> creation only i am using the select statment still its not exectuing the >> create statment or not storing the temp table. >> >>> >>> > What Tom said is that in the following change 'sql' to plpgsql and move > on. Your function is not capable of being executed in an SQL language > function. > > CREATE OR REPLACE FUNCTION api."post_publish_Roster"( > ) > RETURNS void > LANGUAGE 'sql' > COST 100 > VOLATILE PARALLEL UNSAFE > AS $BODY$ > > David J. > >
Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables.
Hi Adrian, Thanks for the quick response. I've already performed a vacuum, reindex, and analyze on the entire database, but the issue persists. As you can see from the execution plan, the time difference in PostgreSQL 16 is still significantly higher, even after all maintenance activities have been completed. It seems there might be a bug in PostgreSQL 16 where the performance of queries on *information_schema* tables is degraded. As both the tables are postgres system tables https://explain.depesz.com/s/bdO6b :-PG13 <https://explain.depesz.com/s/bdO6b> https://explain.depesz.com/s/bpAU :- PG16 <https://explain.depesz.com/s/bpAU> On Tue 27 Aug, 2024, 3:40 AM Adrian Klaver, wrote: > On 8/26/24 14:49, nikhil raj wrote: > > Hi All, > > > > I've encountered a noticeable difference in execution time and query > > execution plan row counts between PostgreSQL 13 and PostgreSQL 16 when > > running a query on |information_schema| tables. Surprisingly, PostgreSQL > > 16 is performing slower than PostgreSQL 13. > > Did you run ANALYZE on the Postgres 16 instance? > > > *4PostgreSQL 13.14 (PostgreSQL 13.14 on x86_64-pc-linux-gnu, compiled by > > gcc 11.4.0, 64-bit)* > > Execution plan: PG13.14 Execution Plan > > <https://explain.dalibo.com/plan/ag1a62a9d47dg29d> > > > > *PostgreSQL 16.4 (PostgreSQL 16.4 on x86_64-pc-linux-gnu, compiled by > > gcc 11.4.0, 64-bit)* > > Execution plan: PG16.4 Execution Plan > > <https://explain.dalibo.com/plan/4c66fdfbf2hf9ed2> > > > Use: > > https://explain.depesz.com/ > > It is easier to follow it's output. > > > > > > > Has anyone else experienced similar behavior or could provide insights > > into why PostgreSQL 16 might be slower for this query? Any advice or > > suggestions for optimization would be greatly appreciated. > > Yes when ANALYZE was not run on a new instance. > > > > > Thank you! > > > > NOTE:- PFA the raw file of explain and analyze below. > > > > > > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > >
Roles and security
Hi I am Nikhil currently working as a MS SQL DBA . Currently my company is shifting from MS SQL to postgres check the roles of user I want to give user roles only select, insert, update, execute and create database to all users Permission The users should not have these permission drop database , delete table permission **(Example --like in MS SQL we have like DB_roles-- ddladmin,data writer,data reader ,etc like these do we have in postgres).*** Thanks
High CPU usage in postgres servers
Hi guys , when check the task manager I see these many postgres.exe are running. How to identify which pid is running for which process please any one can help me out. or Why these many postgres.exe are running on the server [image: Inline image 1] Thanks , Nikhil
