RE: error 53200 out of memory

[francis cherat]

I don't think so, but i am not in my office. I can't connect to this server.
 I would answer you on monday

Regards

De : Adrian Klaver 
Envoyé : vendredi 16 mars 2018 16:26
À : francis cherat; pgsql-gene...@postgresql.org
Objet : Re: error 53200 out of memory

On 03/16/2018 04:00 AM, francis cherat wrote:
> Hello,
>
> we have got  an error 53200 after sql statement
>
> [5-1] ERROR:  53200: out of memory
> [6-1] DETAIL:  Failed on request of size 1572864.
> [7-1] LOCATION:  AllocSetRealloc, aset.c:973
>
> in jboss logs we have got those errors
>
> org.jboss.logging.jboss-logging - 3.1.2.GA | ERROR: out of memory
>Détail : Failed on request of size 1572864.
> executing failed
> org.hibernate.exception.GenericJDBCException: could not extract ResultSet
>
> cluster configuration
> server_version  | 9.3.16
> effective_cache_size| 12800MB
> shared_buffers  | 384MB
> work_mem| 384MB
>
> Server configuration
> RHEL 6.5
> RAM : 16Go
> 2 CPUs
>
> Thanks for your feedback

Is there anything in the Postgres and/or system logs from the same time
that might shed on this?



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

Re: Could not open extension control file “/usr/share/postgresql/10/extension/pg_similarity.control”: No such file or directory

[Adrian Klaver]

On 03/16/2018 03:18 PM, hmidi slim wrote:

Hi,
I’m trying to create the extension pg_similarity 
 
(https://github.com/eulerto/pg_similarity) in a docker container.

Dockerfile:

|FROMpostgres:10ENV POSTGRES_USER userENV POSTGRES_PASSWORD userENV 
POSTGRES_DB user_db RUN apt-get update\& upgrade -y \& 
-y install \postgresql-server-dev-all \wget \make \gcc \& -rf 
/var/lib/apt/lists/* RUN wget -c 
'pgfoundry.org/frs/download.php/2237/pg_similarity-0.0.19.tgz 
' 
RUN tar -zxf pg_similarity-0.0.19.tgz RUN cd pg_similarity \ && 
USE_PGXS=1 make \ && USE_PGXS=1 make install


Then I build the image and I run the container. I exec into the container:


|psql -U user-d user_db psql (10.3(Debian 10.3-1.pgdg90+1))Type 
"help"forhelp.user_db=#createextension pg_similarity;|



I got this error:

|ERROR:could notopenextension control 
file"/usr/share/postgresql/10/extension/pg_similarity.control":No such 
fileordirectory|



Under |/usr/share/postgres/10/contrib| I find the file 
pg_similarity.sql.How can I fix this problem?


But do you see pg_similarity.control which what the error is complaining 
about?






|





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

Could not open extension control file “/usr/share/postgresql/10/extension/pg_similarity.control”: No such file or directory

[hmidi slim]

Hi,
I’m trying to create the extension pg_similarity
 (
https://github.com/eulerto/pg_similarity) in a docker container.
Dockerfile:

FROM postgres:10

ENV POSTGRES_USER user
ENV POSTGRES_PASSWORD user
ENV POSTGRES_DB user_db

RUN apt-get update \
&& apt-get upgrade -y \
&& apt-get -y install \
 postgresql-server-dev-all \
 wget \
 make \
 gcc \
&& rm -rf /var/lib/apt/lists/*
RUN wget -c 'pgfoundry.org/frs/download.php/2237/pg_similarity-0.0.19.tgz'
RUN tar -zxf pg_similarity-0.0.19.tgz
RUN cd pg_similarity \
&& USE_PGXS=1 make \
&& USE_PGXS=1 make install

Then I build the image and I run the container. I exec into the container:

psql -U user -d user_db
psql (10.3 (Debian 10.3-1.pgdg90+1))
 Type "help" for help.
user_db=# create extension pg_similarity;


I got this error:

ERROR: could not open extension control file
"/usr/share/postgresql/10/extension/pg_similarity.control": No such
file or directory


Under /usr/share/postgres/10/contrib I find the file pg_similarity.sql.How
can I fix this problem?

Re: Want to disable fully qualified table names on pg_dump in pg_dump (PostgreSQL) 9.6.8

[Adrian Klaver]

On 03/16/2018 01:55 PM, Foolish Ewe wrote:

Thank you Adrian, this is informative.

With best regards:


On a hunch:

1) Installed  SQL Workbench/J (https://www.sql-workbench.eu/index.html)

2) Set up Postgres JDBC driver(https://jdbc.postgresql.org/download.html)

3) Opened DbExplorer:
https://www.sql-workbench.eu/manual/dbexplorer.html

4) Selected all the objects in a schema and used Create DDL Script to 
generate a file with the objects. The objects where not created with a 
schema qualifier.






Bill



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

Re: Want to disable fully qualified table names on pg_dump in pg_dump (PostgreSQL) 9.6.8

[Foolish Ewe]

Thank you Adrian, this is informative.

With best regards:

Bill


From: Adrian Klaver 
Sent: Thursday, March 15, 2018 11:09 PM
To: Foolish Ewe; pgsql-general@lists.postgresql.org
Subject: Re: Want to disable fully qualified table names on pg_dump in pg_dump 
(PostgreSQL) 9.6.8

On 03/15/2018 04:00 PM, Foolish Ewe wrote:
> Hello All:
>
> A number of our team members and I use pg_dump to export schema in an
> Ubuntu 16.04 environment, I happen to have a postgress 9.6.4 server
> that runs in a docker container, and in some cases I see the following
> select statement and fully qualified table names in the
> CREATE TABLE and ALTER TABLE statements:
>
> SELECT pg_catalog.set_config('search_path', '', false);
>
> CREATE TABLE database_name.table_name

I am pretty sure you are actually seeing:

CREATE TABLE schema_name.table_name

>
> and likewise for ALTER TABLE.
>
> But other users (who haven't updated their systems recently) do not see
> the SELECT statement and we see unqualified table names, e.g.:
>
>
> CREATE TABLE table_name
>
>
> These changes in format impact our workflows, we would prefer to have
> unqualified table names.
>

>
> Does anyone have an idea of what might cause this?   Is there something
> we can do on the client side to avoid getting qualified table names?

The reason:

https://www.postgresql.org/about/news/1834/

"The purpose of this release is to address CVE-2018-1058, which
describes how a user can create like-named objects in different schemas
that can change the behavior of other users' queries and cause
unexpected or malicious behavior, also known as a "trojan-horse" attack. "

More information

https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058:_Protect_Your_Search_Path

There is no flag to unset this.

>
> With best regards:
>
> Bill
>
>
>


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

Re: Nvim as external editor in psql as Postgres root user - .vimrc (environment?) issue

[Victoria]

Ok, here is a clumsy solution.

I have this entry in my /home/victoria/.psqlrc file,
  
  \setenv EDITOR "/usr/bin/nvim"

As you see below, I symlink to that file, from postgres.

[victoria@victoria ~]$ sudo -u postgres -i

[postgres@victoria ~]$ pwd

  /var/lib/postgres

[postgres@victoria ~]$ ls -la

  total 108
  drwxrwxr-x  6 postgres victoria  4096 Mar 16 12:48 .
  drwxr-xr-x 33 root root  4096 Mar 16 00:00 ..
  -rw---  1 postgres postgres   385 Mar 16 12:49 .bash_history
  -rwxr-xr-x  1 root root   806 Mar 16 12:41 .bashrc
  drwx--  2 postgres postgres  4096 Mar 16 12:23 .cache
  drwxrwxr-x  2 postgres victoria  4096 Feb 23 13:26 data
  drwx--  3 postgres postgres  4096 Mar 16 12:12 .local
  lrwxrwxrwx  1 postgres victoria62 Feb 23 15:10 .psql_history-postgres -> 
/mnt/Vancouver/Programming/RDB/postgres/postgres/.psql_history
  lrwxrwxrwx  1 postgres victoria22 Feb 23 14:59 .psqlrc -> 
/home/victoria/.psqlrc
  drwxr-xr-x  2 postgres postgres  4096 Mar 16 12:38 .vim
  -rw---  1 postgres postgres   895 Mar 16 12:48 .viminfo
  -rw-r--r--  1 postgres postgres 68234 Mar 16 12:47 .vimrc

## NOTE: .bashrc and .vimrc are edited COPIES (not symlinks) of 
/home/victoria/{.bashrc | .vimrc}

[postgres@victoria ~]$ cat /var/lib/postgres/.bashrc

  export PSQL_EDITOR="/usr/bin/nvim -u /var/lib/postgres/.vimrc"

## "/var/lib/postgres/.vimrc" is the same as "/home/victoria/.vimrc" EXCEPT
## that I commented out line 77, "execute pathogen#infect(), as that was
## throwing an error when starting nvim (Neovim) as the psql \e external editor.

## Important (slight annoyance: need to load that "postgres" .bashrc file:

[postgres@victoria ~]$ exec bash

[postgres@victoria ~]$ psql

  psql (10.2)
  Type "help" for help.

[postgres]# \e  ## can edit in Neovim, with ~/.vimrc settings, preferences, 
customizations ...

[postgres]# \q

[postgres@victoria ~]$ exit

  exit

[victoria@victoria ~]$ 

I wasn't able to automatically run the "exec bash" command after starting 
postgres, hence the need to manually run it in the postgres shell, prior to 
launching psql.

==

Re: ora2pg and invalid command \N

[Julien Rouhaud]

On Fri, Mar 16, 2018 at 8:28 PM, Adrian Klaver
 wrote:
> On 03/16/2018 11:36 AM, Charlin Barak wrote:
>>
>> Thanks for your response.
>>
>> the NULL values from Oracle were indeed replaced by \N in the data output.
>> How do I go about loading this file? I hope I do not have to temporary
>> replace \N with a string in the flat file and then later update in Postgres.
>
>
> That should not be necessary:
>
> https://www.postgresql.org/docs/10/static/sql-copy.html
>
> "NULL
>
> Specifies the string that represents a null value. The default is \N
> (backslash-N) in text format, and an unquoted empty string in CSV format.
> You might prefer an empty string even in text format for cases where you
> don't want to distinguish nulls from empty strings. This option is not
> allowed when using binary format."
>
>
> To  solve this is going to require more information. From my previous post:
>
> What was the complete command you used to generate the data file?
>
> Can we see the complete error?
>
> Also a section of the data file that contains the data that caused the
> error?

I pinged the original author, and he thinks it might be because of a
nested user defined type.  If that's the case, you should try with
current development version
(https://github.com/darold/ora2pg/archive/master.zip) where the issue
should be fixed.  If not, you should try to open an issue on
https://github.com/darold/ora2pg/issues.

Re: ora2pg and invalid command \N

[Adrian Klaver]

On 03/16/2018 11:36 AM, Charlin Barak wrote:

Thanks for your response.

the NULL values from Oracle were indeed replaced by \N in the data 
output. How do I go about loading this file? I hope I do not have to 
temporary replace \N with a string in the flat file and then later 
update in Postgres.


That should not be necessary:

https://www.postgresql.org/docs/10/static/sql-copy.html

"NULL

Specifies the string that represents a null value. The default is 
\N (backslash-N) in text format, and an unquoted empty string in CSV 
format. You might prefer an empty string even in text format for cases 
where you don't want to distinguish nulls from empty strings. This 
option is not allowed when using binary format."



To  solve this is going to require more information. From my previous post:

What was the complete command you used to generate the data file?

Can we see the complete error?

Also a section of the data file that contains the data that caused the 
error?





Thanks.

102 48299   50  C   3  \N
103     48299   \N  G   3  45
...
...

On Fri, Mar 16, 2018 at 2:06 PM, Pavel Stehule > wrote:




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 not clear
and was  not ora2pg related. How do I resolve this issue? Can
someone provide some guidance?


\N is symbol for NULL. but \cmd are psql commands too. This behave
means so psql lost synchronization and try to eval data like
commands. psql import is tolerant - that means so source of this
issue is lost usually. Try to import data with option ON_ERROR_STOP


https://stackoverflow.com/questions/4480381/postgres-sql-fail-on-script-error



Regards

Pavel


Thanks.









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

Re: ora2pg and invalid command \N

[Charlin Barak]

Thanks for your response.

the NULL values from Oracle were indeed replaced by \N in the data output.
How do I go about loading this file? I hope I do not have to temporary
replace \N with a string in the flat file and then later update in Postgres.

Thanks.

102 48299   50  C   3  \N
103 48299   \N  G   3  45
...
...

On Fri, Mar 16, 2018 at 2:06 PM, Pavel Stehule 
wrote:

>
>
> 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 not clear and was  not ora2pg related. How do I resolve
>> this issue? Can someone provide some guidance?
>>
>
> \N is symbol for NULL. but \cmd are psql commands too. This behave means
> so psql lost synchronization and try to eval data like commands. psql
> import is tolerant - that means so source of this issue is lost usually.
> Try to import data with option ON_ERROR_STOP
>
>  https://stackoverflow.com/questions/4480381/postgres-
> sql-fail-on-script-error
>
> Regards
>
> Pavel
>
>>
>> Thanks.
>>
>>
>>
>>
>>
>

Re: Question on corruption (PostgreSQL 9.6.1)

[Peter Geoghegan]

On Thu, Mar 15, 2018 at 8:16 AM, Andy Halsall  wrote:
> Thanks for the advice. I re-indexed and reloaded a pg_dumpall into a spare 
> server - no errors. Will run pg_catcheck asap.

You can also run amcheck. Get the version targeting earlier Postgres
releases off Github (there are packages for most Linux systems). This
can verify that the heap is consistent with indexes.

-- 
Peter Geoghegan

Re: ora2pg and invalid command \N

[Pavel Stehule]

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 not clear and was  not ora2pg related. How do I resolve
> this issue? Can someone provide some guidance?
>

\N is symbol for NULL. but \cmd are psql commands too. This behave means so
psql lost synchronization and try to eval data like commands. psql import
is tolerant - that means so source of this issue is lost usually. Try to
import data with option ON_ERROR_STOP


https://stackoverflow.com/questions/4480381/postgres-sql-fail-on-script-error

Regards

Pavel

>
> Thanks.
>
>
>
>
>

Re: Nvim as external editor in psql as Postgres root user - .vimrc (environment?) issue

[Victoria]

Adrian: "... simplest solution would be to add the .vimrc file to the postgres 
user directory ..."

Good suggestion; I tried that previously, but will try it again.  Thanks!  :-)

Re: ora2pg and invalid command \N

[Adrian Klaver]

On 03/16/2018 10:12 AM, Charlin Barak wrote:

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 


What was the complete command you used to generate the data file?

the file via psql, I got lots of "invalid command \N" errors. The 


Can we see the complete error?

Also a section of the data file that contains the data that caused the 
error?


resolution on the internet was not clear and was  not ora2pg related. 
How do I resolve this issue? Can someone provide some guidance?


Thanks.







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

ora2pg and invalid command \N

[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 not clear and was  not ora2pg related. How do I resolve
this issue? Can someone provide some guidance?

Thanks.

Re: Nvim as external editor in psql as Postgres root user - .vimrc (environment?) issue

[Adrian Klaver]

On 03/16/2018 10:47 AM, Victoria wrote:

Hello; I am using PostgreSQL v.10.2 as a root user (sudo -u postgres -i) on my 
local Arch Linux installation.

I want to use Neovim (nvim v.0.2.2) as my external editor (\e) in psql; the 
current default is the Arch Linux default system editor, vi.

If I add this to my ~/.psqlrc (/home/victoria/.psqlrc)

   \setenv EDITOR "/usr/bin/nano"

then I can use nano, no problem.

However, if I replace that with

   \setenv EDITOR "/usr/bin/nvim"

and chown this postgres directory (to get around a .local/ permissions error 
that arises)

   sudo chown -R postgres:victoria /var/lib/postgres

when I type \e in psql I can edit in nvim.

The issue I have is that as I am in a postgres environment, my user (victoria) 
~/.vimrc file (I link my nvim.init file to it) is not being loaded, so I don't 
have access to my Vim/NeoVim settings.


I guess the question is why sudo -u postgres -i? You can access the 
server via psql from your home directory. If you want to work as 
postgres user the simplest solution would be to add the .vimrc file to 
the postgres user directory.




I tried the nvim -u "" type statements in my ~/.psqlrc, but that 
throws an error about not a valid path (again, likely due to the Pg root environment?).

Suggestions?  Thank you.
   
==





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

Nvim as external editor in psql as Postgres root user - .vimrc (environment?) issue

[Victoria]

Hello; I am using PostgreSQL v.10.2 as a root user (sudo -u postgres -i) on my 
local Arch Linux installation.

I want to use Neovim (nvim v.0.2.2) as my external editor (\e) in psql; the 
current default is the Arch Linux default system editor, vi.

If I add this to my ~/.psqlrc (/home/victoria/.psqlrc)

  \setenv EDITOR "/usr/bin/nano"

then I can use nano, no problem.

However, if I replace that with

  \setenv EDITOR "/usr/bin/nvim"

and chown this postgres directory (to get around a .local/ permissions error 
that arises)

  sudo chown -R postgres:victoria /var/lib/postgres

when I type \e in psql I can edit in nvim.

The issue I have is that as I am in a postgres environment, my user (victoria) 
~/.vimrc file (I link my nvim.init file to it) is not being loaded, so I don't 
have access to my Vim/NeoVim settings.

I tried the nvim -u "" type statements in my ~/.psqlrc, but that 
throws an error about not a valid path (again, likely due to the Pg root 
environment?).

Suggestions?  Thank you.
  
==

Re: STRING_AGG and GROUP BY

[David G. Johnston]

On Fri, Mar 16, 2018 at 9:10 AM, Alexander Farber <
alexander.far...@gmail.com> wrote:

>
> But you say that "tiles" and  "word (score)" are unrelated and this does
> not seem true to me:
>
> For each move id aka "mid" there is a JSON value, describing how the
> player played the letter tiles.
> And for the same "mid" there is a list of one or more "word (score)"s
> achieved...
>

​For each mid you want to know all tiles played and all word scores
achieved - but you want to forget/ignore that a given tile achieved a given
word score.  IOW, you are intentionally forgetting/ignoring the fact that
the tiles and the corresponding word scores are related to each other,
beyond the simple/incomplete relationship that both share the same mid.
You thus need to write a query that only relates tiles and word scores to
mid and not to each other.


> Maybe it is possible to GROUP BY tiles somehow or some kind of special SQL
> JOIN?
>
>
​I do not know if the tables or columns below match your model but the
concept should still come across intact.​

​SELECT mid,
(SELECT string_agg(tiles, '; ') FROM tiles WHERE ta.mid = moves.mid) AS
mid_tiles,
(SELECT string_agg(words, '; ') FROM words WHERE words.mid = moves.mid) AS
mid_words
FROM moves​

There are other ways to write that that could perform better but the idea
holds.

David J.

Re: STRING_AGG and GROUP BY

[Alexander Farber]

Hi David -

On Fri, Mar 16, 2018 at 4:40 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> ​First reaction is to ARRAY_AGG(DISTINCT x) and then write a function that
> converts ​that array into a string by extracting 'letter' from each cell in
> the array.
>
> Thinking it over a bit you have two columns that both are aggregates but
> that are otherwise independent of each other.  Since they are independent
> they cannot be aggregated at the same time.  You need to write a two
> subqueries, either in the target list or as separate from/join items, and
> then join the already aggregated queries together on their common group by
> column.
>
> The presence of DISTINCT here (and, IMO, generally), even if it worked,
> would be an indicator that something is not quite right.
>
>
thank you for confirming my feeling that DISTINCT is a bad indicator here...

But you say that "tiles" and  "word (score)" are unrelated and this does
not seem true to me:

For each move id aka "mid" there is a JSON value, describing how the player
played the letter tiles.
And for the same "mid" there is a list of one or more "word (score)"s
achieved...

Maybe it is possible to GROUP BY tiles somehow or some kind of special SQL
JOIN?

Regards
Alex

Re: STRING_AGG and GROUP BY

[David G. Johnston]

On Fri, Mar 16, 2018 at 7:17 AM, Alexander Farber <
alexander.far...@gmail.com> wrote:

> And I can not change the query to: STRING_AGG(DISTINCT x->>'letter', '')
> AS tiles,
>
> Because for example in the last move with mid=6 the player Bob had played
> 2 tiles, both with letter-value "P" and has formed 2 words (PP and PABCD),
> but adding distinct would suggest he played a single tile "P".
>

​First reaction is to ARRAY_AGG(DISTINCT x) and then write a function that
converts ​that array into a string by extracting 'letter' from each cell in
the array.

Thinking it over a bit you have two columns that both are aggregates but
that are otherwise independent of each other.  Since they are independent
they cannot be aggregated at the same time.  You need to write a two
subqueries, either in the target list or as separate from/join items, and
then join the already aggregated queries together on their common group by
column.

The presence of DISTINCT here (and, IMO, generally), even if it worked,
would be an indicator that something is not quite right.

David J.

Re: error 53200 out of memory

[Adrian Klaver]

On 03/16/2018 04:00 AM, francis cherat wrote:

Hello,

we have got  an error 53200 after sql statement

[5-1] ERROR:  53200: out of memory
[6-1] DETAIL:  Failed on request of size 1572864.
[7-1] LOCATION:  AllocSetRealloc, aset.c:973

in jboss logs we have got those errors

org.jboss.logging.jboss-logging - 3.1.2.GA | ERROR: out of memory
   Détail : Failed on request of size 1572864.
executing failed
org.hibernate.exception.GenericJDBCException: could not extract ResultSet

cluster configuration
server_version  | 9.3.16
effective_cache_size                | 12800MB
shared_buffers                      | 384MB
work_mem                            | 384MB

Server configuration
RHEL 6.5
RAM : 16Go
2 CPUs

Thanks for your feedback


Is there anything in the Postgres and/or system logs from the same time 
that might shed on this?




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

Re: Prompt for parameter value in psql

[Pavel Stehule]

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=
>
>
https://www.postgresql.org/docs/current/static/app-psql.html

see \prompt

Regards

Pavel


> Thanks.
>

Re: Prompt for parameter value in psql

[Melvin Davidson]

On Fri, Mar 16, 2018 at 11:12 AM, Tiffany Thang 
wrote:

> 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=
>
> Thanks.
>





*https://www.postgresql.org/docs/9.6/static/app-psql.html
 \prompt [ text ]
name *

*Prompts the user to supply text, which is assigned to the variable name.
An optional prompt string, text, can be specified. (For multiword prompts,
surround the text with single quotes.)*

*By default, \prompt uses the terminal for input and output. However, if
the -f command line switch was used, \prompt uses standard input and
standard output.*

-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

Re: SELECT .. FOR UPDATE: find out who locked a row

[David G. Johnston]

On Fri, Mar 16, 2018 at 8:00 AM, Enrico Thierbach  wrote:

> Hi Melvin, Stephen, hi list,
>
> *FWIW, I really don't understand your need to identify the actual rows that
> are locked. Once you have identified the query that is causing a block
> (which is usually due to "Idle in Transaction"), AFAIK the only way to
> remedy the problem is to kill the offending query, or wait for it to
> complete. I am not aware of any way available to a user to "unlock"
> individual rows". Indeed, if you could, it would probably lead to
> corruption of some form.*
>
> The goal is to run a job queue, with a potentially largish number of
> workers that feed of the queue. So it would be useful to find out which
> queue entry is being processed right now (I can easily find out: when a row
> cannot be read via SKIP UNLOCKED it is locked, and probably being worked
> upon.) It would also be great to understand which worker holds the lock.
> The intention is NOT to kill the worker or its query.
>
​You probably considered this but the queuing mechanism I use doesn't hold
locks on records during processing.  Workers claim tasks by locking them,
setting a claimed flag of some sort, the releasing the lock (including
worker identity if desired) - repeating the general procedure once
completed.

My volume is such that the bloat the extra update causes is not meaningful
and is easily handled by (auto-)vacuum​.

David J.

Prompt for parameter value in psql

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

Thanks.

Re: SELECT .. FOR UPDATE: find out who locked a row

[Enrico Thierbach]

Thanks Steven,

Evidently my second email got lost somewhere along the way- what 
you're

looking for is an extension called 'pgrowlocks':

https://www.postgresql.org/docs/10/static/pgrowlocks.html

My prior email on that subject is here:

https://www.postgresql.org/message-id/20180315220512.GV2416%40tamriel.snowman.net



I’ll look into that as soon after next week.

Best,
/eno

Re: SELECT .. FOR UPDATE: find out who locked a row

[Stephen Frost]

Enrico,

* Enrico Thierbach (e...@open-lab.org) wrote:
> >*FWIW, I really don't understand your need to identify the actual rows
> >that
> >are locked. Once you have identified the query that is causing a block
> >(which is usually due to "Idle in Transaction"), AFAIK the only way to
> >remedy the problem is to kill the offending query, or wait for it to
> >complete. I am not aware of any way available to a user to "unlock"
> >individual rows". Indeed, if you could, it would probably lead to
> >corruption of some form.*
> 
> The goal is to run a job queue, with a potentially largish number of workers
> that feed of the queue. So it would be useful to find out which queue entry
> is being processed right now (I can easily find out: when a row cannot be
> read via SKIP UNLOCKED it is locked, and probably being worked upon.) It
> would also be great to understand which worker holds the lock. The intention
> is NOT to kill the worker or its query.
> 
> With what the conversation brought up here (still trying to catch up with
> everything) I can:
> 
> 1) determine all workers that currently are holding a lock (via Melvin’s);
> 2) on an individual base try to lock the row in a second connection and use
> a third connection to figure out which worker connection holds a lock on a
> specific single row (via Stephen’s).
> 
> This is probably good enough to cover the necessary basic functionality, so
> thank you for your input.
> 
> Am I correct to assume that there is no other way to determine who is
> holding a lock on a specific row and/or determine this for many rows in one
> go?

Evidently my second email got lost somewhere along the way- what you're
looking for is an extension called 'pgrowlocks':

https://www.postgresql.org/docs/10/static/pgrowlocks.html

My prior email on that subject is here:

https://www.postgresql.org/message-id/20180315220512.GV2416%40tamriel.snowman.net

> (I guess I am also correct to assume that whatever the worker is doing there
> is no way to somehow write this information into the database **via the same
> connection**. (Using a second connection would be obviously easy)

You can write it into the database, of course, but you can't read that
information out by some other process.  Depending on what you're doing,
you can use RAISE NOTICE to send messages back to the connected client
from within a stored procedure or similar.

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: SELECT .. FOR UPDATE: find out who locked a row

[Stephen Frost]

Greetings,

Please don't top-post.

* Melvin Davidson (melvin6...@gmail.com) wrote:
> this whole discussion started because Enrico did not originally specify the
> PostgreSQL version he was working with. So after he did advise it was for
> 9.6, I felt it necessary to explain to him why a certain section of my
> query was commented out and that it would also work for 10. I have
> previously made it a policy to request ops include the PostgreSQL version
> and O/S when submitting to this list, but I was berated by others for
> always requesting ops to provide that extremely difficult information to
> obtain.

There's a difference between saying that a particular query is intended
for certain versions and to ask for what version while providing useful
information, and just immediately replying to every email which doesn't
specify it asking for what the version and OS is.  The former is being
helpful and specific while soliciting for additional information, while
the latter tends to just create noise on the list, particularly when the
question isn't ultimately relevant.

Note that the above comments are entirely generic- I'm not aware of the
specific emails which you're referring to or why it was suggested that
they weren't helpful.  Specific concerns regarding list usage should
really be addressed to the list moderators and not individuals taking
action on their own.

> I also felt it important that I express my opinion that the changes needed
> were caused by what I felt was cosmetic and unnecessary changes to the
> catalog. There is an old saying "If it ain't broke, don't fix it" and that
> certainly applies here.

There was a great deal of discussion and consideration for the changes
and specific reasons why they were made.

> Now, as to your request for me to read the thread in the url's you
> suggested, I did read most of the content. I note that the entire
> discussion was amongst
> PostgreSQL-development , So at no time
> was the generic population of PostgreSQL users and DBA's involved.

That version of PostgreSQL, as with all of them, went through a beta
period where we specifically ask for feedback from users.  Serious
concerns raised during that time period do result in changes being made
prior to release.

> Therefore, said population, myself included, had no foreknowledge of the
> intended changes which is the cause of the problem. Therefore your
> statement "you might want to consider speaking up in some reasonable time
> frame, not six years later" is abrasive at best, since I, and others, only
> found out about it after the fact. Not to mention, even if I did complain
> earlier, I seriously doubt the changes could or would be reversed.

If you're interested in having a say in what will be included in the
next version of PostgreSQL then I strongly encourage you, and all users,
to work with the beta packages that are put out, every year, for people
to test with.

> At this point I have said all I have to say and will discuss it no further.
> I can only strongly recommend that in the future, proposed changes to
> system catalogs that could adversely affect existing scripts and
> applications be sent to the generic PostgreSQL population (IE:
> pgsql-general@lists.postgresql.org) for comment BEFORE said changes are
> implemented.

Beta releases are announced through the pgsql-announce mailing list,
which is a list that has a great deal less traffic than -general and one
which I'd suggest all users subscribe to, to be aware of changes which
are likely to be in the next release and to test to see if there are any
serious issues, and also to update their applications and queries in
advance of changes being released.

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: SELECT .. FOR UPDATE: find out who locked a row

[Melvin Davidson]

Tom,
this whole discussion started because Enrico did not originally specify the
PostgreSQL version he was working with. So after he did advise it was for
9.6, I felt it necessary to explain to him why a certain section of my
query was commented out and that it would also work for 10. I have
previously made it a policy to request ops include the PostgreSQL version
and O/S when submitting to this list, but I was berated by others for
always requesting ops to provide that extremely difficult information to
obtain.
I also felt it important that I express my opinion that the changes needed
were caused by what I felt was cosmetic and unnecessary changes to the
catalog. There is an old saying "If it ain't broke, don't fix it" and that
certainly applies here.

Now, as to your request for me to read the thread in the url's you
suggested, I did read most of the content. I note that the entire
discussion was amongst
PostgreSQL-development , So at no time
was the generic population of PostgreSQL users and DBA's involved.
Therefore, said population, myself included, had no foreknowledge of the
intended changes which is the cause of the problem. Therefore your
statement "you might want to consider speaking up in some reasonable time
frame, not six years later" is abrasive at best, since I, and others, only
found out about it after the fact. Not to mention, even if I did complain
earlier, I seriously doubt the changes could or would be reversed.

At this point I have said all I have to say and will discuss it no further.
I can only strongly recommend that in the future, proposed changes to
system catalogs that could adversely affect existing scripts and
applications be sent to the generic PostgreSQL population (IE:
pgsql-general@lists.postgresql.org) for comment BEFORE said changes are
implemented.

On Thu, Mar 15, 2018 at 11:23 PM, Tom Lane  wrote:

> Melvin Davidson  writes:
> > Yes, Stephen, I certainly understand making changes to system catalogs
> > _when necessary_.  That being said, the first change was the renaming of
> > pid to procpid in pg_stat_activity.  However, I contend that was more
> > because someone felt that it was more to make the column names
> > consistent across catalogs, rather than necessity.
>
> Please read all of
> https://www.postgresql.org/message-id/flat/201106091554.
> p59Fso314146%40momjian.us
> where this was discussed to death (and rejected), and then read all of
> https://www.postgresql.org/message-id/flat/CAKq0gvK8PzMWPv19_o7CGg8ZQ0G%
> 2BUuAWor5RrAg0SOmWTqqLwg%40mail.gmail.com
> which is the thread in which the change was agreed to after all
> (on the grounds that we were breaking backwards compatibility of
> the view anyway with respect to other, more important, columns).
>
> If you still feel that we make incompatible changes without adequate
> consideration, that's your right, but you might want to consider
> speaking up in some reasonable time frame, not six years later.
> This could have been objected to as late as 9.2 beta, so it's not
> like you need to be drinking from the pgsql-hackers firehose continually
> in order to weigh in.  But 9.2 is not just released, it's EOL, so it's
> really kinda late to be objecting.
>
> regards, tom lane
>



-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!

error 53200 out of memory

[francis cherat]

Hello,

we have got  an error 53200 after sql statement

[5-1] ERROR:  53200: out of memory
[6-1] DETAIL:  Failed on request of size 1572864.
[7-1] LOCATION:  AllocSetRealloc, aset.c:973

in jboss logs we have got those errors

org.jboss.logging.jboss-logging - 3.1.2.GA | ERROR: out of memory
  Détail : Failed on request of size 1572864.
executing failed
org.hibernate.exception.GenericJDBCException: could not extract ResultSet

cluster configuration
server_version  | 9.3.16
effective_cache_size| 12800MB
shared_buffers  | 384MB
work_mem| 384MB

Server configuration
RHEL 6.5
RAM : 16Go
2 CPUs

Thanks for your feedback

Re: Question on corruption (PostgreSQL 9.6.1)

[Andy Halsall]

Thanks for the advice. I re-indexed and reloaded a pg_dumpall into a spare 
server - no errors. Will run pg_catcheck asap.

regards,
Andy

Andy Halsall  writes:
>  db=# select * from x where col_a = 4675635;
>  col_a   | col_b   | col_c   | col_d | col_e |  
>last_modified
> +-+-+---+---+---
> | | |   |   |
> (1 row)

> Row 4675635 is very odd - NULL columns and at the same time retrievable by a 
> value in col_a.

Doesn't seem particularly surprising if col_a is indexed.  That query
would choose an indexscan plan, which would normally not bother to
re-verify the index condition against heap tuples found via the index.

If you're continuing to use this damaged database, it might be a good
idea to try to REINDEX all your indexes.  That'd be particularly
useful for primary/unique indexes, since if corruption has led to
any apparent duplicate rows, the reindex would fail and complain.
But in any case it'd clean up heap-vs-index inconsistencies like the
above, as well as repairing any cases where the corruption was in an
index rather than heap.

Another test I'd strongly recommend is to see if you can pg_dumpall
and reload into a spare server.  That might catch forms of data
corruption that reindexing would not, such as violated CHECK constraints.

    regards, tom lane

RE: Circle and box intersect

[Martin Moore]

Cheers - I'd tried postGIS on 9.6 but had install issues. Installed first time 
on 10 :)


-Original Message-
From: Andreas Kretschmer [mailto:andr...@a-kretschmer.de] 
Sent: 16 March, 2018 11:46 AM
To: pgsql-general@lists.postgresql.org
Subject: Re: Circle and box intersect



Am 16.03.2018 um 11:00 schrieb Martin Moore:
> PG10
>
> Is there an operator to determine if a box and circle intersect?
> I can only see box && box and can use centre+rad and distance to 
> calculate circle:circle.
>
> Thanks.
>
>
please don't hijack other mail-threads by answering & changing the subject, 
your mail still contains references to "SELECT .. FOR UPDATE: 
find out who locked a row"
Create a new mail for a new thread!


to your question: consider PostGIS and it's functions st_intersects()

Regards, Andreas

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

Re: Circle and box intersect

[Andreas Kretschmer]

Am 16.03.2018 um 11:00 schrieb Martin Moore:

PG10

Is there an operator to determine if a box and circle intersect?
I can only see box && box and can use centre+rad and distance to calculate
circle:circle.

Thanks.


please don't hijack other mail-threads by answering & changing the 
subject, your mail still contains references to "SELECT .. FOR UPDATE: 
find out who locked a row"

Create a new mail for a new thread!


to your question: consider PostGIS and it's functions st_intersects()

Regards, Andreas

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

Circle and box intersect

[Martin Moore]

PG10

Is there an operator to determine if a box and circle intersect? 
I can only see box && box and can use centre+rad and distance to calculate
circle:circle.

Thanks.