Re: psql variables in the DO command

Hi 2018-03-05 14:13 GMT+01:00 Pavel Luzanov : > Hello, > > I can't use psql variable in the DO command. Is it intentional behavior? > yes. psql variables living on client side, and are not accessible from server side . DO command is executed on server side. you can copy psql variables to GUC va

Re: psql variables in the DO command

2018-03-05 14:52 GMT+01:00 Pavel Luzanov : > On 05.03.2018 16:42, Pavel Stehule wrote: > > > I can't use psql variable in the DO command. Is it intentional behavior? >> > > yes. psql variables living on client side, and are not accessible from > server side . DO co

Re: psql variables in the DO command

2018-03-05 15:02 GMT+01:00 Pavel Luzanov : > On 05.03.2018 16:56, Pavel Stehule wrote: > > >> I can't use psql variable in the DO command. Is it intentional behavior? >>> >> >> yes. psql variables living on client side, and are not accessible from >>

Re: psql variables in the DO command

2018-03-05 16:19 GMT+01:00 Pavel Luzanov : > On 05.03.2018 18:01, Pavel Stehule wrote: > > > It is most correct when you thinking about it. > > 1. :xx is out of SQL syntax, so can by safely used. There is not risk of > unwanted usage. > > But there is absence of wa

Re: psql variables in the DO command

2018-03-06 10:17 GMT+01:00 Pavel Luzanov : > On 05.03.2018 18:35, Pavel Stehule wrote: > > I am slowly working on prototype. The work is simple, when variables are > just scalars. But it is much harder, when we allow composite variables. > When prototype will be done, I invite

Re: plpgsql function-parsing

Hi 2018-03-06 16:51 GMT+01:00 chris : > Hi, > > I am trying to create a function that gets passed a statement as a string > and then I need to change the table_name within the string by adding a > "_cdc" to it, then execute the statement > > ex: > > string passed could be GRANT all ON authors TO

Re: Troubleshooting a segfault and instance crash

Hi 2018-03-08 18:40 GMT+01:00 Blair Boadway : > Hello, > > > > We’re seeing an occasional segfault on a particular database > > > > Mar 7 14:46:35 pgprod2 kernel:postgres[29351]: segfault at 0 ip > 00302f32868a sp 7ffcf1547498 error 4 in libc-2.12.so[302f20+ > 18a000] > > Mar 7 14:4

Re: Troubleshooting a segfault and instance crash

_parameter = off > > pgaudit.log_catalog = off > > pgaudit.log_statement_once = on > > pgaudit.log_level = log > > > > > > select * from information_schema.role_table_grants where grantee = > 'auditor'; > > (0 rows) > > > > > > thanks, Blair

Re: Prompt for parameter value in psql

Hi 2018-03-16 16:12 GMT+01:00 Tiffany Thang : > Hi, > Would it be possible to prompt for a user input in psql like in Oracle > sqlplus? > > In oracle, we use the & sign, for example, > select * from emp where empid=&empidvalue; > > https://www.postgresql.org/docs/current/static/app-psql.html see

Re: ora2pg and invalid command \N

2018-03-16 18:12 GMT+01:00 Charlin Barak : > Hi, > I'm using ora2pg to migrate our Oracle database to Postgres. I was able to > generate the data file using TYPE=COPY but when I attempted to load the > file via psql, I got lots of "invalid command \N" errors. The resolution on > the internet was n

Re: Is there a way to get the name of the calling function in pgplsql?

Hi 2018-03-21 8:24 GMT+01:00 Thiemo Kellner : > Hi all > > In a function I would like to log the caller. Is there a way to get its > name in pgplsql? > you can read it from stack https://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-CALL-STACK Regards Pavel

Re: Is there a way to get the name of the calling function in pgplsql?

Pavel > > Zitat von Pavel Stehule : > > https://www.postgresql.org/docs/current/static/plpgsql-contr >> ol-structures.html#PLPGSQL-CALL-STACK >> > > -- > Öffentlicher PGP-Schlüssel: http://pgp.mit

Re: Troubleshooting a segfault and instance crash

2018-03-25 0:41 GMT+01:00 Blair Boadway : > Thanks for the tip. We are using RHEL 6.9 and definitely up to date on > glibc (2.12-1.209.el6_9.2). We also have the same versions on a very > similar system with no segfault. > > > > My colleague got a better backtrace that shows another extension >

Re: psql variable to plpgsql?

Hi 2018-04-11 8:51 GMT+02:00 Thiemo Kellner : > Hi all > > Is there a way to pass the value of a psql variable into function code? I > create a schema with help of psql variable > >\set SCHEMA_NAME LOGGER >create > schema :SCHEMA_NAME; > > I would like to create a function that has t

Re: psql variable to plpgsql?

2018-04-11 11:36 GMT+02:00 Thiemo Kellner, NHC Barhufpflege < thiemo.kell...@gelassene-pferde.biz>: > Zitat von Pavel Stehule : > > No, there is not possible to read/write client side variables from server >> side. >> > > I did not mean that the variable be r

Re: Query function arg data types ONLY (no arg names)

Hi 2018-04-28 18:52 GMT+02:00 Jeremy Finzel : > It appears that neither pg_get_function_arguments > nor pg_get_function_identity_arguments could be used for this. I want to > get function argument data types from the catalog by ordinal position, > without the argument name. > > For example, I wa

Re: How to find the hits on the databases and tables in Postgres

2018-05-05 13:03 GMT+02:00 PT : > On Fri, 4 May 2018 17:14:39 +0530 > 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 r

Re: Enhancement to psql command, feedback.

2018-05-09 9:59 GMT+02:00 John McKown : > I just wanted to throw this out to the users before I made a complete fool > of myself by formally requesting it. But I would like what I hope would be > a minor change (enhancement) to the psql command. If you look on this page, > https://wiki.postgresql.

Re: How do I get the SQL statement in a trigger?

Hi 2018-05-10 12:23 GMT+02:00 a <372660...@qq.com>: > Hi I would like to write a trigger that recorded every sql statement under > the effected entry. So if it is possible to retrieve the sql statement > within a trigger?? > You can read a tom command from pg_stat_activity table postgres=# sel

Re: How do I get the SQL statement in a trigger?

Regards Pavel > > > -- Original message ------ > *From:* "Pavel Stehule"; > *Sendtime:* Thursday, May 10, 2018 6:38 PM > *To:* "a"<372660...@qq.com>; > *Cc:* "pgsql-general"; > *Subject:* Re: How do I get the SQL statement in a tri

Re: How do I get the SQL statement in a trigger?

l.org/docs/current/static/functions-info.html sure - it is better - forgot it Regards Pavel > > > > > > > > > > On 10/05/18 11:44, Pavel Stehule wrote: > >> >> >> 2018-05-10 12:42 GMT+02:00 a <372660...@qq.com <mailto:372660...@qq.com >&

Re: Function to set up variable inside it

2018-05-15 14:28 GMT+02:00 Łukasz Jarych : > Hi Guys, > > I am using postgres 10.3 (or 4?). > IT is possible to set up variable inside function? > I don't understand to the question. What do you think? Regards Pavel > > Best, > Jacek >

Re: array_agg to array

Hi 2018-05-16 8:14 GMT+02:00 Philipp Kraus : > Hello, > > I have got a function with a reg expr to split chemical formulas e.g. H2O > -> H2 O. > > CREATE OR REPLACE FUNCTION daimon.text2sumformula(text) RETURNS text[] AS > $$ > select array_agg(i::text) as e from ( select unnest( regexp_match

Re: Function to set up variable inside it

Hi 2018-05-16 6:52 GMT+02:00 Łukasz Jarych : > Hi Guys, > > thank you for your help ! > > Hmm yes something like this. > > I was wondering if is possible to pass variable into function: > > CREATE FUNCTION change_trigger() RETURNS trigger AS $$ > > BEGIN > > IF TG_OP

Re: Importing data from CSV into a table with array and composite types

Hi 2018-05-18 10:37 GMT+02:00 a <372660...@qq.com>: > Hi: > > I would like to import data from a csv table. But the table that is going > to be inserted is constructed with arrays and composite types, also with > array of composite. > > I have tried many ways of inserting but fail. Can anyone hel

Re: Importing data from CSV into a table with array and composite types

select array_agg(a) from f, unnest(pv) where f.c = 1000; ┌───┐ │ array_agg │ ╞═══╡ │ {10,30} │ └───┘ (1 row) > Thank you! > > Shore > > > -- Original message -- > *From:* "Pavel Stehule"; > *Sendtime:* Friday, Ma

Re: Whither 1:1?

2018-06-01 18:52 GMT+02:00 Guyren Howe : > It’s come to my attention that what seems an obvious and useful database > design pattern — 1:1 relations between tables by having a shared primary > key — is hardly discussed or used. > > It would seem to be a very simple pattern, and useful to avoid sto

Re: Microsoft buys GitHub, is this a threat to open-source

2018-06-04 20:34 GMT+02:00 Michael Nolan : > > > On Mon, Jun 4, 2018 at 12:15 PM, Tom Lane wrote: > >> Michael Nolan writes: >> > Microsoft has bought GitHub for $7.5 billion, is this a threat to the >> open >> > source community? >> >> A fair question, but one that seems entirely off-topic for

Re: limit and query planner

2018-06-05 20:24 GMT+02:00 armand pirvu : > All > > Please see below > > explain analyze select * from sp_i2birst_reg_staging_test where evt_id = > 'ACSF17' > and status=0 limit 10; > QUERY PLAN > > ---

Re: Slow planning time for simple query

2018-06-06 18:59 GMT+02:00 Jeremy Finzel : > Hello - > > We have an odd scenario on one of our OLTP systems, which behaves the same > way on a streamer, of a 700-1000ms planning time for a query like this: > > SELECT * > FROM table1 > WHERE source_id IN (SELECT id FROM table2 WHERE customer_id =

Re: json_populate_recordset

2018-06-07 18:51 GMT+02:00 Adrian Klaver : > From here: > > https://www.postgresql.org/docs/10/static/functions-json.html > > select * from json_populate_record(null::myrowtype, '{"a": 1, "b": ["2", > "a b"], "c": {"d": 4, "e": "a b c"}}') > > What is the null::myrowtype doing? > this function us

Re: Database name with semicolon

Hi 2018-06-27 15:22 GMT+02:00 Joby John : > Hi, > > > > We have a windows application which communicate to Postgres database via > PostgreSQL ODBC drivers for windows. > > One of our customers has a database with semicolon in its name (e.g.: “db; > name”) and our application is failing to establi

Re: EXTERNAL: Re: Database name with semicolon

bname = '"ssss; sss"' > > Regards, > > Joby John > > > > *From:* Pavel Stehule [mailto:pavel.steh...@gmail.com] > *Sent:* 27 June 2018 15:26 > *To:* Joby John > *Cc:* pgsql-gene...@postgresql.org > *Subject:* EXTERNAL: Re: Database name wit

Re: Database name with semicolon

2018-06-28 12:10 GMT+02:00 joby.john@nccgroup.trust < Joby.John@nccgroup.trust>: > > See if something like below works: > > {db; name} > > > > Hi, > > Thanks for the responses. > > I tried putting curly brackets around the value of database like you > mentioned. > {db; name} > But the server was t

Re: How to remove elements from array .

Hi 2018-07-06 9:49 GMT+02:00 Brahmam Eswar : > Hi , > > I tried to use array_remove to remove elements from an array but it's > saying function doesn't exist . I'm able to use other array functions. > > 1) Capture the results with multiple columns into array . > 2) if ay results exist then loop

Re: How to remove elements from array .

Hi 2018-07-06 10:19 GMT+02:00 Charles Clavadetscher : > Hi > > > > *From:* Brahmam Eswar [mailto:brahmam1...@gmail.com] > *Sent:* Freitag, 6. Juli 2018 09:50 > *To:* pgsql-general ; > pgsql-hack...@postgresql.org > *Subject:* How to remove elements from array . > > > > Hi , > > > > I tried to use

Re: How to remove elements from array .

at > "https://"; . > > Can we iterate over unnest records? > sure - it is relation like any other. Can you send test case? > On Fri, Jul 6, 2018 at 1:56 PM, Pavel Stehule > wrote: > >> Hi >> >> 2018-07-06 10:19 GMT+02:00 Charles Clavadetscher &

Re: How to set array element to null value

2018-07-09 11:58 GMT+02:00 Brahmam Eswar : > I'm trying to reset array element to null. but 3rd line of below snippet > is giving the compilation error. > > > FOR indx_1 IN array_lower(X, 1)..array_upper(X, 1) LOOP > IF X[indx_1].REFERENCE_VALUE = 'ABC' THEN > X[indx_1].REFERENCE_VALUE:=''; > END

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

2018-07-16 13:52 GMT+02:00 Dmitry Igrishin : > > > пн, 16 июл. 2018 г. в 14:26, : > >> We – and the majority of our customers - are mainly focused on Windows. >> We use pgadmin iii and our own assistants. pgadmin iv ist still too slow on >> Windows compared to pgadmin iii. That is one reason why w

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

2018-07-16 14:28 GMT+02:00 Dmitry Igrishin : > > > пн, 16 июл. 2018 г. в 15:01, Pavel Stehule : > >> >> >> 2018-07-16 13:52 GMT+02:00 Dmitry Igrishin : >> >>> >>> >>> пн, 16 июл. 2018 г. в 14:26, : >>> >>>>

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

2018-07-16 15:22 GMT+02:00 Dmitry Igrishin : > > > пн, 16 июл. 2018 г. в 16:00, Pavel Stehule : > >> >> >> 2018-07-16 14:28 GMT+02:00 Dmitry Igrishin : >> >>> >>> >>> пн, 16 июл. 2018 г. в 15:01, Pavel Stehule

Re: Return Multiple Rows from Store Function

Hi 2018-07-27 11:24 GMT+02:00 Brahmam Eswar : > Hi , > > Returning multiple rows from store functions using "RETURNS TABLE" and > RETURN QUERY. The results set may have more than 50k records. Does it give > any performance issues related to memory? if yes how to avoid it > > This command uses t

Re: check_function_bodies not doing much

Hi 2018-08-07 21:17 GMT+02:00 Marcelo Lacerda : > I was trying to get postgres to warn me that I'm referencing a table that > it doesn't exists inside a function so I was told on the IRC to check the > setting "check_function_bodies", however when I use it in a plpgsql > function it doesn't actua

Re: check_function_bodies not doing much

2018-08-08 0:02 GMT+02:00 Marcelo Lacerda : > That's a whole different nightmare that I'm expecting. > > > "Yep I double-checked all my functions to see if any would break if I > change this field mytable.a into 2 fields mytable.a1 and mytable.a2 and > everything is ok." > > *1 month later* > >

Re: User documentation vs Official Docs

Hi 2018-08-10 21:00 GMT+02:00 Bruce Momjian : > On Fri, Jul 20, 2018 at 05:31:40PM -0700, Adrian Klaver wrote: > > JD sit down, I am going to agree with you:) The documentation as it > stands > > is very good, though it requires some fore knowledge to successfully > > navigate. On pages with a lo

Re: Returning Vector of Pairs with a PostgreSQL C Extension Function

2018-08-27 14:40 GMT+02:00 TalGloz : > It looks like changing the > > elements[0] = CStringGetDatum(localT1.c_str()); > elements[1] = CStringGetDatum(localT2.c_str()); > > to: > > elements[0] = PointerGetDatum(cstring_to_text(localT1.c_str())); > elements[1] = PointerGetDatum(cstring_to_text(local

Re: using a plpgsql function argument as a table column.

Hi 2018-08-29 7:09 GMT+02:00 Shaun Savage : > I have a table with many years as columns. y1976, y2077, .. , y2019,y2020 > I want to dynamically return a column from a function. > no - it is not possible - the functions should to return exact same set of columns. Teoretically you can use SETOF R

Re: Bitmap Heap Scan and Bitmap Index Scan

Hi so 15. 9. 2018 v 9:39 odesílatel Arup Rakshit napsal: > Here is a explain plan of a very simple query: > > aruprakshit=# explain analyze select first_name, last_name from users > where lower(state) = 'colorado'; > QUERY PLAN > >

Re: array must have even number of elements

Hi čt 20. 9. 2018 v 19:55 odesílatel Susan Hurst < susan.hu...@brookhurstdata.com> napsal: > > Why must an array have an even number of elements? I need to use a > trigger function on any table, some of which may have an odd number of > columns that I want to cleanse before inserting/updating. >

Re: array must have even number of elements

; with pleasure Pavel > Sue > > > > --- > > Susan E Hurst > Principal Consultant > Brookhurst Data LLC > Email: susan.hu...@brookhurstdata.com > Mobile: 314-486-3261 > > On 2018-09-20 13:04, Pavel Stehule wrote: &g

Re: How to improve sql query to achieve the better plan

Hi ne 30. 9. 2018 v 18:23 odesílatel Arup Rakshit napsal: > I have the below query which is taking 1873 ms. How can I improve this? > > explain analyze select > sum(coalesce(price_cents, 0)::bigint * coalesce(quantity, 0) * (1 - > coalesce(workitems.discount, 0)/ 100)) as total_budget_cents, > s

Re: How to improve sql query to achieve the better plan

orkitem_id" btree (parent_workitem_id) > "index_workitems_on_project_id" btree (project_id) > "index_workitems_on_standard_workitem_id" btree (standard_workitem_id) > "index_workitems_on_workitem_category_id" btree (workitem_category_id) >

Re: How can I get and handle the status of sql statements that run in plpgsql ?

Hi st 3. 10. 2018 v 18:26 odesílatel David Gauthier napsal: > I found "no_data" here... > https://www.postgresql.org/docs/10/static/errcodes-appendix.html > > update blah, blah... > if(no_data) then > raise exception "update failed to update anything"; > end if > UPDATE IF NOT FOUND THEN

Re: Why the index is not used ?

so 6. 10. 2018 v 11:57 odesílatel ROS Didier napsal: > Hi > > I would like to submit the following problem to the PostgreSQL community. > In my company, we have data encryption needs. > So I decided to use the following procedure : > > > > (1)Creating a table with a bytea type column to store

Re: Optimizing Postgresql ILIKE while query

Hi po 22. 10. 2018 v 7:57 odesílatel aman gupta napsal: > Hi Team, > > Greetings for the day!! > > Platform: > > PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 > 20150623 (Red Hat 4.8.5-11), 64-bit > > > Issue: > > > We have the base table which contains 22M records and we

Re: CRecordset::Open postgresql procedure call don't work

Hi Dne po 15. 7. 2019 13:40 uživatel jeanclaude marzin < jeanclaude.mar...@sfr.fr> napsal: > ‌Hi > I migrate from MSSQL to postgresql 11. I translate MSSQL stored procedure > to Postgresql one : > > CREATE PROCEDURE procacp () > LANGUAGE SQL > AS $$ > SELECT tabjdbexploit.jdbeid, tabjdbexploit.jd

Re: How to check if a field exists in NEW in trigger

po 5. 8. 2019 v 7:55 odesílatel Thomas Kellerer napsal: > Igal @ Lucee.org schrieb am 05.08.2019 um 00:52: > > I have the following statement in a trigger: > > > > new.email = lower(new.email); > > > > When I try to update a record without setting the email column however, > I get an error: >

Re: How to check if a field exists in NEW in trigger

po 5. 8. 2019 v 10:10 odesílatel Thomas Kellerer napsal: > Pavel Stehule schrieb am 05.08.2019 um 08:19: > >>> I have seen some hacks suggesting TRY/CATCH or converting to a JSON > >>> and checking if the field exists, but I would think that there's a > >>

Re: A question about trigger fucntion syntax

Hi > BTW, here is what I Ave tried. > > CREATE OR REPLACE FUNCTION check_permission() > RETURNS trigger AS > $BODY$ > BEGIN > SELECT > permit > FROM > permitted_work > WHERE > NEW.employee_key = OLD.employee_key > A

Re: "storing" a calculated value in plsql function ?

this adjustment > factor, and > stores it in a PLSQL variable< and if I do that, can I reference this > stored value > in the select? > you can use custom configuration values like global variables https://wiki.postgresql.org/wiki/Variable_Design#PostgreSQL_User-Defined_GUCS

Re: SQL equivalint of #incude directive ?

pá 30. 8. 2019 v 15:49 odesílatel stan napsal: > > I thought this would be common. But a quick Google only revealed what look > to be > workarounds. > > I am defining a bunch of functions, and I would prefer to store them in a > separate file, which then gets "source" by the main DB init file. >

Re: echo work alike in SQL

Hi so 31. 8. 2019 v 17:46 odesílatel stan napsal: > Just started using the RAISE functionality to help in debugging. > > Tried to add it to an SQL script, only to realize it is a PLPGSQL > extension. > > Is there a way to get a string to appear in the output f an SQL script? > > Can I do some so

Re: pgmodeler ?

Hi ne 1. 9. 2019 v 12:35 odesílatel stan napsal: > On Sun, Sep 01, 2019 at 06:30:23AM -0400, stan wrote: > > I apologize, as this is a bit off topic for this list. > > > > pgmodeler got mention in a thread that I started a day or two ago, and > it looks > > like it might be very useful > > > >

Re: ERROR: too many dynamic shared memory segments

Hi st 11. 9. 2019 v 9:48 odesílatel Nicola Contu napsal: > Hello, > We are running postgres 11.5 and in the last two weeks we did : > > - upgrade of postgres to 11.5 from 11.4 > - increased shared_buffer to 1/3 of the memory > - increased effective_cache_size = 160GB from 120 > - increased check

Re: PG SQL and LIKE clause

Hi pá 13. 9. 2019 v 7:29 odesílatel Matthias Apitz napsal: > > Hello, > > We're porting a huge Library Management System, written using all kind > of languages one can think of (C, C++, ESQL/C, Perl, Java, ...) on Linux > from the DBS Sybase to PG, millions of lines of code, which works also > w

Re: PG SQL and LIKE clause

pá 13. 9. 2019 v 8:49 odesílatel Matthias Apitz napsal: > El día Friday, September 13, 2019 a las 07:33:10AM +0200, Pavel Stehule > escribió: > > > > We got to know that in CHAR columns with trailing blanks a > > > > > > SELECT ... FROM ... WHERE name LIKE

Re: JSONB maximal length ?

so 28. 9. 2019 v 18:12 odesílatel ROS Didier napsal: > Hi > >By inserting data in a JSONB type column I got the following error > message: > > *>> * > > *ERROR: string too long to represent as jsonb string* > > *DETAIL: Due to an implementation restriction, jsonb strings cannot > exceed 268

Re: JSONB maximal length ?

so 28. 9. 2019 v 18:35 odesílatel ROS Didier napsal: > Hi Pavel > > > >I don’t know what is the value 255MB ? > > all I know is that I want to insert large pdf documents. I am prevented > because of this limit. > It's pretty long pdf :) Maybe you are hit some bug. Postgres JSON, JSONB, XM

Re: Performance on JSONB select

Hi so 5. 10. 2019 v 13:34 odesílatel PegoraroF10 napsal: > I think I solved my problem. I didn´t know it was possible but I´ve created > an almost dynamic index, because it´ll index for status and corresponding > datevalue of an object with that status value. > you created multicolumn functiona

Re: psql \copy hanging

út 8. 10. 2019 v 9:06 odesílatel Arnaud L. napsal: > Le 07/10/2019 à 16:36, Adrian Klaver a écrit : > > So you are saying that you have not run the problematic line by itself? > > It hung during last night's run. > > I had modified my batch script to run the \copy commands separately, > i.e. it n

Re: jsonb_set() strictness considered harmful to data

Hi > What I am talking about is that jsonb_set(..., ..., NULL) returns SQL NULL. > > postgres=# \pset null '(null)' > Null display is "(null)". > postgres=# select jsonb_set('{"a":1,"b":2,"c":3}'::jsonb, '{a}', NULL); > jsonb_set > --- > (null) > (1 row) > > This behaviour is basically gi

Re: jsonb_set() strictness considered harmful to data

so 19. 10. 2019 v 7:41 odesílatel David G. Johnston < david.g.johns...@gmail.com> napsal: > On Friday, October 18, 2019, Pavel Stehule > wrote: > > >> Probably there will be some applications that needs NULL result in >> situations when value was not changed or whe

Re: CPU SPIKE

Hi po 21. 10. 2019 v 7:14 odesílatel Pawan Sharma napsal: > Hi All, > > Having real high CPU issue (95-98%), with SELECT statements and select > queries contains multiple AND operator, is it will cause any CPU Spike..??? > > apps team is using sub-partition, PG11, CPU:24, Mem: 16GB > > ```test=#

Re: A question about building pg-libphonenumber

Hi út 22. 10. 2019 v 14:15 odesílatel stan napsal: > OK, this seems a stupid question, but I do not see the answer. > > > I downloaded pg-libphonenumber, and am going to try to build it for PG > version 11. Looks like the last build was against version 9.6. > > It builds fine, the issue comes in

Re: A question about building pg-libphonenumber

út 22. 10. 2019 v 14:37 odesílatel stan napsal: > On Tue, Oct 22, 2019 at 02:19:15PM +0200, Pavel Stehule wrote: > > Hi > > > > ??t 22. 10. 2019 v 14:15 odes??latel stan napsal: > > > > > OK, this seems a stupid question, but I do not see the answe

Re: PostgreSQL && data types in ESQL/C

Hi > The above is a bit by try and error. Is there any good manual which > describes the ESQL/C details for PostgreSQL. > I newer used ESQL/C so I cannot to evaluate a quality of this part of this doc. I expect so there will not be any other. https://www.postgresql.org/docs/12/ecpg.html Regard

Re: PostGreSQL Replication and question on maintenance

pá 15. 11. 2019 v 6:26 odesílatel github kran napsal: > > Hello postGreSQL Community , >> >> >> >> Hope everyone is doing great !!. >> >> >> *Background* >> >> We use PostgreSQL Version 10.6 version and heavily use PostgreSQL for our >> day to day activities to write and read data. We have 2 clus

Re: jsonb_set() strictness considered harmful to data

Hi > For release 13+, I have given some more thought to what should be done. > I think the bar for altering the behaviour of a function should be > rather higher than we have in the present case, and the longer the > function has been sanctioned by time the higher the bar should be. > However, I

Re: jsonb_set() strictness considered harmful to data

pá 15. 11. 2019 v 21:01 odesílatel Andrew Dunstan < andrew.duns...@2ndquadrant.com> napsal: > > On 11/15/19 2:14 PM, Pavel Stehule wrote: > > Hi > > > > > > > > For release 13+, I have given some more thought to what should be > > done. &g

Re: Function performance degrades after repeated execution

so 16. 11. 2019 v 16:06 odesílatel Dave Roberge napsal: > Hi, > > We've been troubleshooting a slow running function in our postgres > database. I've been able to boil it down to the simplest function possible. > It looks like this: > > FOR rec IN select 1 as matchval FROM table1 t1, table2 t2 >

Re: Function performance degrades after repeated execution

so 16. 11. 2019 v 16:46 odesílatel Ron napsal: > On 11/16/19 8:22 AM, Dave Roberge wrote: > > Hi, > > > > We've been troubleshooting a slow running function in our postgres > database. I've been able to boil it down to the simplest function possible. > It looks like this: > > > > FOR rec IN selec

Re: REINDEX VERBOSE unknown option

so 16. 11. 2019 v 18:43 odesílatel Josef Šimánek napsal: > Ahh, I just tried to do the same with reindexdb cli tool and the > actual syntax is REINDEX (VERBOSE) TABLE sales; Sorry for unnecessary > question. Anyway maybe we can add this to documentation as a example. I can > prepare patch for thi

Re: PostGreSQL Replication and question on maintenance

oso avísanos a **s...@entel.cl > ** . * > Any reply on this please ?. > > On Fri, Nov 15, 2019 at 9:10 AM github kran wrote: > >> >> >> On Thu, Nov 14, 2019 at 11:42 PM Pavel Stehule >> wrote: >> >>> these numbers looks crazy high - how m

Re: How to get column and identifier names in UPPERCASE in postgres?

Hi st 20. 11. 2019 v 14:11 odesílatel Amine Tengilimoglu < aminetengilimo...@gmail.com> napsal: > Hi all; > > I want to get the column and other identifier names in UPPERCASE > form rather than a lowercase one without changing application code like > qouting the identifiers. Do you know an

Re: Return Table in StoredProceure/Function

Hi st 20. 11. 2019 v 16:01 odesílatel İlyas Derse napsal: > > How can I return table in Stored Procedure ? I can do it in function but I > have inout parameters.So I can not create in function. What can I do this > case ? > > I guess,It should be like for function : > > CREATE or REPLACE FUNCTIO

Re: Return Table in StoredProceure/Function

čt 21. 11. 2019 v 7:34 odesílatel Tony Shelver napsal: > Well then SQL Server breaks that rule big time :) > Most people coming from a SQL Server background expect procedures to > return a result set that can be queried, and in-out or out parameters to > return variables for further information.

Re: Adding LIMIT changes PostgreSQL plan from good to a bad one

čt 21. 11. 2019 v 17:19 odesílatel Michael Korbakov napsal: > Hi everybody. > > I stumbled upon a weird problem with the query planner. I have a query > on a typical EAV schema: > > SELECT contacts.id > FROM contacts > LEFT OUTER JOIN (SELECT DISTINCT contacts_values.company_id AS > company_i

Re: Adding LIMIT changes PostgreSQL plan from good to a bad one

čt 21. 11. 2019 v 22:04 odesílatel Michael Korbakov napsal: > On November 21, 2019 at 19:14:33, Pavel Stehule (pavel.steh...@gmail.com) > wrote: > > > > čt 21. 11. 2019 v 17:19 odesílatel Michael Korbakov > napsal: > >> Hi everybody. >> >> I stumbled u

Re: Slow planing...

pá 6. 12. 2019 v 15:12 odesílatel Mladen Marinović < mladen.marino...@kset.org> napsal: > After a couple of hours of trying different stuff, set enable_mergejoin = > off made the planning time look better: Planning time: 0.322 ms > Any ideas why this helps? > pls, can you try reindex all related

Re: Query with correlated join having slow performance

po 9. 12. 2019 v 21:05 odesílatel saket bansal napsal: > Thank you Michael. I re-wrote it and it does perform well. Modified query > at: > > > https://github.com/bansalsaket/PG_correlated_subquery_slowness/blob/master/Modified%20query%20-%20performs%20faster.txt > > Our app team is checking with

Re: How to Change collate & ctype for an existing database?

Hi pá 13. 12. 2019 v 9:57 odesílatel Daulat Ram napsal: > Hi, > > How we can change the Collate & Ctype from “c” to “C.UTF-8” on > existing production database. > > SELECT datcollate FROM pg_database WHERE datname='wwkidbt'; > > datcollate > > > > C > > postgres=# select versio

Re: Syntax question about returning value from an insert

Hi st 25. 12. 2019 v 16:26 odesílatel stan napsal: > I am writing a trigger/function to make certain a default item, and its key > exist when an insert is called. EG > > The trigger gets called on insert to T1 If column c1 is NULL in the NEW > structure, I need to check table t2 to get the key a

Re: Not my day :-( Another syntax error

čt 26. 12. 2019 v 18:50 odesílatel stan napsal: > > On Thu, Dec 26, 2019 at 10:39:54AM -0700, David G. Johnston wrote: > > You should probably send that reply again using reply-to-all. > > > > Dave > > > > > > On Thu, Dec 26, 2019 at 10:38 AM stan wrote: > > > > > On Thu, Dec 26, 2019 at 10:26:4

Re: jsonb_set() strictness considered harmful to data

Hi po 6. 1. 2020 v 22:34 odesílatel Andrew Dunstan < andrew.duns...@2ndquadrant.com> napsal: > On Thu, Nov 28, 2019 at 2:15 PM Andrew Dunstan > wrote: > > > > > > On 11/27/19 9:35 PM, Michael Paquier wrote: > > > On Fri, Nov 15, 2019 at 09:45:59PM +

Re: Is there a GoTo ?

Hi čt 16. 1. 2020 v 11:53 odesílatel İlyas Derse napsal: > In this function I have to GOTO to a label1, but GOTO keyword is not > working, can you please help me in getting the way from which I am able to > jump from a particular code to label. > Thanks... > no, plpgsql has not GOTO statement.

Re: performance of loading CSV data with COPY is 50 times faster than Perl::DBI

pá 31. 1. 2020 v 19:25 odesílatel Matthias Apitz napsal: > > Hello, > > Since ages, we transfer data between different DBS (Informix, Sybase, > Oracle, and now PostgreSQL) with our own written tool, based on > Perl::DBI which produces a CSV like export in a common way, i.e. an > export of Oracle

Re: Force Commit

st 5. 2. 2020 v 9:10 odesílatel İlyas Derse napsal: > I'm writing to you about Commit. I want to do force commit query even > if I have exception. > It's like : > > CREATE OR REPLACE PROCEDURE public."test"() > LANGUAGE 'plpgsql' > AS $BODY$ > DECLARE "a" integer ; > DECLARE "b" integer ; > B

Re: a proposal for a new functionality: "SELECT * [EXCEPT col1 [,col2]]

út 25. 2. 2020 v 22:14 odesílatel Tom Lane napsal: > Paul Jungwirth writes: > > Not that this is necessarily fatal, but you'd need to avoid parsing > > trouble with the other EXCEPT, e.g. > > SELECT 1 EXCEPT SELECT 1; > > Yeah, it doesn't sound like much consideration has been given to > that am

Re: Detecting which columns a query will modify in a function called by a trigger

po 2. 3. 2020 v 19:59 odesílatel stan napsal: > I need to implement a fairly fine grained security model. Probably a bit > finer that I can do with the standard ownership functionality. > > My thinking on this is to create a table that contains the users, and a > "permission bit" for each functio

Re: How to plpgsql scripting

st 25. 3. 2020 v 13:20 odesílatel Ekaterina Amez napsal: > Hi List, > > I'm used to make my own scripts in Oracle plsql, Sql Server tsql... but > I'm unable to make one simple script in Postgres. > > Objective version is 8.4 (I know, I know... it's a legacy server, I'm > planning upgrade this ser

  1   2   3   4   5   >