status of CURSORs after DISCONNECT

[Matthias Apitz]

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 to CLOSE the
CURSOR (perhaps a long standing bug in our application we port now from
Sybase(...) to PostgreSQL).

Thanks

matthias
-- 
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

"Glaube wenig, hinterfrage alles, denke selbst: Wie man Manipulationen 
durchschaut"
"Believe little, scrutinise all, think by your own: How see through 
manipulations"
ISBN-10: 386489218X

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

[Lauri Kajan]

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

[Andrew Dunstan]

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 you would to use Jsonb NULL - "null"::jsonb"
>>
>> I don't know, but in this case, the exception should be verbose. This is
>> "rich" function with lot of functionality
> @Andrew: This patch is waiting on input from you for a couple of days
> now.
>


Will get to this on Friday - tomorrow is Thanksgiving so I'm unlikely to
get to it then.


cheers


andrew

-- 
Andrew Dunstanhttps://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: ROLE VALID UNTIL timezone?

[Adrian Klaver]

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 ts_test valid until '12/31/2020';
CREATE ROLE

ts_test    | Cannot login   +| {}
     | Password valid until 2020-12-31 00:00:00-08  |

Best guess it operates like if specifying a value for a timestamptz field.


Decided to see if guess was correct:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/commands/user.c;h=aab5aa855d2851222dc7738c7575ad805f6bce94;hb=HEAD

Line 94

Datum   validUntil_datum;   /* same, as timestamptz Datum */


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: jsonb_set() strictness considered harmful to data

[Michael Paquier]

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 don't know, but in this case, the exception should be verbose. This is
> "rich" function with lot of functionality

@Andrew: This patch is waiting on input from you for a couple of days
now.
--
Michael


signature.asc
Description: PGP signature

Re: Install different directory issues

[Justin]

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  uninstall and and try again and read help files regarding the
command line switches to change the install path

If the the files are and paths are correct the something else went wrong.
either failed to register the daemon or initdb failed which will cause the
service to fail to start.

try to  start postgres from the command prompt.  it will throw errors
giving you an idea what the problem is

on debian logs are located in /var/log/postgresql/versionnumber/

On Wed, Nov 27, 2019 at 3:58 PM Thomas Carter  wrote:

> Hi Justin,
> I expect this is the case since the traditional install will have
> everything “fall” into the right place. How would I correct since I’ve
> installed to the /app directory?
>
> Thanks for your help.
>
> On Wed, Nov 27, 2019 at 2:26 PM Justin  wrote:
>
>> 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
>>> 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 I used rpm
>>> —prefix.
>>>
>>> Tom Carter
>>>
>>> > On Nov 27, 2019, at 2:00 PM, Adrian Klaver 
>>> wrote:
>>> >
>>> > 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 for any help!
>>> >> --
>>> >> Tom Carter
>>> >
>>> >
>>> > --
>>> > Adrian Klaver
>>> > adrian.kla...@aklaver.com
>>>
>>>
>>> --
> Tom Carter
>

Re: ROLE VALID UNTIL timezone?

[Adrian Klaver]

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

ts_test| Cannot login 
  +| {}
| Password valid until 2020-12-31 00:00:00-08 
 |


Best guess it operates like if specifying a value for a timestamptz field.

--
Adrian Klaver
adrian.kla...@aklaver.com

ROLE VALID UNTIL timezone?

[Ron]

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

[Adrian Klaver]

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 more then one installer, which one?

Do you have another instance of Postgres installed?




I’m not sure if I would have the same issue right now if I used rpm —prefix.

Tom Carter


On Nov 27, 2019, at 2:00 PM, Adrian Klaver  wrote:

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 for any help!
--
Tom Carter



--
Adrian Klaver
adrian.kla...@aklaver.com





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Install different directory issues

[Justin]

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 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 I used rpm
> —prefix.
>
> Tom Carter
>
> > On Nov 27, 2019, at 2:00 PM, Adrian Klaver 
> wrote:
> >
> > 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 for any help!
> >> --
> >> Tom Carter
> >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com
>
>
>

Re: Install different directory issues

[Thomas Carter]

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 I used rpm —prefix. 

Tom Carter

> On Nov 27, 2019, at 2:00 PM, Adrian Klaver  wrote:
> 
> 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 for any help!
>> -- 
>> Tom Carter
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com

Re: Install different directory issues

[Adrian Klaver]

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 for any help!
--
Tom Carter



--
Adrian Klaver
adrian.kla...@aklaver.com

Install different directory issues

[Thomas Carter]

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 approach to block concurrent access to the database, may be, is 
run all pg_restore inside single transaction. But this is impossible with 
--create option, but --create option is the only way to restore database 
settings. And this is impossible for us, because we need pg_restore in 3 stages.

It is inconvenient that the pg_restore has functionality for 3 stages, but 
don't has functionality to block database for time of this 3 stages.

Re: pg_restore with connection limit 0

[Adrian Klaver]

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 restoration of a database. I need to block them in time of the 
restoration. ALLOW_CONNECTIONS=true don't work in this case, because it blocks 
a superuser too. We use CONNECTION LIMIT 0, because this blocks almost all, 
except a superuser. But to prevent a race condition we must set this in the 
same query as CREATE DATABASE, the postgresql syntax allow this. Now this is 
rather complex procedure:

We get `pg_restore --create --list −−section=pre−data` and `pg_restore ---stage 
--list −−section=pre−data`, diff them to get difference.
Get `pg_restore --create --use-list=diff −−section=pre−data` to get sql.
Edit sql: remove ALTER DATABASE ... CONNECTION LIMIT ... if exist, add 
CONNECTION LIMIT 0 to the CREATE DATABASE.
Send sql to the postgresql.
And so on.

Will be convenient to add CONNECTION LIMIT 0 to the CREATE DATABASE just by 
option of pg_restore.
And will be even more straight approach do not use CONNECTION LIMIT 0 is this case, but 
change ALLOW_CONNECTIONS to accept values: false, true, superuser. ("Superuser" 
to accept connections from superuser only).




Why not use pg_hba.conf to allow only connection from superuser for 
duration?




--
Adrian Klaver
adrian.kla...@aklaver.com

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

[Tom Lane]

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:
> SELECT * FROM table WHERE  ($1 IS null OR $1 < ts) AND ($2 IS null OR ts <=
> $2);
> I like the <@ syntax more. Is there something I can do differently? Maybe a
> different type of index instead?

As others mentioned, a gist index on a tsrange expression could be
used for this, but another idea is to build some syntactic sugar
using a custom operator.  Light testing suggests that this works:

create function expand_range_contain(anyelement, anyrange)
returns bool language sql parallel safe as
$$ select
case when lower_inf($2) then true
 when lower_inc($2) then $1 >= lower($2)
 else $1 > lower($2) end
and
case when upper_inf($2) then true
 when upper_inc($2) then $1 <= upper($2)
 else $1 < upper($2) end
$$;

create operator <<@ (
  function = expand_range_contain,
  leftarg = anyelement,
  rightarg = anyrange
);

select * from table where ts <<@ tsrange($1, $2, '(]');

An important caveat though is that the range operand *must* reduce
to a constant.  If the planner fails to const-simplify those CASE
expressions, you'll not only not get an indexscan, but you'll be worse
off than with the native <@ operator.  So this isn't an all-purpose
fix --- but it might cover your needs and be nicer than maintaining a
second index on the column.

regards, tom lane

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 them in time of the 
restoration. ALLOW_CONNECTIONS=true don't work in this case, because it blocks 
a superuser too. We use CONNECTION LIMIT 0, because this blocks almost all, 
except a superuser. But to prevent a race condition we must set this in the 
same query as CREATE DATABASE, the postgresql syntax allow this. Now this is 
rather complex procedure:

We get `pg_restore --create --list −−section=pre−data` and `pg_restore ---stage 
--list −−section=pre−data`, diff them to get difference.
Get `pg_restore --create --use-list=diff −−section=pre−data` to get sql.
Edit sql: remove ALTER DATABASE ... CONNECTION LIMIT ... if exist, add 
CONNECTION LIMIT 0 to the CREATE DATABASE.
Send sql to the postgresql.
And so on.

Will be convenient to add CONNECTION LIMIT 0 to the CREATE DATABASE just by 
option of pg_restore.
And will be even more straight approach do not use CONNECTION LIMIT 0 is this 
case, but change ALLOW_CONNECTIONS to accept values: false, true, superuser. 
("Superuser" to accept connections from superuser only).

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

[Joe Conway]

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:
> > A GiST or SP-GiST index can accelerate queries involving these
> range operators: =, &&, <@, @>, <<, >>, -|-, &<, and &>
> 
> 
> Hi,
> 
> I have understood that gist indexes can be used if the column is range
> type but my column is just plain timestamp.
> I tried actually to add gist index for the timestamp column. That was
> not possible without installing the btree_gist extension. But that
> didn't work.


Try this:

create table tstest(id int, ts timestamptz);
insert into tstest
  select
   g.i,
   now() - (g.i::text || ' days')::interval
  from generate_series(1, 10) as g(i);

create index tstest_gin
 on tstest using gist((tstzrange(ts,ts,'[]')));

explain analyze
 select * from tstest
 where
  tstzrange(ts,ts,'[]') <@
  tstzrange(now()- '9 days'::interval,
now()-'7 days'::interval,'(]');
QUERY PLAN


 Bitmap Heap Scan on tstest  (cost=24.17..590.16 rows=500 width=12)
(actual time=0.069..0.070 rows=2 loops=1)
   Recheck Cond: (tstzrange(ts, ts, '[]'::text) <@ tstzrange((now() - '9
days'::interval), (now() - '7 days'::interval), '(]'::text))
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on tstest_gin  (cost=0.00..24.04 rows=500
width=0) (actual time=0.063..0.063 rows=2 loops=1)
 Index Cond: (tstzrange(ts, ts, '[]'::text) <@ tstzrange((now()
- '9 days'::interval), (now() - '7 days'::interval), '(]'::text))
 Planning Time: 20.920 ms
 Execution Time: 0.115 ms
(7 rows)

HTH,

Joe

-- 
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development



signature.asc
Description: OpenPGP digital signature

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

[Lauri Kajan]

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: =, &&, <@, @>, <<, >>, -|-, &<, and &>
>

Hi,

I have understood that gist indexes can be used if the column is range type
but my column is just plain timestamp.
I tried actually to add gist index for the timestamp column. That was not
possible without installing the btree_gist extension. But that didn't work.


-Lauri

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:
> 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:
> SELECT * FROM table WHERE  ($1 IS null OR $1 < ts) AND ($2 IS null OR ts <= 
> $2);
> I like the <@ syntax more. Is there something I can do differently? Maybe a 
> different type of index instead?
>
> Thanks,
>
> -Lauri

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: =, &&, <@, @>, <<, >>, -|-, &<, and &>

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

[Dmytro Zhluktenko]

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.*) @@ '''fir'':*'::tsquery)  Buffers: shared hit=2  ->  Bitmap Index Scan on repro_fts_idx  (cost=0.00..12.00 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=1)    Index Cond: (cp.make_tsvector(x.*) @@ '''fir'':*'::tsquery)    Buffers: shared hit=2Planning Time: 0.070 msExecution Time: 0.040 ms  Query runs fine if uses seq scan. Seq Scan is not desired here since this is the query that should run on huge amounts of data and it should find the first element.Obviously, if seq_scan is off, then query still does the same result.Also, if you add 10 more entries, it will still fail to find the first one using index. BR, Dmytro. From: Laurenz AlbeSent: 26 листопада 2019 р. 21:13To: Dmytro Zhluktenko; pgsql-general@lists.postgresql.orgSubject: Re: Postgres Full Text Search Jsonb Array column does not search for first row On Tue, 2019-11-26 at 13:37 +0200, Dmytro Zhluktenko wrote:> Pgsql is unable to perform indexed full text search onto jsonb column containing an array when looking for the first row in the table.> > Any ideas why this is happening?>  > CREATE OR REPLACE FUNCTION cp.make_tsvector(in_t cp."Repro")>  RETURNS tsvector>  LANGUAGE plpgsql>  IMMUTABLE> > [...] >  > CREATE INDEX repro_fts_idx ON cp."Repro" USING gin (cp.make_tsvector(cp."Repro".*)) WITH (fastupdate=off, gin_pending_list_limit='64');>  > [...]> > -- explain analyze> SELECT *>  FROM "cp"."Repro" x where cp.make_tsvector(x) @@ 'sup:*'::tsquery One possibility is that there ar just too few rows in the table. SET enable_seqscan = off; and then try again. If that is not the problm, please provide EXPLAIN (ANALYZE, BUFFERS) output forthe query. Yours,Laurenz Albe-- Cybertec | https://www.cybertec-postgresql.com  

Range contains element filter not using index of the element column

[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:
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:
SELECT * FROM table WHERE  ($1 IS null OR $1 < ts) AND ($2 IS null OR ts <=
$2);
I like the <@ syntax more. Is there something I can do differently? Maybe a
different type of index instead?


Thanks,

-Lauri

Re: Weird seqscan node plan

[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.

> On 27. Nov 2019, at 04:53, Игорь Выскорко  wrote:
> 
> Why planner mistakes in determining the number of rows (every time planner 
> expects only 1 row) in this step I can understand - inner nodes do some joins 
> (inner and outer with filtration) and it's hard to predict result.
> But what I can't understand is why seq scan when it is always slower than 
> index.