duplicate key value violates unique constraint "pg_statistic_relid_att_inh_index" while performing full vacuum on the database

[Raghavendra Rao J S V]

Hi,


While performing vacuum full, I have  received the below highlighted error.
Please guide me how to resolve this issue.


​/opt/postgres/9.2/bin/psql -p 5433 --username=cmuser  cpcm -c "*VACUUM
FULL ANALYZE*;"


ERROR:  *duplicate key value violates unique constraint
"pg_statistic_relid_att_inh_index"*
DETAIL:  Key (starelid, staattnum, stainherit)=(18915, 6, f) already exists.

' pg_statistic ' is a meta data table. Is it ok if I remove one duplicated
record from ' pg_statistic' table?.

-- 
Regards,
Raghavendra Rao J S V

Enhancement to psql command, feedback.

[John McKown]

I just wanted to throw this out to the users before I made a complete fool
of myself by formally requesting it. But I would like what I hope would be
a minor change (enhancement) to the psql command. If you look on this page,
https://wiki.postgresql.org/wiki/Shared_Database_Hosting ,
you will see a number of example which look like:

psql -U postgres template1 -f - << EOT

REVOKE ALL ON DATABASE template1 FROM public;
REVOKE ALL ON SCHEMA public FROM public;
GRANT ALL ON SCHEMA public TO postgres;
CREATE LANGUAGE plpgsql;

EOT


To me this looks similar to a UNIX shell script. Now, going sideways for a
second, if someone wanted to create a "self contained" awk script. It would
look something like:

#!/bin/awk -f
... awk code ...

When a user executes the above from the command line, the UNIX system runs
the program in the first "magic" line as if the user had entered "/bin/awk
-f ..." where the ... is replaced by the name of the file executed followed
by the rest of the command line parameters.

I think it would be nice if psql would do the same, mainly for
"consistency" with other UNIX scripting languages, such as python, perl, &
gawk.

The example above would then become:

#!/bin/psql -U postgres template1 -f
REVOKE ALL ON DATABASE template1 FROM public;
REVOKE ALL ON SCHEMA public FROM public;
GRANT ALL ON SCHEMA public TO postgres;
CREATE LANGUAGE plpgsql;

Does this seem reasonable to others? When I actually try the following as a
"script", I get an error.

=== transcript ===

$ls -l ./x.psql; cat ./x.psql; ./x.psql
-rwxr-xr-x. 1 joarmc joarmc 40 May  9 02:55 ./x.psql
#!/usr/bin/psql -f
select * from table;
psql:./x.psql:2: ERROR:  syntax error at or near "#!/"
LINE 1: #!/usr/bin/psql -f
   ^


​I have not looked at the source yet, but it seems that it would be "easy"
to implement if psql would simply ignore the first line of any file
referenced via the "-f" parameter if it started with "#!" or maybe even
just "#". I'm not suggesting ignoring _every_ line that start with that
"magic", just the first.​


-- 
We all have skeletons in our closet.
Mine are so old, they have osteoporosis.

Maranatha! <><
John McKown

Re: Enhancement to psql command, feedback.

[Pavel Stehule]

2018-05-09 9:59 GMT+02:00 John McKown :

> I just wanted to throw this out to the users before I made a complete fool
> of myself by formally requesting it. But I would like what I hope would be
> a minor change (enhancement) to the psql command. If you look on this page,
> https://wiki.postgresql.org/wiki/Shared_Database_Hosting ,
> you will see a number of example which look like:
>
> psql -U postgres template1 -f - << EOT
>
> REVOKE ALL ON DATABASE template1 FROM public;
> REVOKE ALL ON SCHEMA public FROM public;
> GRANT ALL ON SCHEMA public TO postgres;
> CREATE LANGUAGE plpgsql;
>
> EOT
>
>
> To me this looks similar to a UNIX shell script. Now, going sideways for a
> second, if someone wanted to create a "self contained" awk script. It would
> look something like:
>
> #!/bin/awk -f
> ... awk code ...
>
> When a user executes the above from the command line, the UNIX system runs
> the program in the first "magic" line as if the user had entered "/bin/awk
> -f ..." where the ... is replaced by the name of the file executed followed
> by the rest of the command line parameters.
>
> I think it would be nice if psql would do the same, mainly for
> "consistency" with other UNIX scripting languages, such as python, perl, &
> gawk.
>

These languages has defined # as line comment. It is not true for SQL.

Regards

Pavel



>
> The example above would then become:
>
> #!/bin/psql -U postgres template1 -f
> REVOKE ALL ON DATABASE template1 FROM public;
> REVOKE ALL ON SCHEMA public FROM public;
> GRANT ALL ON SCHEMA public TO postgres;
> CREATE LANGUAGE plpgsql;
>
> Does this seem reasonable to others? When I actually try the following as
> a "script", I get an error.
>

> === transcript ===
>
> $ls -l ./x.psql; cat ./x.psql; ./x.psql
> -rwxr-xr-x. 1 joarmc joarmc 40 May  9 02:55 ./x.psql
> #!/usr/bin/psql -f
> select * from table;
> psql:./x.psql:2: ERROR:  syntax error at or near "#!/"
> LINE 1: #!/usr/bin/psql -f
>^
>
>
> ​I have not looked at the source yet, but it seems that it would be "easy"
> to implement if psql would simply ignore the first line of any file
> referenced via the "-f" parameter if it started with "#!" or maybe even
> just "#". I'm not suggesting ignoring _every_ line that start with that
> "magic", just the first.​
>
>
> --
> We all have skeletons in our closet.
> Mine are so old, they have osteoporosis.
>
> Maranatha! <><
> John McKown
>

partition table query allocate much memory

[tao tony]

hi guys,

I'm not sure it was a bug,but a query on partition table allocate nearly 100GB 
memory is not normal.pid :119775 as below

[cid:part1.2A42CFAD.715BF014@outlook.com]

SQL:

SELECT DISTINCT etl_source,gid FROM zh_erp.buy2 WHERE gid NOT IN (SELECT gid 
FROM zh_erp.goodsh);

I had modified the query to:SELECT count(DISTINCT etl_source,gid) FROM 
zh_erp.buy2 WHERE gid NOT IN (SELECT gid FROM zh_erp.goodsh);

This problem appear again.

buy2:102GB,438382597 rows,118 partitions.

etl_source VARCHAR,gid INTEGER.

goodsh:20GB,24692534 rows,118 partitions.

gid INTEGER.

dbversion：PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.2.1 
20170829 (Red Hat 7.2.1-1), 64-bit

mem and paralle setting:

shared_buffers = 8GB

work_mem = 2GB

max_worker_processes = 48   # (change requires restart)
max_parallel_workers_per_gather = 4 # taken from max_parallel_workers
max_parallel_workers = 8# maximum number of 
max_worker_processes that


I reset the work_mem:when it was 1GB or 2GB,the problem appeared,while it set  
to 64NB,128MB,and 512MB,it will run in parallel mode and the memory less than 
10GB.

I'm not sure it was a bug and I also cloud not explain why it allocated so much 
memory.Dosn't each sub partition table allocated the size of work_mem memory 
and not free it?

When work_mem=1GB or more,the  query plan is  a HashAggregate.otherwise it was 
Unique and running on parallel mode.


Thanks!

Re: Enhancement to psql command, feedback.

[Matt Zagrabelny]

On Wed, May 9, 2018 at 3:05 AM, Pavel Stehule 
wrote:

>
>
> 2018-05-09 9:59 GMT+02:00 John McKown :
>
>> I just wanted to throw this out to the users before I made a complete
>> fool of myself by formally requesting it. But I would like what I hope
>> would be a minor change (enhancement) to the psql command. If you look on
>> this page, https://wiki.postgresql.org/wiki/Shared_Database_Hosting ,
>> you will see a number of example which look like:
>>
>> psql -U postgres template1 -f - << EOT
>>
>> REVOKE ALL ON DATABASE template1 FROM public;
>> REVOKE ALL ON SCHEMA public FROM public;
>> GRANT ALL ON SCHEMA public TO postgres;
>> CREATE LANGUAGE plpgsql;
>>
>> EOT
>>
>>
>> To me this looks similar to a UNIX shell script. Now, going sideways for
>> a second, if someone wanted to create a "self contained" awk script. It
>> would look something like:
>>
>> #!/bin/awk -f
>> ... awk code ...
>>
>> When a user executes the above from the command line, the UNIX system
>> runs the program in the first "magic" line as if the user had entered
>> "/bin/awk -f ..." where the ... is replaced by the name of the file
>> executed followed by the rest of the command line parameters.
>>
>> I think it would be nice if psql would do the same, mainly for
>> "consistency" with other UNIX scripting languages, such as python, perl, &
>> gawk.
>>
>
> These languages has defined # as line comment. It is not true for SQL.
>

For fun, not because I've put considerable thought into it:

#!/usr/bin/psql --enable-hash-comment -f
...

-m

Re: Enhancement to psql command, feedback.

[Ron]

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

you will see a number of example which look like:

psql -U postgres template1 -f - << EOT

REVOKE ALL ON DATABASE template1 FROM public;
REVOKE ALL ON SCHEMA public FROM public;
GRANT ALL ON SCHEMA public TO postgres;
CREATE LANGUAGE plpgsql;

EOT

To me this looks similar to a UNIX shell script.


Because it *is* a Unix shell script.  The "<< EOT" is part of a heredoc, 
which is designed to keep everything in one place instead of needing a 
second file for the SQL commands.


https://en.wikipedia.org/wiki/Here_document

(The concept is as old as computing.  Anyone who's worked on mainframes or 
proprietary minicomputers from DEC will instantly recognize it.)


--
Angular momentum makes the world go 'round.

Re: New install of 9.5.12 missing default PostgreSQL DB

[Adrian Klaver]

On 05/08/2018 06:12 PM, chandru.ar...@yahoo.com wrote:
I installed PostgreSQL using PostgreSQL-9.5.12-1-win64-bigsql on a 
Windows 10 system.  I installed as a local admin.  The installation 
completed fine, although it failed earlier when I tried to install it as 
non-admin of the laptop.


When I launch pgAdmin, I don't see the default instance of the DB 
installed on my system.  When I expand the Servers group it is empty.  I 


What do you consider the default database?

You need to set up the Server(s):

https://www.pgadmin.org/docs/pgadmin4/3.x/connecting.html

went to services to make sure that the PostgreSQL service (PostgreSQL 
9.5 Server) was running. I restarted the service too.



What an I missing?  Appreciate any help on this.

Thanks,

Chandru





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

Re: Enhancement to psql command, feedback.

[John McKown]

On Wed, May 9, 2018 at 3:05 AM, Pavel Stehule 
wrote:

>
>
> 2018-05-09 9:59 GMT+02:00 John McKown :
>
>> I just wanted to throw this out to the users before I made a complete
>> fool of myself by formally requesting it. But I would like what I hope
>> would be a minor change (enhancement) to the psql command. If you look on
>> this page, https://wiki.postgresql.org/wiki/Shared_Database_Hosting ,
>> you will see a number of example which look like:
>>
>> psql -U postgres template1 -f - << EOT
>>
>> REVOKE ALL ON DATABASE template1 FROM public;
>> REVOKE ALL ON SCHEMA public FROM public;
>> GRANT ALL ON SCHEMA public TO postgres;
>> CREATE LANGUAGE plpgsql;
>>
>> EOT
>>
>>
>> To me this looks similar to a UNIX shell script. Now, going sideways for
>> a second, if someone wanted to create a "self contained" awk script. It
>> would look something like:
>>
>> #!/bin/awk -f
>> ... awk code ...
>>
>> When a user executes the above from the command line, the UNIX system
>> runs the program in the first "magic" line as if the user had entered
>> "/bin/awk -f ..." where the ... is replaced by the name of the file
>> executed followed by the rest of the command line parameters.
>>
>> I think it would be nice if psql would do the same, mainly for
>> "consistency" with other UNIX scripting languages, such as python, perl, &
>> gawk.
>>
>
> These languages has defined # as line comment. It is not true for SQL.
>

​Thanks, that looks like a "NO" vote to me. ​



>
> Regards
>
> Pavel
>
>

-- 
We all have skeletons in our closet.
Mine are so old, they have osteoporosis.

Maranatha! <><
John McKown

Re: issues when installing postgres

[Adrian Klaver]

On 05/08/2018 05:54 PM, Antonio Silva wrote:

Hello!



Comments inline.


I bought a new computer and I installed Ubuntu 18.04 and after PostgreSQL.
sudo apt install postgresql postgresql-contrib pgadmin3


Are you using the Ubuntu or Postgres repos?



Nevertheless I had some issues with configuration files and decided to
uninstall it completely
sudo apt purge postgresql postgresql-contrib pgadmin3

When I installed it again I notice that postgresql.conf and pg_hba.conf
were the oldies files. Then I uninstall Postgres once more and removed the
directory /etc/postgresql/

After a new install I noticed that the directory /etc/postgresql/ was
completely empty - it was not created again. There are no more
postgresql.conf and pg_hba.conf files. I could find only
postgresql.conf.sample and pg_hba.conf.sample at /usr/share/postgresql/10

/etc/init.d/postgresql status says that Postgres is running fine


Can you connect to Postgres using psql?



● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor
preset: enabled)
Active: active (exited) since Tue 2018-05-08 10:43:23 -03; 1h 55min ago
Process: 6451 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 6451 (code=exited, status=0/SUCCESS)


To be running the server would need its conf files somewhere.



What should I have to do to heve the folder /etc/postgresql/10/ ... and all
its files agais?


If you are going to use the packages then yes.




I really appreciate any help. Thanks in advance.

All the best

--
Antônio Olinto Ávila da Silva




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

Re: Enhancement to psql command, feedback.

[John McKown]

On Wed, May 9, 2018 at 8:17 AM, Ron  wrote:

> On 05/09/2018 02:59 AM, John McKown wrote:
>
> I just wanted to throw this out to the users before I made a complete fool
> of myself by formally requesting it. But I would like what I hope would be
> a minor change (enhancement) to the psql command. If you look on this page,
> https://wiki.postgresql.org/wiki/Shared_Database_Hosting ,
> you will see a number of example which look like:
>
> psql -U postgres template1 -f - << EOT
>
> REVOKE ALL ON DATABASE template1 FROM public;
> REVOKE ALL ON SCHEMA public FROM public;
> GRANT ALL ON SCHEMA public TO postgres;
> CREATE LANGUAGE plpgsql;
>
> EOT
>
>
> To me this looks similar to a UNIX shell script.
>
>
> Because it *is* a Unix shell script.  The "<< EOT" is part of a heredoc,
> which is designed to keep everything in one place instead of needing a
> second file for the SQL commands.
>
> https://en.wikipedia.org/wiki/Here_document
>
> (The concept is as old as computing.  Anyone who's worked on mainframes or
> proprietary minicomputers from DEC will instantly recognize it.)
>

​Yes, I use HERE docs in my shell scripts. I was just, sort of, wanting to
avoid that by making a "slight" change to the psql program to ignore the
first (and only the first) line of any file referenced​ via a "-f". This is
NOT any kind of critical necessity. I just think it would be "nice" simply
because _I_ have a habit of use the "magic" #! at the start of the first
like in order to have other "languages" (such as python, perl, gawk), be
invoked with the script file name as a parameter. One reason to avoid a
HERE doc is from what I've learned about how BASH at least implements them.
The BASH shell sees the HERE document and copies it into a "temporary" disk
file. It then opens this file and supplies that file descriptor to whatever
is being fed the HERE document as input. So, in effect, using a HERE
document, at least in BASH, does a lot more I/O to the disk system.

Again, this is just a discussion point. And I'm quite willing to admit
defeat if most people don't think that it is worth the effort.



>
>
> --
> Angular momentum makes the world go 'round.
>



-- 
We all have skeletons in our closet.
Mine are so old, they have osteoporosis.

Maranatha! <><
John McKown

Re: Enhancement to psql command, feedback.

[Stephen Frost]

Greetings,

* John McKown (john.archie.mck...@gmail.com) wrote:
> Again, this is just a discussion point. And I'm quite willing to admit
> defeat if most people don't think that it is worth the effort.

For my 2c, at least, I do think it'd be kind of neat to have, but we'd
need a fool-proof way to realize that's how we're being called and,
ideally, that would be something we could detect without having to have
special flags for psql which anyone writing such a script would have to
be aware of.

Do you know if there's a way to detect that we're being called this
way..?

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: Enhancement to psql command, feedback.

[David G. Johnston]

On Wed, May 9, 2018 at 6:44 AM, John McKown 
wrote:

> Again, this is just a discussion point. And I'm quite willing to admit
> defeat if most people don't think that it is worth the effort.
>

​-1, at least per the example.  I would not want "-U postgres" inside the
file.  I tend to rely on service entries, not environment variables, and
wouldn't want to hard-code them either.  While psql has grown more
flow-control capabilities recently it is, in most cases, a support language
for me, not a main entry point.  Shell scripts merge the per-instance
run-time environment I need with the behavior the script provides - merging
that I find I need more often than not and don't miss the added overhead in
the few cases where it is unnecessary.

David J.

Re: Enhancement to psql command, feedback.

[John McKown]

On Wed, May 9, 2018 at 8:56 AM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wed, May 9, 2018 at 6:44 AM, John McKown 
> wrote:
>
>> Again, this is just a discussion point. And I'm quite willing to admit
>> defeat if most people don't think that it is worth the effort.
>>
>
> ​-1, at least per the example.  I would not want "-U postgres" inside the
> file.  I tend to rely on service entries, not environment variables, and
> wouldn't want to hard-code them either.  While psql has grown more
> flow-control capabilities recently it is, in most cases, a support language
> for me, not a main entry point.  Shell scripts merge the per-instance
> run-time environment I need with the behavior the script provides - merging
> that I find I need more often than not and don't miss the added overhead in
> the few cases where it is unnecessary.
>
> David J.
>
>
​I agree. I wouldn't want the -U inside a "regular" shell script either. As
a minor example, consider the following _almost_ equivalent scripts.


$ cat psql-script.sh
#!/bin/sh
psql "$@" -f - <${file}
psql -U postgres -d somedb -h remote-host.com -f ${file}

It just that the HERE document doesn't actually create the ${file}
variable. I have NO idea how other shell implement HERE documents.

However, in the second case, the "magic" first line causes psql, at
present, to report an error and abort. This is why I'd like to modify how
the file referenced via the -f argument is processed. That is, the first
line of any file referenced & executed via the -f argument will be ignored
if and only if it starts with a shebang (#!). If the first line of the file
does not start with a shebang, it is processed normally as are all
subsequent lines.

If I get the energy & time, I'll give a look at the actual source. If it is
within my, admitted limited, ability to generate a patch to implement what
I'm thinking of, I'll post it over on the development forum.


-- 
We all have skeletons in our closet.
Mine are so old, they have osteoporosis.

Maranatha! <><
John McKown

Re: Enhancement to psql command, feedback.

[John McKown]

On Wed, May 9, 2018 at 8:52 AM, Stephen Frost  wrote:

> Greetings,
>
> * John McKown (john.archie.mck...@gmail.com) wrote:
> > Again, this is just a discussion point. And I'm quite willing to admit
> > defeat if most people don't think that it is worth the effort.
>
> For my 2c, at least, I do think it'd be kind of neat to have, but we'd
> need a fool-proof way to realize that's how we're being called and,
> ideally, that would be something we could detect without having to have
> special flags for psql which anyone writing such a script would have to
> be aware of.
>

​I probably should have taken a good look at how the psql code actually
handles the "-f" argument. Unfortunately, I've been very "time poor​"
recently due to some medical work which, along with "real" work, keeps me
away from the house for about 15 hrs a day, except for weekends which I
used to try to recover.



>
> Do you know if there's a way to detect that we're being called this
> way..?
>
> Thanks!
>
> Stephen
>



-- 
We all have skeletons in our closet.
Mine are so old, they have osteoporosis.

Maranatha! <><
John McKown

Re: Enhancement to psql command, feedback.

[David G. Johnston]

On Wed, May 9, 2018 at 7:17 AM, John McKown 
wrote:

>
> However, in the second case, the "magic" first line causes psql, at
> present, to report an error and abort. This is why I'd like to modify how
> the file referenced via the -f argument is processed. That is, the first
> line of any file referenced & executed via the -f argument will be ignored
> if and only if it starts with a shebang (#!). If the first line of the file
> does not start with a shebang, it is processed normally as are all
> subsequent lines.
>
>
​Don't forget the \i and \ir meta commands.

David J.
​

Re: Enhancement to psql command, feedback.

[Tom Lane]

Stephen Frost  writes:
> Greetings,
> * John McKown (john.archie.mck...@gmail.com) wrote:
>> Again, this is just a discussion point. And I'm quite willing to admit
>> defeat if most people don't think that it is worth the effort.

> For my 2c, at least, I do think it'd be kind of neat to have, but we'd
> need a fool-proof way to realize that's how we're being called and,
> ideally, that would be something we could detect without having to have
> special flags for psql which anyone writing such a script would have to
> be aware of.
> Do you know if there's a way to detect that we're being called this
> way..?

Actually, I'd say that's exactly what *not* to do.  It's generally
important that a script act the same whether or not it was invoked
with a shortcut.  For instance, just because you had one of these
magic lines at the top, you'd not want it to not work if called
via \include.

So my take on it is that this is a request to ignore the first line
if it starts with "#!" (and yes, I'd insist on checking both characters).
I do not see that as noticeably more dangerous than the existing kluge
to ignore a UTF BOM character at the start of the file.

The concerns about whether psql would get invoked with a desirable
set of options if you tried to do this seem more worrisome, but if
that does work out usefully, I think this is a reasonable proposal.

regards, tom lane

Re: Enhancement to psql command, feedback.

[Christopher Browne]

On Wed, 9 May 2018 at 04:00, John McKown 
wrote:
> To me this looks similar to a UNIX shell script. Now, going sideways for
a second, if someone wanted to create a "self contained" awk script. It
would look something like:

> #!/bin/awk -f
> ... awk code ...

I have found it convenient when Lisp implementations (that use ; as the
comment indicator, and where # tends to mean something quite different)
have provided something like this.

I'd quite like it if I could start a script with
#!psql
or similar and have it be, yes, indeed, directly executable via psql.

There are several complications that leap out at me...

1.  Would want to run the apropos psql

It's a common thing in Perl to have an idiom where a suitable script prefix
goes off and finds the appropriate Perl instance.  It's possible to have
multiple versions of psql, it would be kinda nice if the script could
choose the right one to run.  But that could easily be trying too hard.

2.  Specifying database connection parameters

Making use of PGDATABASE and other environment parameters is well and good;
it would also be a fine thing for something in the first line to be able to
specify values.

Thus, something like...

#!psql -d postgresql://postgres@localhost:5432/some_db_name

The not-so-nice is that perhaps that first line provides some defaults, and
one could override slices of that via PGDATABASE/PGHOST/PGUSER/...

What overrides what is an excellent question, and perhaps a horrible
bikeshedding debate.

3.  Nesting is a funny thing; I'm not sure if \i should simply ignore the
first line if it begins with #!, or if it should become a "subshell"
perhaps in another psql session.

In effect, is \i like a C #include (ergo, perhaps strip leading line
beginning with #!), or is it a separate psql session, with separate
connection/transaction?  I imagine it's more like #include, but
both seem potentially useful.

There's certainly a danger of bikeshedding.
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"

RE: Error creating plpython3u extension

[Bruce Harold]

Thank you Adrian and Jeff.

I made some progress and can extend databases with plpython3u.

Re. the edb doc, "C:\edb\LanguagePack-10\x64\Python-3.4\bin" does not exist, 
stripping 'bin' off the path is required.

Setting PYTHONHOME for the system is unfriendly to the rest of my python 
dependencies, so to make this specific to pg I edited the pgAdmin 4 shortcut to 
set the variable there, the shortcut target becomes: 

C:\Windows\System32\cmd.exe /c "SET 
PYTHONHOME=C:\edb\languagepack-10\x64\Python-3.4 && START  pgAdmin4.exe"

The "Start in" property does not change, the executable is found.

Whether or not this works across the server I guess I'll find out shortly, I'm 
a newbie doing a proof of concept so deployment isn't a concern.

Bruce

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com] 
Sent: Tuesday, May 8, 2018 3:38 PM
To: Bruce Harold ; pgsql-general@lists.postgresql.org
Subject: Re: Error creating plpython3u extension

On 05/08/2018 01:28 PM, Bruce Harold wrote:
> Hi
> 
> I have PostgreSQL 10 on Windows 10 and the EDB language pack X64 for 
> Python 3.4 etc.
> 
> I edited the *system* environment variables:

According to this:

https://urldefense.proofpoint.com/v2/url?u=https-3A__www.enterprisedb.com_docs_en_10.0_Language-5FPack-5Fv10_EDB-5FPostgres-5FLanguage-5FPack-5FGuide.1.08.html&d=DwIC-g&c=n6-cguzQvX_tUIrZOS_4Og&r=nRgKzHukYnMuxsKJmL-jRA&m=A89phKNv5UT7bfmJVcGEJawoPPWKYHm4vDtVUer15Lk&s=d0WYDSstKd642xdAtngF9suS84SpG9PZibyLpTmPjUo&e=

This:
> 
> PYTHONPATH = C:\edb\languagepack-10\x64\Python-3.4

should be:

set PYTHONHOME=C:\edb\languagepack-10\x64\Python-3.4


> 
> Added C:\edb\LanguagePack-10\x64\Python-3.4\bin to PATH
> 
> Creating the extension plpython3u errors:
> 
> ERROR: could not load library "C:/Program
> Files/PostgreSQL/10/lib/plpython3.dll": The specified module could not 
> be found
> 
> I have conda on the same machine with one Python 3.6 environment, I 
> tried adding a Python 3.4 environment with conda but no luck there either.
> 
> Anyone have any experience enabling plpython3u in a conda environment?
> 
> Thanks
> 
> Bruce
> 


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

RE: Error creating plpython3u extension

[Bruce Harold]

It seems my cheap hack doesn't work, the server connection breaks on CREATE 
FUNCTION unless PYTHONHOME is set for the system.

-Original Message-
From: Bruce Harold 
Sent: Wednesday, May 9, 2018 9:46 AM
To: 'Adrian Klaver' ; 
pgsql-general@lists.postgresql.org
Subject: RE: Error creating plpython3u extension

Thank you Adrian and Jeff.

I made some progress and can extend databases with plpython3u.

Re. the edb doc, "C:\edb\LanguagePack-10\x64\Python-3.4\bin" does not exist, 
stripping 'bin' off the path is required.

Setting PYTHONHOME for the system is unfriendly to the rest of my python 
dependencies, so to make this specific to pg I edited the pgAdmin 4 shortcut to 
set the variable there, the shortcut target becomes: 

C:\Windows\System32\cmd.exe /c "SET 
PYTHONHOME=C:\edb\languagepack-10\x64\Python-3.4 && START  pgAdmin4.exe"

The "Start in" property does not change, the executable is found.

Whether or not this works across the server I guess I'll find out shortly, I'm 
a newbie doing a proof of concept so deployment isn't a concern.

Bruce

-Original Message-
From: Adrian Klaver [mailto:adrian.kla...@aklaver.com]
Sent: Tuesday, May 8, 2018 3:38 PM
To: Bruce Harold ; pgsql-general@lists.postgresql.org
Subject: Re: Error creating plpython3u extension

On 05/08/2018 01:28 PM, Bruce Harold wrote:
> Hi
> 
> I have PostgreSQL 10 on Windows 10 and the EDB language pack X64 for 
> Python 3.4 etc.
> 
> I edited the *system* environment variables:

According to this:

https://urldefense.proofpoint.com/v2/url?u=https-3A__www.enterprisedb.com_docs_en_10.0_Language-5FPack-5Fv10_EDB-5FPostgres-5FLanguage-5FPack-5FGuide.1.08.html&d=DwIC-g&c=n6-cguzQvX_tUIrZOS_4Og&r=nRgKzHukYnMuxsKJmL-jRA&m=A89phKNv5UT7bfmJVcGEJawoPPWKYHm4vDtVUer15Lk&s=d0WYDSstKd642xdAtngF9suS84SpG9PZibyLpTmPjUo&e=

This:
> 
> PYTHONPATH = C:\edb\languagepack-10\x64\Python-3.4

should be:

set PYTHONHOME=C:\edb\languagepack-10\x64\Python-3.4


> 
> Added C:\edb\LanguagePack-10\x64\Python-3.4\bin to PATH
> 
> Creating the extension plpython3u errors:
> 
> ERROR: could not load library "C:/Program
> Files/PostgreSQL/10/lib/plpython3.dll": The specified module could not 
> be found
> 
> I have conda on the same machine with one Python 3.6 environment, I 
> tried adding a Python 3.4 environment with conda but no luck there either.
> 
> Anyone have any experience enabling plpython3u in a conda environment?
> 
> Thanks
> 
> Bruce
> 


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

Re: Enhancement to psql command, feedback.

[David G. Johnston]

On Wed, May 9, 2018 at 9:04 AM, Christopher Browne 
wrote:

> On Wed, 9 May 2018 at 04:00, John McKown 
> wrote:
> > To me this looks similar to a UNIX shell script. Now, going sideways for
> a second, if someone wanted to create a "self contained" awk script. It
> would look something like:
>
> > #!/bin/awk -f
> > ... awk code ...
>
> I have found it convenient when Lisp implementations (that use ; as the
> comment indicator, and where # tends to mean something quite different)
> have provided something like this.
>
> I'd quite like it if I could start a script with
> #!psql
> or similar and have it be, yes, indeed, directly executable via psql.
>
> There are several complications that leap out at me...
>

​Frankly, none of those are complications.  For all the areas of concern
you described the decision for desired behavior has already been made.
They seem to limit the extent to which a shebang would be useful...

1. O/S PATH determines what a bare "psql" invocation finds
2. arguments override environment variables
3. \i means include, no transaction semantics

And trying harder for #1 doesn't seem worthwhile - or maybe is a feature in
its own right.  Something like:

--@ client-version >= 9.6
--@ server-version >= 9.4

If those comments are found in a file psql is evaluating it should error
out if the condition doesn't match.  That should apply regardless of
invocation method.

David J.

Why is my Postgre server went in recovery mode all in sudden

[nikhil raj]

Hi Team,

I dont have any idea why did my Postgres server crash and it says

timestamp=2018-05-07 00:34:11.209 EDT,user=,db=,app=,client=,
transaction-ID=0,session_start_timestamp=2018-04-26 10:08:19
EDT,SQL_state=0LOG:  worker process: parallel worker for PID 2864 (PID
4476) exited with exit code 0
timestamp=2018-05-07 00:34:11.209 EDT,user=,db=,app=,client=,
transaction-ID=0,session_start_timestamp=2018-04-26 10:08:19
EDT,SQL_state=0LOG:  terminating any other active server processes
timestamp=2018-05-07 00:34:11.209 EDT,user=postgres,db=Ozalo,
app=[unknown],transaction-ID=0,session_start_timestamp=2018-05-07 00:32:46
EDT,SQL_state=0LOG:  duration: 0.331 ms  bind : SELECT
"repository"."c_token" AS "token", "repository"."c_path" AS "path"

 FROM

t_e20so1_repository AS "repository" INNER JOIN t_e20so1_document_bigint
AS "documentbigint" ON  "repository"."c_repositoryid" =
"documentbigint"."c_value"

  WHERE  "documentbigint"."c_documentid" = 201989

AND  "documentbigint"."c_fieldid" = 'b035afc8-439f-4f2c-a9ae-
e054052511fd'
timestamp=2018-05-07 00:34:11.210 EDT,user=postgres,db= Ozalo,app=[unknown],
transaction-ID=0,session_start_timestamp=2018-05-07 00:32:46
EDT,SQL_state=0LOG:  duration: 0.061 ms  execute : SELECT
"repository"."c_token" AS "token", "repository"."c_path" AS "path"
 FROM
 t_zs01_sys_dm AS "repository" INNER JOIN t_e20so1_document_bigint AS
"documentbigint" ON  "repository"."c_repositoryid" =
"documentbigint"."c_value"
  WHERE  "documentbigint"."c_documentid" = 201989
AND  "documentbigint"."c_fieldid" = 'b035afc8-439f-4f2c-a9ae-
e054052511fd'


after Some time i was receiving this error


timestamp=2018-05-07 00:34:11.218 EDT,user=postgres,db= Ozalo
,app=[unknown],transaction-ID=0,session_start_timestamp=2018-05-07 00:34:10
EDT,SQL_state=57P02WARNING:  terminating connection because of crash of
another server process
timestamp=2018-05-07 00:34:11.218 EDT,user=postgres,db= Ozalo
,app=[unknown],transaction-ID=0,session_start_timestamp=2018-05-07 00:34:10
EDT,SQL_state=57P02DETAIL:  The postmaster has commanded this server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
timestamp=2018-05-07 00:34:11.218 EDT,user=postgres,db= Ozalo
,app=[unknown],transaction-ID=0,session_start_timestamp=2018-05-07 00:34:10
EDT,SQL_state=57P02HINT:  In a moment you should be able to reconnect to
the database and repeat your command.

timestamp=2018-05-07 00:34:11.357 EDT,user=,db=,app=,client=,
transaction-ID=0,session_start_timestamp=2018-04-26 10:08:20
EDT,SQL_state=57P02DETAIL:  The postmaster has commanded this server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
timestamp=2018-05-07 00:34:11.357 EDT,user=,db=,app=,client=,
transaction-ID=0,session_start_timestamp=2018-04-26 10:08:20
EDT,SQL_state=57P02HINT:  In a moment you should be able to reconnect to
the database and repeat your command.
timestamp=2018-05-07 00:34:11.379 EDT,user=postgres,db=
Ozalo,app=[unknown],,transaction-ID=0,session_start_timestamp=2018-05-07
00:34:11 EDT,SQL_state=57P03FATAL:  the database system is in recovery mode
timestamp=2018-05-07 00:34:11.381 EDT,user=postgres,db=
Ozalo,app=[unknown],transaction-ID=0,session_start_timestamp=2018-05-07
00:34:11 EDT,SQL_state=57P03FATAL:  the database system is in recovery mode



what is the reason it corrupted  share memory ?

what is meant by The postmaster has commanded this server process to roll
back the current transaction and exit, because another server process
exited abnormally and possibly corrupted shared memory. ?
 how much of share memory if its consume  it will crash

 Please can any one help me in this
or else what is the reason of crash of DB server

Current using 10.3

 Current Config

max_connections = 5000
shared_buffers = 7680MB
effective_cache_size = 23040MB

maintenance_work_mem = 1920MBmin_wal_size = 1GBmax_wal_size =
2GBcheckpoint_completion_target = 0.7wal_buffers =
16MBdefault_statistics_target = 100random_page_cost =
1.1effective_io_concurrency = 200max_worker_processes =
16max_parallel_workers_per_gather = 8max_parallel_workers = 16work_mem
= 196kB



Thanks

Re: Enhancement to psql command, feedback.

[Jan Claeys]

On Wed, 2018-05-09 at 08:36 -0500, John McKown wrote:
> On Wed, May 9, 2018 at 3:05 AM, Pavel Stehule
>  wrote:

> > These languages has defined # as line comment. It is not true for
> > SQL.
> 
> Thanks, that looks like a "NO" vote to me. 
> 


Not necessarily. There are other languages which don't use "#" for
comments, but ignore a first line when it starts with "#" or when you
add a specific command line option.


-- 
Jan Claeys

Re: duplicate key value violates unique constraint "pg_statistic_relid_att_inh_index" while performing full vacuum on the database

[Laurenz Albe]

Raghavendra Rao J S V wrote:
> While performing vacuum full, I have  received the below highlighted error. 
> Please guide me how to resolve this issue.
> 
> 
> /opt/postgres/9.2/bin/psql -p 5433 --username=cmuser  cpcm -c "VACUUM FULL 
> ANALYZE;"
> 
> 
> ERROR:  duplicate key value violates unique constraint 
> "pg_statistic_relid_att_inh_index"
> DETAIL:  Key (starelid, staattnum, stainherit)=(18915, 6, f) already exists.
> 
> ' pg_statistic ' is a meta data table. Is it ok if I remove one duplicated 
> record from ' pg_statistic' table?. 

That is data corruption.

Do you have any idea how you got there?
Any crashes, any unsafe settings? Unreliable hardware?

Fortunately it is only the pg_statistic table.

You can stop the server, start it with

   pg_ctl start -o -O

Then connect as superuser and run

   TRUNCATE pg_statistic;
   ANALYZE;

That should take care of the problem.

It would be a good idea to pg_dumpall the cluster, remove it,
recreate it and load the dump so that you are sure to have
no data corruption.

Yours,
Laurenz Albe

Re: issues when installing postgres

[Antonio Silva]

Hello Adrian

Are you using the Ubuntu or Postgres repos?
> I'm using the Ubuntu repos

Can you connect to Postgres using psql?
> No I cannot

Thanks

Antonio


2018-05-09 10:36 GMT-03:00 Adrian Klaver :

> On 05/08/2018 05:54 PM, Antonio Silva wrote:
>
>> Hello!
>>
>>
> Comments inline.
>
> I bought a new computer and I installed Ubuntu 18.04 and after PostgreSQL.
>> sudo apt install postgresql postgresql-contrib pgadmin3
>>
>
> Are you using the Ubuntu or Postgres repos?
>
>
>> Nevertheless I had some issues with configuration files and decided to
>> uninstall it completely
>> sudo apt purge postgresql postgresql-contrib pgadmin3
>>
>> When I installed it again I notice that postgresql.conf and pg_hba.conf
>> were the oldies files. Then I uninstall Postgres once more and removed the
>> directory /etc/postgresql/
>>
>> After a new install I noticed that the directory /etc/postgresql/ was
>> completely empty - it was not created again. There are no more
>> postgresql.conf and pg_hba.conf files. I could find only
>> postgresql.conf.sample and pg_hba.conf.sample at /usr/share/postgresql/10
>>
>> /etc/init.d/postgresql status says that Postgres is running fine
>>
>
> Can you connect to Postgres using psql?
>
>
>> ● postgresql.service - PostgreSQL RDBMS
>> Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor
>> preset: enabled)
>> Active: active (exited) since Tue 2018-05-08 10:43:23 -03; 1h 55min ago
>> Process: 6451 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
>> Main PID: 6451 (code=exited, status=0/SUCCESS)
>>
>
> To be running the server would need its conf files somewhere.
>
>
>> What should I have to do to heve the folder /etc/postgresql/10/ ... and
>> all
>> its files agais?
>>
>
> If you are going to use the packages then yes.
>
>
>
>
>> I really appreciate any help. Thanks in advance.
>>
>> All the best
>>
>> --
>> Antônio Olinto Ávila da Silva
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: pg_stat_statements : how to catch non successfully finished statements ?

[legrand legrand]

Hello,

Here is a patch that  :
- adds a new guc: pg_stat_statements.track_errors boolean (default to true),
- capture of DML, DDL, PL/PGSQL commands in error into pgss.

There is always a risk that new code used in PG_CATCH (mainly pgss_store)
gives an error.
I'm not able to tell when it could occur and what would be the impact ...

see
pgss_with_errors.patch
  

pgss_with_errors.txt
  

Regards
PAscal



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

Re: Error creating plpython3u extension

[Adrian Klaver]

On 05/09/2018 10:38 AM, Bruce Harold wrote:

It seems my cheap hack doesn't work, the server connection breaks on CREATE 
FUNCTION unless PYTHONHOME is set for the system.


Yeah, pretty sure the server is not looking at the environment that 
pgAdmin4 is running in as that is not part of the core.


Not sure how to solve this. I don't run Windows enough anymore to offer 
any suggestions. Maybe someone from EDB will chime in.




-Original Message-
From: Bruce Harold
Sent: Wednesday, May 9, 2018 9:46 AM
To: 'Adrian Klaver' ; 
pgsql-general@lists.postgresql.org
Subject: RE: Error creating plpython3u extension

Thank you Adrian and Jeff.

I made some progress and can extend databases with plpython3u.

Re. the edb doc, "C:\edb\LanguagePack-10\x64\Python-3.4\bin" does not exist, 
stripping 'bin' off the path is required.

Setting PYTHONHOME for the system is unfriendly to the rest of my python 
dependencies, so to make this specific to pg I edited the pgAdmin 4 shortcut to 
set the variable there, the shortcut target becomes:

C:\Windows\System32\cmd.exe /c "SET PYTHONHOME=C:\edb\languagepack-10\x64\Python-3.4 
&& START  pgAdmin4.exe"

The "Start in" property does not change, the executable is found.

Whether or not this works across the server I guess I'll find out shortly, I'm 
a newbie doing a proof of concept so deployment isn't a concern.

Bruce





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

Re: Why is my Postgre server went in recovery mode all in sudden

[Adrian Klaver]

On 05/09/2018 11:31 AM, nikhil raj wrote:

Hi Team,

I dont have any idea why did my Postgres server crash and it says

timestamp=2018-05-07 00:34:11.209 
EDT,user=,db=,app=,client=,transaction-ID=0,session_start_timestamp=2018-04-26 
10:08:19 EDT,SQL_state=0LOG:  worker process: parallel worker for 
PID 2864 (PID 4476) exited with exit code 0
timestamp=2018-05-07 00:34:11.209 
EDT,user=,db=,app=,client=,transaction-ID=0,session_start_timestamp=2018-04-26 
10:08:19 EDT,SQL_state=0LOG:  terminating any other active server 
processes
timestamp=2018-05-07 00:34:11.209 
EDT,user=postgres,db=Ozalo,app=[unknown],transaction-ID=0,session_start_timestamp=2018-05-07 
00:32:46 EDT,SQL_state=0LOG:  duration: 0.331 ms  bind : 
SELECT "repository"."c_token" AS "token", "repository"."c_path" AS "path"


      FROM

     t_e20so1_repository AS "repository" INNER JOIN 
t_e20so1_document_bigint AS "documentbigint" ON  
"repository"."c_repositoryid" = "documentbigint"."c_value"


       WHERE  "documentbigint"."c_documentid" = 201989

     AND  "documentbigint"."c_fieldid" = 
'b035afc8-439f-4f2c-a9ae-e054052511fd'
timestamp=2018-05-07 00:34:11.210 EDT,user=postgres,db= 
Ozalo,app=[unknown],transaction-ID=0,session_start_timestamp=2018-05-07 
00:32:46 EDT,SQL_state=0LOG:  duration: 0.061 ms  execute : 
SELECT "repository"."c_token" AS "token", "repository"."c_path" AS "path"

  FROM
  t_zs01_sys_dm AS "repository" INNER JOIN t_e20so1_document_bigint AS 
"documentbigint" ON  "repository"."c_repositoryid" = 
"documentbigint"."c_value"

       WHERE  "documentbigint"."c_documentid" = 201989
     AND  "documentbigint"."c_fieldid" = 
'b035afc8-439f-4f2c-a9ae-e054052511fd'



after Some time i was receiving this error


timestamp=2018-05-07 00:34:11.218 EDT,user=postgres,db= Ozalo 
,app=[unknown],transaction-ID=0,session_start_timestamp=2018-05-07 
00:34:10 EDT,SQL_state=57P02WARNING:  terminating connection because of 
crash of another server process
timestamp=2018-05-07 00:34:11.218 EDT,user=postgres,db= Ozalo 
,app=[unknown],transaction-ID=0,session_start_timestamp=2018-05-07 
00:34:10 EDT,SQL_state=57P02DETAIL:  The postmaster has commanded this 
server process to roll back the current transaction and exit, because 
another server process exited abnormally and possibly corrupted shared 
memory.
timestamp=2018-05-07 00:34:11.218 EDT,user=postgres,db= Ozalo 
,app=[unknown],transaction-ID=0,session_start_timestamp=2018-05-07 
00:34:10 EDT,SQL_state=57P02HINT:  In a moment you should be able to 
reconnect to the database and repeat your command.


timestamp=2018-05-07 00:34:11.357 
EDT,user=,db=,app=,client=,transaction-ID=0,session_start_timestamp=2018-04-26 
10:08:20 EDT,SQL_state=57P02DETAIL:  The postmaster has commanded this 
server process to roll back the current transaction and exit, because 
another server process exited abnormally and possibly corrupted shared 
memory.
timestamp=2018-05-07 00:34:11.357 
EDT,user=,db=,app=,client=,transaction-ID=0,session_start_timestamp=2018-04-26 
10:08:20 EDT,SQL_state=57P02HINT:  In a moment you should be able to 
reconnect to the database and repeat your command.
timestamp=2018-05-07 00:34:11.379 EDT,user=postgres,db= 
Ozalo,app=[unknown],,transaction-ID=0,session_start_timestamp=2018-05-07 
00:34:11 EDT,SQL_state=57P03FATAL:  the database system is in recovery mode
timestamp=2018-05-07 00:34:11.381 EDT,user=postgres,db= 
Ozalo,app=[unknown],transaction-ID=0,session_start_timestamp=2018-05-07 
00:34:11 EDT,SQL_state=57P03FATAL:  the database system is in recovery mode




what is the reason it corrupted share memory ?


At this point I don't know. More information is required:

1) What OS and version?

2) System memory size is ?

3) Where was Postgres installed from?

4) What is session_start_timestamp tracking? In other words what does it 
match up to here:


https://www.postgresql.org/docs/10/static/runtime-config-logging.html

log_line_prefix

5) What is the process that started 2018-04-26 10:08:19?

6) The query in the log started at 2018-05-07 00:32:46, what is it doing?




what is meant by The postmaster has commanded this server process to 
roll back the current transaction and exit, because another server 
process exited abnormally and possibly corrupted shared memory. ?

  how much of share memory if its consume  it will crash

  Please can any one help me in this
or else what is the reason of crash of DB server

Current using 10.3
|
  Current Config

max_connections = 5000||
shared_buffers = 7680MB 
effective_cache_size = 23040MB |||

|maintenance_work_mem = 1920MB min_wal_size = 1GB max_wal_size = 2GB 
checkpoint_completion_target = 0.7 wal_buffers = 16MB 
default_statistics_target = 100 random_page_cost = 1.1 
effective_io_concurrency = 200 max_worker_processes = 16 
max_parallel_workers_per_gather = 8 max_parallel_workers = 16 work_mem = 
196kB|




Thanks





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

Re: issues when installing postgres

[Adrian Klaver]

On 05/09/2018 02:47 PM, Antonio Silva wrote:

Hello Adrian

Are you using the Ubuntu or Postgres repos?
 > I'm using the Ubuntu repos

Can you connect to Postgres using psql?
 > No I cannot


What does ps ax | grep post show?

My guess is you are going to have to reinstall Postgres.



Thanks

Antonio


2018-05-09 10:36 GMT-03:00 Adrian Klaver >:


On 05/08/2018 05:54 PM, Antonio Silva wrote:

Hello!


Comments inline.

I bought a new computer and I installed Ubuntu 18.04 and after
PostgreSQL.
sudo apt install postgresql postgresql-contrib pgadmin3


Are you using the Ubuntu or Postgres repos?


Nevertheless I had some issues with configuration files and
decided to
uninstall it completely
sudo apt purge postgresql postgresql-contrib pgadmin3

When I installed it again I notice that postgresql.conf and
pg_hba.conf
were the oldies files. Then I uninstall Postgres once more and
removed the
directory /etc/postgresql/

After a new install I noticed that the directory
/etc/postgresql/ was
completely empty - it was not created again. There are no more
postgresql.conf and pg_hba.conf files. I could find only
postgresql.conf.sample and pg_hba.conf.sample at
/usr/share/postgresql/10

/etc/init.d/postgresql status says that Postgres is running fine


Can you connect to Postgres using psql?


● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled;
vendor
preset: enabled)
Active: active (exited) since Tue 2018-05-08 10:43:23 -03; 1h
55min ago
Process: 6451 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
Main PID: 6451 (code=exited, status=0/SUCCESS)


To be running the server would need its conf files somewhere.


What should I have to do to heve the folder /etc/postgresql/10/
... and all
its files agais?


If you are going to use the packages then yes.




I really appreciate any help. Thanks in advance.

All the best

-- 
Antônio Olinto Ávila da Silva




-- 
Adrian Klaver

adrian.kla...@aklaver.com 





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

Re: New install of 9.5.12 missing default PostgreSQL DB

[Adrian Klaver]

On 05/09/2018 06:56 AM, Chandru Aroor wrote:

Please reply to list also.
Ccing list.

I am used to seeing an instance of the PostgreSQL database once I finish 
installing.  I then restore a copy of the database I need to work with 
into this instance.


Inline image
I am setting up a dev environment, into which I wish to restore a backup 
of a Test instance. However after the install is done and I open 
pgAdmin, this is what I see in the new install. That is the Servers 
group is empty and I (presume) I cannot restore my Test instance here.


Have you tried?:

https://www.pgadmin.org/docs/pgadmin4/3.x/connecting.html

Contents:

The Server Dialog





Inline image


On Wednesday, May 9, 2018, 8:28:44 AM CDT, Adrian Klaver 
 wrote:



On 05/08/2018 06:12 PM, chandru.ar...@yahoo.com 
 wrote:

 > I installed PostgreSQL using PostgreSQL-9.5.12-1-win64-bigsql on a
 > Windows 10 system.  I installed as a local admin.  The installation
 > completed fine, although it failed earlier when I tried to install it as
 > non-admin of the laptop.
 >
 > When I launch pgAdmin, I don't see the default instance of the DB
 > installed on my system.  When I expand the Servers group it is empty.  I

What do you consider the default database?

You need to set up the Server(s):

https://www.pgadmin.org/docs/pgadmin4/3.x/connecting.html


 > went to services to make sure that the PostgreSQL service (PostgreSQL
 > 9.5 Server) was running. I restarted the service too.
 >
 >
 > What an I missing?  Appreciate any help on this.
 >
 > Thanks,
 >
 > Chandru

 >
 >


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





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

Re: issues when installing postgres

[Tim Cross]

On 10 May 2018 at 09:45, Adrian Klaver  wrote:

> On 05/09/2018 02:47 PM, Antonio Silva wrote:
>
>> Hello Adrian
>>
>> Are you using the Ubuntu or Postgres repos?
>>  > I'm using the Ubuntu repos
>>
>> Can you connect to Postgres using psql?
>>  > No I cannot
>>
>
> What does ps ax | grep post show?
>
> My guess is you are going to have to reinstall Postgres.
>
>
>> Thanks
>>
>> Antonio
>>
>>
>> 2018-05-09 10:36 GMT-03:00 Adrian Klaver > >:
>>
>>
>> On 05/08/2018 05:54 PM, Antonio Silva wrote:
>>
>> Hello!
>>
>>
>> Comments inline.
>>
>> I bought a new computer and I installed Ubuntu 18.04 and after
>> PostgreSQL.
>> sudo apt install postgresql postgresql-contrib pgadmin3
>>
>>
>> Are you using the Ubuntu or Postgres repos?
>>
>>
>> Nevertheless I had some issues with configuration files and
>> decided to
>> uninstall it completely
>> sudo apt purge postgresql postgresql-contrib pgadmin3
>>
>> When I installed it again I notice that postgresql.conf and
>> pg_hba.conf
>> were the oldies files. Then I uninstall Postgres once more and
>> removed the
>> directory /etc/postgresql/
>>
>> After a new install I noticed that the directory
>> /etc/postgresql/ was
>> completely empty - it was not created again. There are no more
>> postgresql.conf and pg_hba.conf files. I could find only
>> postgresql.conf.sample and pg_hba.conf.sample at
>> /usr/share/postgresql/10
>>
>> /etc/init.d/postgresql status says that Postgres is running fine
>>
>>
>> Can you connect to Postgres using psql?
>>
>>
>> ● postgresql.service - PostgreSQL RDBMS
>> Loaded: loaded (/lib/systemd/system/postgresql.service; enabled;
>> vendor
>> preset: enabled)
>> Active: active (exited) since Tue 2018-05-08 10:43:23 -03; 1h
>> 55min ago
>> Process: 6451 ExecStart=/bin/true (code=exited, status=0/SUCCESS)
>> Main PID: 6451 (code=exited, status=0/SUCCESS)
>>
>>
>> To be running the server would need its conf files somewhere.
>>
>>
>> What should I have to do to heve the folder /etc/postgresql/10/
>> ... and all
>> its files agais?
>>
>>
>> If you are going to use the packages then yes.
>>
>>
>>
>>
>> I really appreciate any help. Thanks in advance.
>>
>> All the best
>>
>> -- Antônio Olinto Ávila da Silva
>>
>>
>>
>> -- Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
Probably not relevant, but I noticed your also installing pgadmin3. I don't
believe pgadmin3 will work with Postgres 10. You need pgadmin4, which isn't
available as a package on ubuntu 18.04.

Also, be aware that Ubuntu has also been pushing 'snaps', so make sure that
Postgres hasn't been installed as a snap package (I think the command is
snap lis). I recall when I tried to install postgres in 17.10, which I did
from the 'software centre' on ubuntu, it initially installed it as a snap.
I had to remove the snap and then use apt to get the normal deb package
install.  the snap package system does not use the normal locations for
config files.

This could also be a ubuntu 18.04 issue. This version was only released a
couple of weeks ago and it is the first version which has Postgres 10 as
the default.  I would try the following

1. use systemctl to stop postgresql service
2. remove all postgres packages making sure all config files are also
removed
3. Use synaptic to make sure all postgres package and associated config
files have been removed.
4. Reboot
5. run apt update and then apt upgrade
6 re-install using apt (not the software centre).



-- 
regards,

Tim

--
Tim Cross

ON CONFLICT DO UPDATE

[tango ward]

Hi,

Sorry for asking question again.

I would like to know if there's a workaround for this. I need to insert
Student Balance data into a table. The source data have duplicate values
for student_id, school_id and campus_name. My StudentBalance model in Djano
have Class Meta of:

class Meta:
unique_together = (
"school",
"student_id",
"campus_name"
)

Searched online and found this magical tool called ON CONFLICT DO UPDATE. I
played around with it, made it work but there's a problem. The balance data
is not being updated which is because it's not in class Meta with
unique_together. I would like to know if there's a way to update the data
in Student Balance column without adding it to the class Meta?


Thanks,
J

Re: New install of 9.5.12 missing default PostgreSQL DB

[Chandru Aroor]

I don't even have a server to connect to and that is my problem. 
 

On Wednesday, May 9, 2018, 6:48:40 PM CDT, Adrian Klaver 
 wrote:  
 
 On 05/09/2018 06:56 AM, Chandru Aroor wrote:

Please reply to list also.
Ccing list.

> I am used to seeing an instance of the PostgreSQL database once I finish 
> installing.  I then restore a copy of the database I need to work with 
> into this instance.
> 
> Inline image
> I am setting up a dev environment, into which I wish to restore a backup 
> of a Test instance. However after the install is done and I open 
> pgAdmin, this is what I see in the new install. That is the Servers 
> group is empty and I (presume) I cannot restore my Test instance here.

Have you tried?:

https://www.pgadmin.org/docs/pgadmin4/3.x/connecting.html

Contents:

    The Server Dialog


> 
> 
> Inline image
> 
> 
> On Wednesday, May 9, 2018, 8:28:44 AM CDT, Adrian Klaver 
>  wrote:
> 
> 
> On 05/08/2018 06:12 PM, chandru.ar...@yahoo.com 
>  wrote:
>  > I installed PostgreSQL using PostgreSQL-9.5.12-1-win64-bigsql on a
>  > Windows 10 system.  I installed as a local admin.  The installation
>  > completed fine, although it failed earlier when I tried to install it as
>  > non-admin of the laptop.
>  >
>  > When I launch pgAdmin, I don't see the default instance of the DB
>  > installed on my system.  When I expand the Servers group it is empty.  I
> 
> What do you consider the default database?
> 
> You need to set up the Server(s):
> 
> https://www.pgadmin.org/docs/pgadmin4/3.x/connecting.html
> 
> 
>  > went to services to make sure that the PostgreSQL service (PostgreSQL
>  > 9.5 Server) was running. I restarted the service too.
>  >
>  >
>  > What an I missing?  Appreciate any help on this.
>  >
>  > Thanks,
>  >
>  > Chandru
> 
>  >
>  >
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com 
> 
> 


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

Re: New install of 9.5.12 missing default PostgreSQL DB

[Adrian Klaver]

On 05/09/2018 07:12 PM, Chandru Aroor wrote:

I don't even have a server to connect to and that is my problem.


I thought you said earlier that the Postgres service was running.

Is it running?





On Wednesday, May 9, 2018, 6:48:40 PM CDT, Adrian Klaver 
 wrote:



On 05/09/2018 06:56 AM, Chandru Aroor wrote:

Please reply to list also.
Ccing list.

 > I am used to seeing an instance of the PostgreSQL database once I finish
 > installing.  I then restore a copy of the database I need to work with
 > into this instance.
 >
 > Inline image
 > I am setting up a dev environment, into which I wish to restore a backup
 > of a Test instance. However after the install is done and I open
 > pgAdmin, this is what I see in the new install. That is the Servers
 > group is empty and I (presume) I cannot restore my Test instance here.

Have you tried?:

https://www.pgadmin.org/docs/pgadmin4/3.x/connecting.html

Contents:

     The Server Dialog


 >
 >
 > Inline image
 >
 >
 > On Wednesday, May 9, 2018, 8:28:44 AM CDT, Adrian Klaver
 > mailto:adrian.kla...@aklaver.com>> wrote:
 >
 >
 > On 05/08/2018 06:12 PM, chandru.ar...@yahoo.com 


 > > wrote:
 >  > I installed PostgreSQL using PostgreSQL-9.5.12-1-win64-bigsql on a
 >  > Windows 10 system.  I installed as a local admin.  The installation
 >  > completed fine, although it failed earlier when I tried to install 
it as

 >  > non-admin of the laptop.
 >  >
 >  > When I launch pgAdmin, I don't see the default instance of the DB
 >  > installed on my system.  When I expand the Servers group it is 
empty.  I

 >
 > What do you consider the default database?
 >
 > You need to set up the Server(s):
 >
 > https://www.pgadmin.org/docs/pgadmin4/3.x/connecting.html
 >
 >
 >  > went to services to make sure that the PostgreSQL service (PostgreSQL
 >  > 9.5 Server) was running. I restarted the service too.
 >  >
 >  >
 >  > What an I missing?  Appreciate any help on this.
 >  >
 >  > Thanks,
 >  >
 >  > Chandru
 >
 >  >
 >  >
 >
 >
 > --
 > Adrian Klaver
 > adrian.kla...@aklaver.com  
>


 >
 >


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



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

Re: New install of 9.5.12 missing default PostgreSQL DB

[Chandru Aroor]

 Yes, the service shows as running. But I don't have a Server to connect to! 

On Wednesday, May 9, 2018, 9:43:02 PM CDT, Adrian Klaver 
 wrote:  
 
 On 05/09/2018 07:12 PM, Chandru Aroor wrote:
> I don't even have a server to connect to and that is my problem.

I thought you said earlier that the Postgres service was running.

Is it running?


> 
> 
> On Wednesday, May 9, 2018, 6:48:40 PM CDT, Adrian Klaver 
>  wrote:
> 
> 
> On 05/09/2018 06:56 AM, Chandru Aroor wrote:
> 
> Please reply to list also.
> Ccing list.
> 
>  > I am used to seeing an instance of the PostgreSQL database once I finish
>  > installing.  I then restore a copy of the database I need to work with
>  > into this instance.
>  >
>  > Inline image
>  > I am setting up a dev environment, into which I wish to restore a backup
>  > of a Test instance. However after the install is done and I open
>  > pgAdmin, this is what I see in the new install. That is the Servers
>  > group is empty and I (presume) I cannot restore my Test instance here.
> 
> Have you tried?:
> 
> https://www.pgadmin.org/docs/pgadmin4/3.x/connecting.html
> 
> Contents:
> 
>      The Server Dialog
> 
> 
>  >
>  >
>  > Inline image
>  >
>  >
>  > On Wednesday, May 9, 2018, 8:28:44 AM CDT, Adrian Klaver
>  > mailto:adrian.kla...@aklaver.com>> wrote:
>  >
>  >
>  > On 05/08/2018 06:12 PM, chandru.ar...@yahoo.com 
> 
>  > > wrote:
>  >  > I installed PostgreSQL using PostgreSQL-9.5.12-1-win64-bigsql on a
>  >  > Windows 10 system.  I installed as a local admin.  The installation
>  >  > completed fine, although it failed earlier when I tried to install 
> it as
>  >  > non-admin of the laptop.
>  >  >
>  >  > When I launch pgAdmin, I don't see the default instance of the DB
>  >  > installed on my system.  When I expand the Servers group it is 
> empty.  I
>  >
>  > What do you consider the default database?
>  >
>  > You need to set up the Server(s):
>  >
>  > https://www.pgadmin.org/docs/pgadmin4/3.x/connecting.html
>  >
>  >
>  >  > went to services to make sure that the PostgreSQL service (PostgreSQL
>  >  > 9.5 Server) was running. I restarted the service too.
>  >  >
>  >  >
>  >  > What an I missing?  Appreciate any help on this.
>  >  >
>  >  > Thanks,
>  >  >
>  >  > Chandru
>  >
>  >  >
>  >  >
>  >
>  >
>  > --
>  > Adrian Klaver
>  > adrian.kla...@aklaver.com  
> >
> 
>  >
>  >
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com 


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

  

Re: New install of 9.5.12 missing default PostgreSQL DB

[David G. Johnston]

On Wed, May 9, 2018 at 8:05 PM, Chandru Aroor  wrote:

> Yes, the service shows as running. But I don't have a Server to connect
> to!
>

​I have to imagine you can add a server to pgAdmin...try host: localhost
and port: 5432

David J.

Re: New install of 9.5.12 missing default PostgreSQL DB

[Chandru Aroor]

That did it! Thanks much!

> On May 9, 2018, at 10:31 PM, David G. Johnston  
> wrote:
> 
>> On Wed, May 9, 2018 at 8:05 PM, Chandru Aroor  wrote:
> 
>> Yes, the service shows as running. But I don't have a Server to connect to! 
> 
> ​I have to imagine you can add a server to pgAdmin...try host: localhost and 
> port: 5432
> 
> David J.
> 

Re: ON CONFLICT DO UPDATE

[Adrian Klaver]

On 05/09/2018 07:04 PM, tango ward wrote:


Hi,

Sorry for asking question again.

I would like to know if there's a workaround for this. I need to insert 
Student Balance data into a table. The source data have duplicate values 
for student_id, school_id and campus_name. My StudentBalance model in 
Djano have Class Meta of:


class Meta:
     unique_together = (
     "school",
     "student_id",
     "campus_name"
     )

Searched online and found this magical tool called ON CONFLICT DO 
UPDATE. I played around with it, made it work but there's a problem. The 
balance data is not being updated which is because it's not in class 
Meta with unique_together. I would like to know if there's a way to 
update the data in Student Balance column without adding it to the class 
Meta?


I doubt that Meta has anything to do with the balance not being updated. 
What the Meta does is set up a UNIQUE index over the school, student_id 
and campus_name columns. I am going to say the issue is with ON CONFLICT 
DO UPDATE clause you create on the table. To help solve this we need:


1) The schema of the table StudentBalance points to.

2) The ON CONFLICT DO UPDATE clause you created on the table.




Thanks,
J



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

Re: ON CONFLICT DO UPDATE

[tango ward]

Sorry, I modified the school_system_id in CONFLICT CLAUSE.

On Thu, May 10, 2018 at 12:07 PM, Adrian Klaver 
wrote:

> On 05/09/2018 07:04 PM, tango ward wrote:
>
>>
>> Hi,
>>
>> Sorry for asking question again.
>>
>> I would like to know if there's a workaround for this. I need to insert
>> Student Balance data into a table. The source data have duplicate values
>> for student_id, school_id and campus_name. My StudentBalance model in Djano
>> have Class Meta of:
>>
>> class Meta:
>>  unique_together = (
>>  "school",
>>  "student_id",
>>  "campus_name"
>>  )
>>
>> Searched online and found this magical tool called ON CONFLICT DO UPDATE.
>> I played around with it, made it work but there's a problem. The balance
>> data is not being updated which is because it's not in class Meta with
>> unique_together. I would like to know if there's a way to update the data
>> in Student Balance column without adding it to the class Meta?
>>
>
> I doubt that Meta has anything to do with the balance not being updated.
> What the Meta does is set up a UNIQUE index over the school, student_id and
> campus_name columns. I am going to say the issue is with ON CONFLICT DO
> UPDATE clause you create on the table. To help solve this we need:
>
> 1) The schema of the table StudentBalance points to.
>
> 2) The ON CONFLICT DO UPDATE clause you created on the table.
>
>
>>
>> Thanks,
>> J
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: ON CONFLICT DO UPDATE

[tango ward]

Sorry Sir Adrian, updating the code.

for row in cur_tdc:
print row['studentnumber'], row['firstname'], row['lastname'],
row['currentbalance']
cur_phil.execute("""
 INSERT INTO recipients_studentbalance(
created, modified, student_id,
first_name, middle_name,
last_name, campus_name,
year_level, section,
balance, balance_as_of,
school_system_id
  )
 VALUES (current_timestamp,
 current_timestamp,
 %s, %s, %s, %s, %s,
 %s, %s, %s, current_date,
 (SELECT id
  FROM education_schoolsystem
  WHERE name='My Test School.')
 )
 ON CONFLICT (school_system_id,
  student_id,
  campus_name
  ) DO UPDATE
  SET school_system_id =
excluded.school_system_id,
  student_id = excluded.student_id,
  campus_name = excluded.campus_name
""", (row['studentnumber'], row['firstname'],
  row['middlename'], row['lastname'],
  'My Test Schol.',
  row['yearlevel'], row['section'],
  row['currentbalance']))

On Thu, May 10, 2018 at 12:07 PM, Adrian Klaver 
wrote:

> On 05/09/2018 07:04 PM, tango ward wrote:
>
>>
>> Hi,
>>
>> Sorry for asking question again.
>>
>> I would like to know if there's a workaround for this. I need to insert
>> Student Balance data into a table. The source data have duplicate values
>> for student_id, school_id and campus_name. My StudentBalance model in Djano
>> have Class Meta of:
>>
>> class Meta:
>>  unique_together = (
>>  "school",
>>  "student_id",
>>  "campus_name"
>>  )
>>
>> Searched online and found this magical tool called ON CONFLICT DO UPDATE.
>> I played around with it, made it work but there's a problem. The balance
>> data is not being updated which is because it's not in class Meta with
>> unique_together. I would like to know if there's a way to update the data
>> in Student Balance column without adding it to the class Meta?
>>
>
> I doubt that Meta has anything to do with the balance not being updated.
> What the Meta does is set up a UNIQUE index over the school, student_id and
> campus_name columns. I am going to say the issue is with ON CONFLICT DO
> UPDATE clause you create on the table. To help solve this we need:
>
> 1) The schema of the table StudentBalance points to.
>
> 2) The ON CONFLICT DO UPDATE clause you created on the table.
>
>
>>
>> Thanks,
>> J
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: ON CONFLICT DO UPDATE

[tango ward]

Okay, I think I manage to solve it by adding balance = excluded.balance
inside the parenthesis of ON CONFLICT clause.

On Thu, May 10, 2018 at 1:13 PM, tango ward  wrote:

> Sorry Sir Adrian, updating the code.
>
> for row in cur_tdc:
> print row['studentnumber'], row['firstname'], row['lastname'],
> row['currentbalance']
> cur_phil.execute("""
>  INSERT INTO recipients_studentbalance(
> created, modified, student_id,
> first_name, middle_name,
> last_name, campus_name,
> year_level, section,
> balance, balance_as_of,
> school_system_id
>   )
>  VALUES (current_timestamp,
>  current_timestamp,
>  %s, %s, %s, %s, %s,
>  %s, %s, %s, current_date,
>  (SELECT id
>   FROM education_schoolsystem
>   WHERE name='My Test School.')
>  )
>  ON CONFLICT (school_system_id,
>   student_id,
>   campus_name
>   ) DO UPDATE
>   SET school_system_id =
> excluded.school_system_id,
>   student_id = excluded.student_id,
>   campus_name = excluded.campus_name
> """, (row['studentnumber'], row['firstname'],
>   row['middlename'], row['lastname'],
>   'My Test Schol.',
>   row['yearlevel'], row['section'],
>   row['currentbalance']))
>
> On Thu, May 10, 2018 at 12:07 PM, Adrian Klaver  > wrote:
>
>> On 05/09/2018 07:04 PM, tango ward wrote:
>>
>>>
>>> Hi,
>>>
>>> Sorry for asking question again.
>>>
>>> I would like to know if there's a workaround for this. I need to insert
>>> Student Balance data into a table. The source data have duplicate values
>>> for student_id, school_id and campus_name. My StudentBalance model in Djano
>>> have Class Meta of:
>>>
>>> class Meta:
>>>  unique_together = (
>>>  "school",
>>>  "student_id",
>>>  "campus_name"
>>>  )
>>>
>>> Searched online and found this magical tool called ON CONFLICT DO
>>> UPDATE. I played around with it, made it work but there's a problem. The
>>> balance data is not being updated which is because it's not in class Meta
>>> with unique_together. I would like to know if there's a way to update the
>>> data in Student Balance column without adding it to the class Meta?
>>>
>>
>> I doubt that Meta has anything to do with the balance not being updated.
>> What the Meta does is set up a UNIQUE index over the school, student_id and
>> campus_name columns. I am going to say the issue is with ON CONFLICT DO
>> UPDATE clause you create on the table. To help solve this we need:
>>
>> 1) The schema of the table StudentBalance points to.
>>
>> 2) The ON CONFLICT DO UPDATE clause you created on the table.
>>
>>
>>>
>>> Thanks,
>>> J
>>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>
>