status of CURSORs after DISCONNECT

Hello, When an ESQL/C written process issues a EXEC SQL DISCONNECT [connection]; do the opened CURSOR(s) still survive? We run into the problem that the father process issues DISCONNECT before forking children, the forked child CONNECTs to the same server and database again and "thinks" it has

Re: Range contains element filter not using index of the element column

Thank you Jon and Tom! Both of those ideas seem to work. Do you think this is worth of a feature request? Would there be any use if btree index is used in these certain situations directly with @>? Thanks, Lauri

Re: jsonb_set() strictness considered harmful to data

On 11/27/19 9:35 PM, Michael Paquier wrote: > On Fri, Nov 15, 2019 at 09:45:59PM +0100, Pavel Stehule wrote: >> Maybe ERRCODE_NULL_VALUE_NOT_ALLOWED, and "NULL is not allowed", >> errdetail - a exception due setting "null_value_treatment" => >> raise_exception >> and maybe some errhint - "Maybe y

Re: ROLE VALID UNTIL timezone?

On 11/27/19 2:25 PM, Adrian Klaver wrote: On 11/27/19 2:19 PM, Ron wrote: Hi, In 9.6, does it default to UTC, the postgresql.conf timezone value (US/Eastern) value or to local system time? test_(postgres)# show timezone;   TimeZone  US/Pacific test_(postgres)# create role

Re: jsonb_set() strictness considered harmful to data

On Fri, Nov 15, 2019 at 09:45:59PM +0100, Pavel Stehule wrote: > Maybe ERRCODE_NULL_VALUE_NOT_ALLOWED, and "NULL is not allowed", > errdetail - a exception due setting "null_value_treatment" => > raise_exception > and maybe some errhint - "Maybe you would to use Jsonb NULL - "null"::jsonb" > > I d

Re: Install different directory issues

I'm not that knowledgeable of Redhat paths here is how i would start first find the daemon or systemd postgresql.service files. on debian these are located in /etc/init.d or /etc/systemdif the files exist modify them to the correct paths if they do not exist the install went very side-ways

Re: ROLE VALID UNTIL timezone?

On 11/27/19 2:19 PM, Ron wrote: Hi, In 9.6, does it default to UTC, the postgresql.conf timezone value (US/Eastern) value or to local system time? test_(postgres)# show timezone; TimeZone US/Pacific test_(postgres)# create role ts_test valid until '12/31/2020'; CREATE ROLE

ROLE VALID UNTIL timezone?

Hi, In 9.6, does it default to UTC, the postgresql.conf timezone value (US/Eastern) value or to local system time? -- Angular momentum makes the world go 'round.

Re: Install different directory issues

On 11/27/19 11:19 AM, Thomas Carter wrote: I installed using the Postgres Installer package available on the Postgres site. With the system setup, I had to install to /app. I choose to use the installer because yum wouldn’t allow for me to choose a different install destination. There is mor

Re: Install different directory issues

Hi Thomas did you make sure the search paths and the paths in the config files were updated to find postgresql files. I've seen this not get updated by the install scripts On Wed, Nov 27, 2019 at 2:20 PM Thomas Carter wrote: > I installed using the Postgres Installer package available on the

Re: Install different directory issues

I installed using the Postgres Installer package available on the Postgres site. With the system setup, I had to install to /app. I choose to use the installer because yum wouldn’t allow for me to choose a different install destination. I’m not sure if I would have the same issue right now if

Re: Install different directory issues

On 11/27/19 10:49 AM, Thomas Carter wrote: I installed Postgres to /app and now psql command not working under Postgres user along with not knowing how to start the service like with a traditional systemctl. Installed using source, package, other and from where? RHEL7 Postgres 10.11 Thanks

Install different directory issues

I installed Postgres to /app and now psql command not working under Postgres user along with not knowing how to start the service like with a traditional systemctl. RHEL7 Postgres 10.11 Thanks for any help! -- Tom Carter

Re: pg_restore with connection limit 0

> 27 нояб. 2019 г., в 18:14, Adrian Klaver > написал(а): > Why not use pg_hba.conf to allow only connection from superuser for duration? We considered this, but we don't have direct access to the linux server, only sql connection. The application run in a kubernetes, etc. The most simple ap

Re: pg_restore with connection limit 0

On 11/27/19 7:06 AM, Олег Самойлов wrote: Hi all. I have task to refresh a test database from a production database (with masking) on the fly. To make masking we use pg_restore --create with three stages restoration. And one of the problem is daemons writing concurrently in the time of the re

Re: Range contains element filter not using index of the element column

Lauri Kajan writes: > I have a table with a timestamp column that has a btree index. > I would like to do a query: > SELECT * FROM table WHERE ts <@ tsrange($1, $2, '(]'); > The index is not used and a seq scan is done instead. > To use the index correctly I have to do the query like this: > SELEC

pg_restore with connection limit 0

Hi all. I have task to refresh a test database from a production database (with masking) on the fly. To make masking we use pg_restore --create with three stages restoration. And one of the problem is daemons writing concurrently in the time of the restoration of a database. I need to block the

Re: Range contains element filter not using index of the element column

On 11/27/19 6:33 AM, Lauri Kajan wrote: > On Wed, Nov 27, 2019 at 1:05 PM Игорь Выскорко > wrote: > > Hi! > Do you use GIST index? > According to > https://www.postgresql.org/docs/12/rangetypes.html#RANGETYPES-INDEXING > <@ operator is supported

Re: Range contains element filter not using index of the element column

On Wed, Nov 27, 2019 at 1:05 PM Игорь Выскорко wrote: > Hi! > Do you use GIST index? > According to > https://www.postgresql.org/docs/12/rangetypes.html#RANGETYPES-INDEXING <@ > operator is supported: > > A GiST or SP-GiST index can accelerate queries involving these range > operators: =, &&, <@,

Re: Range contains element filter not using index of the element column

27.11.2019, 16:32, "Lauri Kajan" : > Hi all, > I'm wondering if there are anything to do to utilize a index when doing a > range contains element  query. I have tested this with 9.6 and 12.0. > > I have a table with a timestamp column that has a btree index. > I would like to do a query: > SELE

Re: Weird seqscan node plan

27.11.2019, 15:42, "Andrei Zhidenkov" : > 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. > Yes, you are right in common: Index scan is not a

RE: Re: Postgres Full Text Search Jsonb Array column does not search for first row

Hello, thanks for helping!explain (analyze, BUFFERS)SELECT * FROM "cp"."Repro" x where cp.make_tsvector(x) @@ 'fir:*'::tsqueryoutputs this query plan:Bitmap Heap Scan on "Repro" x  (cost=12.00..16.26 rows=1 width=72) (actual time=0.007..0.007 rows=0 loops=1)  Recheck Cond: (cp.make_tsvector(x.*) @@

Range contains element filter not using index of the element column

Hi all, I'm wondering if there are anything to do to utilize a index when doing a range contains element query. I have tested this with 9.6 and 12.0. I have a table with a timestamp column that has a btree index. I would like to do a query: SELECT * FROM table WHERE ts <@ tsrange($1, $2, '(]'); T

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 num