Re: psql \copy hanging

[Arnaud L.]

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 now reads as :

psql -h myserver -a mydb < originalscriptwithoutproblematicline.sql
psql -h myserver -a mydb < problematicline.sql

It hung at the problematic line, so during the second psql command.

I'm really at loss... I *believe* that the problem lies either in psql 
or in PostgreSQL, but I really don't know what to try now.



Regards
--
Arnaud

Re: psql \copy hanging

[Pavel Stehule]

ú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 now reads as :
> psql -h myserver -a mydb < originalscriptwithoutproblematicline.sql
> psql -h myserver -a mydb < problematicline.sql
>
> It hung at the problematic line, so during the second psql command.
>
> I'm really at loss... I *believe* that the problem lies either in psql
> or in PostgreSQL, but I really don't know what to try now.
>

you can write simple C application with COPY API
https://www.postgresql.org/docs/12/libpq-copy.html

Then you can eliminate or ensure locality of problem.

more, you can use server side copy. Superuser can read data from server
file system.

Regards

Pavel


>
> Regards
> --
> Arnaud
>
>
>

Re: psql \copy hanging

[Arnaud L.]

Le 08/10/2019 à 09:28, Pavel Stehule a écrit :
you can write simple C application with COPY API 
https://www.postgresql.org/docs/12/libpq-copy.html


Unfortunately, I don't know C.


Then you can eliminate or ensure locality of problem.

more, you can use server side copy. Superuser can read data from server 
file system.


Yes, but in this case the file has to be written to a network share, and 
the windows user under wich PostgreSQL runs (Network Service) cannot be 
given write permission on this share.

That's the reason for the use of \copy.


Now that I think about it, *maybe* this started happening after a server 
upgrade. Since this is intermittent, I'm not really sure about this, but 
some time ago we moved our server to a different hardware and upgraded 
from 9.3 to 11 at the same time.
The dates don't perfectly match though, we upgraded around 8th of august 
and the problem arose ~2 weeks later for the first time.
The client was upgraded around that same time period (not exactly the 
same time if I remember correctly).



Regards
--
Arnaud

Re: Performance on JSONB select

[PegoraroF10]

Sorry, I told you that the status was always populated but not, I need that
filter for the index because not all records are using that json structure.
When json is not null then yes, Status is always there. 
I have maybe 20 or 25% of records having json populated, so, I really need
that filter.

Yes, that solution is fine. Selectivity is fine, speed is fine and the index
is "almost dynamic"



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

GPS coordinates problem

[Timmy Siu]

Dear All Postgre Users,

I want to develop a new project on Lavarel 5 and Postgresql 11.

Now, I need Global Position System coordinates as a data type. How do I 
define it in Postgresql 11 or 12?



Thanks for your help.

Timmy

Re: psql \copy hanging

[Daniel Verite]

Arnaud L. wrote:

> As a side note, COPY (...) TO STDOUT \g 'somefile' does not work in a 
> script file (i.e. it does not work if the command is passed in a file 
> via the -f argument).
> The command runs fine, no error is raised either by the client or the 
> server, but no file is written.

Testing this with 11.5, it works for me.
Make sure you're running the latest minor release (on the client
side in this case), because a related fix was issued last February.
For the 11 branch it was in version 11.2.


Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

Re: psql \copy hanging

[Arnaud L.]

Le 08/10/2019 à 12:55, Daniel Verite a écrit :

Arnaud L. wrote:

As a side note, COPY (...) TO STDOUT \g 'somefile' does not work in a 
script file (i.e. it does not work if the command is passed in a file 
via the -f argument).
The command runs fine, no error is raised either by the client or the 
server, but no file is written.


Testing this with 11.5, it works for me.
Make sure you're running the latest minor release (on the client
side in this case), because a related fix was issued last February.
For the 11 branch it was in version 11.2.



I'm on 11.5-1 on the client side, but I had added an output redirection 
for this batch file to try to understand what was happening during the 
night runs, and that might be the reason why \g fails (i.e. some 
interference between the console redirections).

I'll give it another try without these redirections.

Regards
--
Arnaud

Re: GPS coordinates problem

[Andreas Kretschmer]

Am 08.10.19 um 12:50 schrieb Timmy Siu:
Now, I need Global Position System coordinates as a data type. How do 
I define it in Postgresql 11 or 12?


consider PostGIS.


Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Re: GPS coordinates problem

[Robert Heinen]

You can use the postgis extension:

create extension postgis;

Then you can create a geography coulmn

location geography( point, 4326)

and insert a lat /long as a point like this:
ST_GeographyFromText('SRID=4326;POINT(%(longitude)s %(latitude)s)'),





On Tue, Oct 8, 2019 at 1:30 PM Andreas Kretschmer 
wrote:

>
>
> Am 08.10.19 um 12:50 schrieb Timmy Siu:
> > Now, I need Global Position System coordinates as a data type. How do
> > I define it in Postgresql 11 or 12?
>
> consider PostGIS.
>
>
> Regards, Andreas
>
> --
> 2ndQuadrant - The PostgreSQL Support Company.
> www.2ndQuadrant.com
>
>
>
>

Re: psql \copy hanging

[Arnaud L.]

Le 08/10/2019 à 12:55, Daniel Verite a écrit :

Testing this with 11.5, it works for me.
Make sure you're running the latest minor release (on the client
side in this case), because a related fix was issued last February.
For the 11 branch it was in version 11.2.


OK, my bad, backslashes in a windows-style share path have to be escaped.
Anyway, it hung using this syntax during last night's run.
I'll give it another try tonight  just to be sure.


Regards
--
Arnaud

Re: psql \copy hanging

[Adrian Klaver]

On 10/8/19 12:06 AM, Arnaud L. wrote:

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 now reads as :

psql -h myserver -a mydb < originalscriptwithoutproblematicline.sql
psql -h myserver -a mydb < problematicline.sql


This is going to be hard to troubleshoot if you change your commands.

Previously you had:
psql -h %MYPGSERVER% -a -f myscript.sql %MYPGDB%

Changing more then one thing at a time makes it that much more difficult 
to isolate the issue.


I would create an entirely separate batch file that runs just 
problematicline.sql.


Given that you suspect Postgres it would be helpful to see the query 
that underlies the view you are copying.


You might want to look at autoexplain:

https://www.postgresql.org/docs/11/auto-explain.html

as a way of getting information at run time.




It hung at the problematic line, so during the second psql command.

I'm really at loss... I *believe* that the problem lies either in psql 
or in PostgreSQL, but I really don't know what to try now.



Regards
--
Arnaud




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

RE: Declarative Range Partitioning Postgres 11

[Shatamjeev Dewan]

Thanks Michael.

From: Michael Lewis 
Sent: October-07-19 7:18 PM
To: Shatamjeev Dewan 
Cc: pgsql-general 
Subject: Re: Declarative Range Partitioning Postgres 11

No, what you want is not possible and probably won't ever be I would expect. 
Scanning every partition to validate the primary key isn't scalable.

RE: Declarative Range Partitioning Postgres 11

[Shatamjeev Dewan]

Hi Michael,

In this case , I always need to include partition key(date)  in primary key ( 
if I have a primary key defined on non partition key column e.g id (in my 
case), to make it a composite primary key (id, date). This would allow 
duplicate id with different date,which is not desirable .

Thanks,
Shatamjeev

From: Michael Lewis 
Sent: October-07-19 7:18 PM
To: Shatamjeev Dewan 
Cc: pgsql-general 
Subject: Re: Declarative Range Partitioning Postgres 11

No, what you want is not possible and probably won't ever be I would expect. 
Scanning every partition to validate the primary key isn't scalable.

Re: psql \copy hanging

[Arnaud L.]

Le 08/10/2019 à 16:03, Adrian Klaver a écrit :

This is going to be hard to troubleshoot if you change your commands.

Previously you had:
psql -h %MYPGSERVER% -a -f myscript.sql %MYPGDB%

Changing more then one thing at a time makes it that much more difficult
to isolate the issue.


Yes, true. Actually I do one change at a time, I'm just no posting every 
single attempt. So I tried to feed the script using console redirection 
rather than -f as you can see. That was the only change.



I would create an entirely separate batch file that runs just
problematicline.sql.


OK, that's easy.
Actually the batch file is not doing much more than running this psql 
command, but that's really not a problem/



Given that you suspect Postgres it would be helpful to see the query
that underlies the view you are copying.
You might want to look at autoexplain:

https://www.postgresql.org/docs/11/auto-explain.html

as a way of getting information at run time.


OK that's nice.
Since I don't want to mess with the whole server configuration, I added 
some auto_explain settings to my script.


So for tonight, my script looks like this :

LOAD 'auto_explain';
SET auto_explain.log_min_duration = 0;
SET auto_explain.log_analyze = true;
COPY (SELECT * FROM myview) TO STDOUT \g 'myserver\\myshare\\myfile.txt'

And it'll run in a separate batch.

Thanks for your help Adrian !

Regards
--
Arnaud

Re: psql \copy hanging

[Daniel Verite]

Arnaud L. wrote:

> Anyway, it hung using this syntax during last night's run.
> I'll give it another try tonight  just to be sure.

When psql.exe is hanging, maybe you could use a tool like
Process Monitor [1] or Process Explorer [2] to get insights about
what it's stuck on or what it's doing exactly.

[1] https://docs.microsoft.com/en-us/sysinternals/downloads/procmon
[2] https://docs.microsoft.com/en-us/sysinternals/downloads/process-explorer


Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

Re: psql \copy hanging

[Arnaud L.]

Le 08/10/2019 à 16:55, Daniel Verite a écrit :

Arnaud L. wrote:


Anyway, it hung using this syntax during last night's run.
I'll give it another try tonight  just to be sure.


When psql.exe is hanging, maybe you could use a tool like
Process Monitor [1] or Process Explorer [2] to get insights about
what it's stuck on or what it's doing exactly.

[1] https://docs.microsoft.com/en-us/sysinternals/downloads/procmon
[2] https://docs.microsoft.com/en-us/sysinternals/downloads/process-explorer


Yes, I already did that, and unfortunately there's no activy.
There is absolutely no activity in procmon using psql.exe as a filter.
process-xp is not giving more information, processor usage is 0%.


Regards
--
Arnaud

Re: Table locking during backup

[Tom Lane]

=?utf-8?Q?Artur_Zaj=C4=85c?=  writes:
> First session:
> BEGIN; 
> set transaction isolation level repeatable read, read only;
> lock TABLE gm.tableabc IN access share mode;

> Second session:
> BEGIN;
> CREATE TEMP TABLE IF NOT EXISTS tableabc (Id BIGINT DEFAULT random()) 
> INHERITS (gm.tableabc);

> "CREATE TEMP TABLE" does not wait for anything. Waiting state is only when I 
> start third session with the same queries as in second.

A bit of looking into pg_locks will show you that CREATE TABLE
... INHERITS takes ShareUpdateExclusiveLock on the table being
inherited from.  Per the manual,

SHARE UPDATE EXCLUSIVE

Conflicts with the SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW
EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode
protects a table against concurrent schema changes and VACUUM
runs.

So that doesn't conflict with pg_dump, but would conflict with another
session trying to INHERIT from the same table.  AFAICS, pg_dump's lock
isn't involved in that conflict at all.

If you only notice blocking when pg_dump is running, it's likely not
due to this lock in isolation, but the fact that pg_dump takes
access-share locks on everything in sight.  That can block sessions
that are trying to do DDL, causing other things to queue up behind
them, depending on what other locks those session(s) already hold.

There's really no substitute for looking into pg_locks to see
what's going on in cases like this ...

regards, tom lane

Segmentation fault with PG-12

[Andreas Joseph Krogh]

In our production-environment we get sig11 every now and then after upgrading 
to PG-12: 2019-10-08 15:45:29.654 CEST [8829-76] LOG: server process (PID 
20631) was terminated bysignal 11: Segmentation fault 
 2019-10-08 15:45:29.654 CEST [8829-77] DETAIL: Failed process was running: 
COMMIT
 2019-10-08 15:45:29.654 CEST [8829-78] LOG: terminating any other active 
server processes
Will running a debug-enabled build slow things noticably down? Is there a way 
to make it dump a stack-trace (or back-trace in C-land?) on sig11? -- Andreas 
Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com
  www.visena.com   
 

Re: Segmentation fault with PG-12

[Tom Lane]

Andreas Joseph Krogh  writes:
> Will running a debug-enabled build slow things noticably down?

gcc promises that the generated code is the same with or without debug.
I think clang does too.  With other compilers you may pay some penalty.

> Is there a way 
> to make it dump a stack-trace (or back-trace in C-land?) on sig11?

You should be able to get a core file from which you can extract a
stack trace (and other info) after the fact.

https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend

regards, tom lane

Re: temporary files

[dangal]

thank you very much you take for your time
We raised the work_mem to 130 mb and there was no more problem!
Now we are seeing to improve the query, it is complicated because it is
generated by a product that we have installed in production!



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Declarative Range Partitioning Postgres 11

[Michael Lewis]

On Mon, Oct 7, 2019 at 5:56 PM Ron  wrote:

> On 10/7/19 6:17 PM, Michael Lewis wrote:
> > No, what you want is not possible and probably won't ever be I would
> expect.
>
> Sure it is.  Maybe not the (weird) way that Postgres does partitioning,
> but
> the legacy RDBMS that I still occasionally maintain has for at least 25
> years had partition key independent of any indexes.
>
> > Scanning every partition to validate the primary key isn't scalable.
>
> That's only because of the way Pg implements partitioning.
>

I can dig that, but since this is a Postgres list and everything I have
heard indicates it is not a limitation that is likely to be removed in
Postgres, it seems like we are having two different discussions.

Re: Declarative Range Partitioning Postgres 11

[Michael Lewis]

On Tue, Oct 8, 2019 at 8:00 AM Shatamjeev Dewan  wrote:

> Hi Michael,
>
>
>
> In this case , I always need to include partition key(date)  in primary
> key ( if I have a primary key defined on non partition key column e.g id
> (in my case), to make it a composite primary key (id, date). This would
> allow duplicate id with different date,which is not desirable .
>

If you are generating the ID with a sequence, there isn't any real world
likelihood of conflict, but I do understand your concern in terms of
enforcing data integrity. Other than creating a custom stored procedure
that functions as a primary key constraint, I don't know of any way around
that.

Let's take a step back... why do you think you need to partition at all?
And why partition by the date/timestamp/timestamptz field? Also, from what
I have seen, PG12 is when partitioning really gets performant in terms of
more than 10 to 100 partitions, and you can then create FKeys to the
partitioned table (not possible in PG11). Also, if your frequent access of
the table is by date/timestamptz field, then you might consider a BRIN
index if you have high correlation between physical storage and values in
that field. That can mitigate the need for partitioning.

Our organization will be waiting until next quarter to upgrade to PG12 and
then partitioning a few of our largest tables. That is to say, I don't have
experience with partitioning in production yet so others may chime in with
better advice.

Re: Segmentation fault with PG-12

[Andreas Joseph Krogh]

På tirsdag 08. oktober 2019 kl. 17:24:21, skrev Tom Lane mailto:t...@sss.pgh.pa.us>>: Andreas Joseph Krogh  writes:
 > Will running a debug-enabled build slow things noticably down?

 gcc promises that the generated code is the same with or without debug.
 I think clang does too. With other compilers you may pay some penalty. Nice, 
I'm using the ubuntu-packages, so I'll go ahead and installpostgresql-12-dbgsym
> Is there a way
 > to make it dump a stack-trace (or back-trace in C-land?) on sig11?

 You should be able to get a core file from which you can extract a
 stack trace (and other info) after the fact.

 
https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend
I'll look into that, thanks. -- Andreas Joseph Krogh CTO / Partner - Visena AS 
Mobile: +47 909 56 963 andr...@visena.com  
www.visena.com   

RE: Declarative Range Partitioning Postgres 11

[Shatamjeev Dewan]

Thanks a lot Michael for invaluable advise . Appreciate your great help and 
support.

From: Michael Lewis 
Sent: October-08-19 1:33 PM
To: Shatamjeev Dewan 
Cc: pgsql-general 
Subject: Re: Declarative Range Partitioning Postgres 11

On Tue, Oct 8, 2019 at 8:00 AM Shatamjeev Dewan 
mailto:sde...@nbsps.com>> wrote:
Hi Michael,

In this case , I always need to include partition key(date)  in primary key ( 
if I have a primary key defined on non partition key column e.g id (in my 
case), to make it a composite primary key (id, date). This would allow 
duplicate id with different date,which is not desirable .

If you are generating the ID with a sequence, there isn't any real world 
likelihood of conflict, but I do understand your concern in terms of enforcing 
data integrity. Other than creating a custom stored procedure that functions as 
a primary key constraint, I don't know of any way around that.

Let's take a step back... why do you think you need to partition at all? And 
why partition by the date/timestamp/timestamptz field? Also, from what I have 
seen, PG12 is when partitioning really gets performant in terms of more than 10 
to 100 partitions, and you can then create FKeys to the partitioned table (not 
possible in PG11). Also, if your frequent access of the table is by 
date/timestamptz field, then you might consider a BRIN index if you have high 
correlation between physical storage and values in that field. That can 
mitigate the need for partitioning.

Our organization will be waiting until next quarter to upgrade to PG12 and then 
partitioning a few of our largest tables. That is to say, I don't have 
experience with partitioning in production yet so others may chime in with 
better advice.

Re: Declarative Range Partitioning Postgres 11

[Ron]

On 10/8/19 12:33 PM, Michael Lewis wrote:
On Tue, Oct 8, 2019 at 8:00 AM Shatamjeev Dewan > wrote:


Hi Michael,

In this case , I always need to include partition key(date)  in
primary key ( if I have a primary key defined on non partition key
column e.g id (in my case), to make it a composite primary key (id,
date). This would allow duplicate id with different date,which is not
desirable .


If you are generating the ID with a sequence, there isn't any real world 
likelihood of conflict, but I do understand your concern in terms of 
enforcing data integrity. Other than creating a custom stored procedure 
that functions as a primary key constraint, I don't know of any way around 
that.


Let's take a step back... why do you think you need to partition at all? 
And why partition by the date/timestamp/timestamptz field?


Because archiving old is (well, /should be/) easier that way.

--
Angular momentum makes the world go 'round.

Case Insensitive Comparison with Postgres 12

[Igal Sapir]

I am trying to test a simple case insensitive comparison.  Most likely the
collation that I chose is wrong, but I'm not sure how to choose the correct
one (for English/US?).  Here is my snippet:

create collation case_insensitive(
provider=icu, locale='en-US-x-icu', deterministic=false
);
select 'Abc' = 'abc' collate case_insensitive;

I expected true but am getting false.

Any thoughts?

Thanks,

Igal

Re: Case Insensitive Comparison with Postgres 12

[Morris de Oryx]

As I understand it, custom collation are not applied globally. Meaning, you
have to associate a collation with a column or en expression with COLLATE.

Transition tables for column-specific UPDATE triggers

[Guy Burgess]

Hello,

The manual says: 
https://www.postgresql.org/docs/current/sql-createtrigger.html


   A column list cannot be specified when requesting transition relations.

And (I think the same point):

   The standard allows transition tables to be used with
   column-specific |UPDATE| triggers, but then the set of rows that
   should be visible in the transition tables depends on the trigger's
   column list. This is not currently implemented by PostgreSQL.

Are there any plans to allow transition tables to be used with 
column-specific UPDATE triggers?  Or, is there another way for a trigger 
function to see the rows changed by a column-specific UPDATE trigger?


Thanks