Re: [GENERAL] Unable to start postgresql

[Tom Lane]

John Iliffe  writes:
> On Wednesday 08 March 2017 23:35:10 Tom Lane wrote:
>> That isn't proving a lot: as I showed in my example lsof output,
>> Fedora's lsof will map "5432" to "postgres" in the context of an IP
>> port number. (I'm sure there's a way to turn that off, but -n ain't
>> it.)

> Yes, but your lsof output also showed a line for postmaster and mine 
> doesn't.

That's because I started mine by saying "postmaster" not "postgres".
It's not real relevant, just ancient habit of mine.

> In your case postmaster has an IPv6 TCP socket (but no IPv4 I 
> notice)

Uh, what?  I showed an IPv6, an IPv4, and a Unix socket.

> The following is from ss, the new version of netstat:
> 
> tcpLISTEN 0  128127.0.0.1:postgres  *:*
> tcpLISTEN 0  128   ::1:postgres :::*
> 

Well, that's pretty interesting, because it proves that *something* has
got IPv4 port 5432 open.  If not your manually-started postmaster, then
what?  You need to inquire into that a bit harder.  Running lsof as root
and examining all processes might help.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] too may LWLocks

[hariprasath nallasamy]

oops its my bad implementation.. I was leaking locks and its fixed now.
Thanks for the help!

-harry

On Thu, Mar 9, 2017 at 1:07 AM, Julien Rouhaud 
wrote:

> On Wed, Mar 08, 2017 at 03:34:56PM +0530, hariprasath nallasamy wrote:
> > Hi all
> > I am building an extension using shared memory hash table and for locking
> > hash table i am using LWLocks, but the thing was when i try to run some
> 1k
> > queries one after other, for each query i am getting one LWLock but on
> > executing 200th query i am getting the error *ERROR:  too many LWLocks
> > taken*.
> >
> > But in each query i acquire and release that block. So that lock has to
> be
> > flushed after executing query, but why am i getting this error.?
> >
> > Is this due to *held_lwlocks *in LWLock.c is fixed only to some number
> 200
> > here.
> > Or am i missing something here.?
>
> The most likely reason is that you have some code path in your extension
> where
> you don't release the LWLock.  Without access to the code we can't do much
> more
> to help you I'm afraid.  You could also try on a postgres build having
> LWLOCK_STATS defined.
>
> --
> Julien Rouhaud
> http://dalibo.com - http://dalibo.org
>

Re: [GENERAL] Unable to start postgresql

[John Iliffe]

On Wednesday 08 March 2017 23:35:10 Tom Lane wrote:
> John Iliffe  writes:
> > [root@prod04 John]# lsof -n | grep postmaster
> > [root@prod04 John]# lsof -n | grep postgres | grep 5432
> > postgres  2760  postgres5u unix 0x9e5390b5b800  
> > 0t0
> > 
> >  69422 /tmp/.s.PGSQL.5432 type=STREAM
> 
> That isn't proving a lot: as I showed in my example lsof output,
> Fedora's lsof will map "5432" to "postgres" in the context of an IP
> port number. (I'm sure there's a way to turn that off, but -n ain't
> it.)
> 
>   regards, tom lane

Yes, but your lsof output also showed a line for postmaster and mine 
doesn't.  In your case postmaster has an IPv6 TCP socket (but no IPv4 I 
notice) whereas mine has neither.  

The postgres output seems to be equivalent.

As for lsof, I have searched the man page to try and find appropriate 
parameters for when all I want to know about is IP sockets but to date no 
joy.

The following is from ss, the new version of netstat:


tcpLISTEN 0  128127.0.0.1:postgres  *:* 
   
tcpLISTEN 0  128   ::1:postgres :::* 


Unfortunately a request to resolve the socket numbers (ss -rl) fails.  

What I was able to get is the SELinux context of these sockets but frankly, 
it means nothing to me at his point.  With SELinux in permissive mode it 
shouldn't be relevant, but just in case it helps you, here it is:

---
[root@prod04 John]# ss -aZ | grep post
u_str  LISTEN 0  128/tmp/.s.PGSQL.5432 69422 * 
0 users:
(("postgres",pid=2760,proc_ctx=unconfined_u:unconfined_r:unconfined_t:s0-
s0:c0.c1023,fd=5))
udpESTAB  0  0 ::1:49481   ::1:49481
 
users:
(("postgres",pid=2766,proc_ctx=unconfined_u:unconfined_r:unconfined_t:s0-
s0:c0.c1023,fd=8),
("postgres",pid=2765,proc_ctx=unconfined_u:unconfined_r:unconfined_t:s0-
s0:c0.c1023,fd=8),
("postgres",pid=2764,proc_ctx=unconfined_u:unconfined_r:unconfined_t:s0-
s0:c0.c1023,fd=8),
("postgres",pid=2763,proc_ctx=unconfined_u:unconfined_r:unconfined_t:s0-
s0:c0.c1023,fd=8),
("postgres",pid=2762,proc_ctx=unconfined_u:unconfined_r:unconfined_t:s0-
s0:c0.c1023,fd=8),
("postgres",pid=2760,proc_ctx=unconfined_u:unconfined_r:unconfined_t:s0-
s0:c0.c1023,fd=8))
tcpLISTEN 0  128127.0.0.1:postgres  *:* 

users:
(("postgres",pid=2760,proc_ctx=unconfined_u:unconfined_r:unconfined_t:s0-
s0:c0.c1023,fd=4))
tcpLISTEN 0  128   ::1:postgres :::*
 
users:
(("postgres",pid=2760,proc_ctx=unconfined_u:unconfined_r:unconfined_t:s0-
s0:c0.c1023,fd=3))
---

Regards,

John





-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] count case when - PG 9.2

[David G. Johnston]

On Wednesday, March 8, 2017, Patrick B  wrote:

> Hi guys. How can I count using 'CASE WHEN'?
>
> Example:
>
> SELECT
>
> CASE
>
> WHEN AND c.regdate > EXTRACT(epoch FROM (now() - INTERVAL '14
> day'))
>
> THEN 'trial'
>
> WHEN last_pay > EXTRACT(epoch FROM (now() - INTERVAL '37 day'))
>
> THEN 'paying'
>
> END as account_status,
>
> c.id
>
> FROM public.clients c
>
> WHERE (
>
>(last_pay > EXTRACT('epoch' FROM now() - '12
> Months'::INTERVAL))
>
>   )
>
> ORDER BY 1
>
> I wanna know how many of 'trial' and 'paying' customers the query returns.
> can you guys please advice how to do it?
>
> Thanks
> Patrick
>

Turn that into a subquery and group by...

David J.

Re: [GENERAL] Which release does a commit end up getting into?

[Michael Paquier]

On Thu, Mar 9, 2017 at 1:41 PM,   wrote:
> I'm keen on using the functionality in the commit dated Jan 2, 2017 below.
>
> Probing the git repo however it exists only in the master branch and not 
> tagged in a release.  The most recent release, tag REL9_6_2, contains commits 
> after Jan 2, but does not contain this commit.  The commit appears to have 
> been inserted into the 2017-01 CommitFest [2].
>
> How are commits filtered into releases?  i.e. when can we expect this commit 
> to appear in a release?
>
> [1] 
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=de41869b64d57160f58852eab20a27f248188135
>
> commit de41869b64d57160f58852eab20a27f248188135
> Author: Tom Lane 
> Date:   Mon Jan 2 21:37:12 2017 -0500
>
> Allow SSL configuration to be updated at SIGHUP.
>
> [2] https://commitfest.postgresql.org/12/863/

You may be confused by the particular branching model that Postgres
uses in its git repository. There is one master branch where all the
latest development goes, and a set of stable branches, one per major
version. Community maintaining a major version for 5 years, there are
up to 6 branches maintained at the same time. Patches are also not
merged from the master to the stable branches, they are cherry-picked
by committers, which makes the story of the project easier to follow
(there are usually small conflicts as well).

In short you will need to wait for Postgres 10 before using this
feature, which will hopefully be released next September if everything
is on tracks. Note that in stable branches no new features are
allowed, only bug fixes are accepted. Nothing prevents you to fork the
9.2 branch and apply this commit on a custom build, just it won't be
supported by community per its forking nature.
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Which release does a commit end up getting into?

[jpap00]

Hello all,

I'm keen on using the functionality in the commit dated Jan 2, 2017 below.

Probing the git repo however it exists only in the master branch and not tagged 
in a release.  The most recent release, tag REL9_6_2, contains commits after 
Jan 2, but does not contain this commit.  The commit appears to have been 
inserted into the 2017-01 CommitFest [2].

How are commits filtered into releases?  i.e. when can we expect this commit to 
appear in a release?

Thank you,
John


[1] 
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=de41869b64d57160f58852eab20a27f248188135

commit de41869b64d57160f58852eab20a27f248188135
Author: Tom Lane 
Date:   Mon Jan 2 21:37:12 2017 -0500

Allow SSL configuration to be updated at SIGHUP.

[2] https://commitfest.postgresql.org/12/863/



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unable to start postgresql

[Tom Lane]

John Iliffe  writes:
> [root@prod04 John]# lsof -n | grep postmaster
> [root@prod04 John]# lsof -n | grep postgres | grep 5432
> postgres  2760  postgres5u unix 0x9e5390b5b800   0t0 
>  69422 /tmp/.s.PGSQL.5432 type=STREAM

That isn't proving a lot: as I showed in my example lsof output, Fedora's
lsof will map "5432" to "postgres" in the context of an IP port number.
(I'm sure there's a way to turn that off, but -n ain't it.)

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] import CSV file to a table

[Günce Kaya]

Hi,

When I open a new thread, I didn't know exactly what is true words to
research. I read all of your posts and I think CSV parsing is the point to
me. I've created my script and I share it to record.

#cat cargo2.sh
#!/bin/bash
while IFS=, read uor_desc crime_type zip_code ucr_hierarchy date_reported
date_occured
do

echo "select * from insertorders('$uor_desc', '$crime_type', '$zip_code',
'$fucr_hierarchy', '$date_reported', '$date_occured');"

done < test.txt;

Thank you for your advices and helps.

Regards,

Gunce

2017-03-08 20:00 GMT+03:00 Rob Sargent :

>
>
> On 03/08/2017 09:52 AM, Karl Czajkowski wrote:
>
>> On Mar 08, Rob Sargent modulated:
>>
>> Yes Karl, I agree.  I admitted as much.  But if it's clean, as in
>>> free of quoted commas, life is much more simple.  I've lost site of
>>> whether or not the OP knows his situation w.r.t. to this.  The awk
>>> line will tell him and for a one-off load this can make a world of
>>> difference in complexity - two bash lines and a COPY.
>>>
>>> Maybe I didn't understand your awk? I thought it was counting commas
>> in lines.  This isn't the same as counting commas in records.
>>
>>  this,is,record,one
>>  "this,,","is
>>  ,,record","two
>>  ,,,"
>>
>> this has three commas on each line and definitely is not suitable
>> for naive CSV handling.
>>
>>
>> Karl
>>
> In essence it does count commas but plus one :).  $NF is number of fields
> defined by commas so one more field than number of commas. If you
> think/hope the file is simple and well formatted, this is a pretty quick
> check. But if you're looking for a general solution, you need a real csv
> parser.  I recall being quite surprised and amused to learn there is an
> actual standard for csv format. (Naturally if you have one to hand, you
> don't need the awk line.)
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Gunce Kaya

[GENERAL] count case when - PG 9.2

[Patrick B]

Hi guys. How can I count using 'CASE WHEN'?

Example:

SELECT

CASE

WHEN AND c.regdate > EXTRACT(epoch FROM (now() - INTERVAL '14 day'))

THEN 'trial'

WHEN last_pay > EXTRACT(epoch FROM (now() - INTERVAL '37 day'))

THEN 'paying'

END as account_status,

c.id

FROM public.clients c

WHERE (

   (last_pay > EXTRACT('epoch' FROM now() - '12
Months'::INTERVAL))

  )

ORDER BY 1

I wanna know how many of 'trial' and 'paying' customers the query returns.
can you guys please advice how to do it?

Thanks
Patrick

Re: [GENERAL] Unable to start postgresql

[John Iliffe]

On Wednesday 08 March 2017 17:22:21 Adrian Klaver wrote:
> On 03/08/2017 01:48 PM, John Iliffe wrote:
> > On Wednesday 08 March 2017 15:13:29 Tom Lane wrote:
> >> John Iliffe  writes:
> >>> I tried psql but it won't work, as expected, because socket 5432 is
> >>> not available.
> >> 
> >> Actually, that's not all that expected.  psql by default would try to
> >> connect via a Unix socket, so it wouldn't matter whether or not the
> >> postmaster had been able to open an IPv4 port.  The most likely
> >> reason for failing to connect via Unix socket is looking in the
> >> wrong directory for the socket, viz "/tmp" vs "/var/run/postgresql",
> >> which is why Adrian is pressing you about other Postgres
> >> installations on the machine.  If your psql session is using a Red
> >> Hat-supplied libpq.so then it will likely look in
> >> /var/run/postgresql, whereas this stock-sources postmaster is going
> >> to have put it in /tmp by default.  (You could adjust the
> >> unix_socket_directories parameter to fix that.)  It would also help
> >> to pay close attention to the error message psql gives when it fails
> >> to connect.
> >> 
> >> Even if you are doing "psql -h localhost", I'm pretty sure
> >> "localhost" will resolve as IPv6 not IPv4 (ie ::1 not 127.0.0.1) on
> >> Fedora 25 --- it does on mine.  So if the postmaster successfully
> >> opened an IPv6 port, which I think it would do by default, then it
> >> still wouldn't matter that the IPv4 port wasn't there; the issue
> >> should still be masked.
> >> 
> >> FWIW, this is what I see for network sockets when lsof'ing a stock
> >> postmaster on current Fedora 25:
> >> 
> >> ...
> >> postmaste 20082  tgl3u  IPv6  37256  0t0 TCP
> >> localhost:postgres (LISTEN) postmaste 20082  tgl4u  IPv4
> >> 
> >>   37257  0t0 TCP localhost:postgres (LISTEN) postmaste 20082
> >> 
> >> tgl5u  unix 0x9eb3435cfc00  0t0   37259
> >> /tmp/.s.PGSQL.5432 type=STREAM ...
> >> 
> >> or with -n it looks like
> >> 
> >> postmaste 20082  tgl3u  IPv6  37256  0t0 TCP
> >> [::1]:postgres (LISTEN) postmaste 20082  tgl4u  IPv4
> >> 37257  0t0 TCP 127.0.0.1:postgres (LISTEN) postmaste 20082 
> >> tgl
> >> 
> >>5u  unix 0x9eb3435cfc00  0t0   37259 /tmp/.s.PGSQL.5432
> >> 
> >> type=STREAM
> >> 
> >> What I am suspicious of at this point is that the root of the problem
> >> is networking misconfiguration on your machine, such that IPv4
> >> doesn't work at all; given the platform's bias towards IPv6 for
> >> loopback, you might not have noticed otherwise.  You might check
> >> what results you get from "ping ::1" vs "ping 127.0.0.1" vs "ping
> >> localhost".
> >> 
> >>regards, tom lane
> > 
> > Hi Tom:
> > 
> > By now you have probably seen that I did get PSQL to run.  I had
> > assumed that meant that everything was at least running if not as
> > expected, at least running.  But I did an lsof and I don't get
> > anything at all for postmaster.  postgres has a lot of open files but
> > they are libraries and so forth so not applicable to this situation.
> > 
> > I did the pings you suggest with the following results:
> > 
> > ---
> > [root@prod04 John]# ping localhost
> > PING localhost(localhost (::1)) 56 data bytes
> > 64 bytes from localhost (::1): icmp_seq=1 ttl=64 time=0.078 ms
> > 64 bytes from localhost (::1): icmp_seq=2 ttl=64 time=0.060 ms
> > 64 bytes from localhost (::1): icmp_seq=3 ttl=64 time=0.059 ms
> > 64 bytes from localhost (::1): icmp_seq=4 ttl=64 time=0.064 ms
> > 64 bytes from localhost (::1): icmp_seq=5 ttl=64 time=0.059 ms
> > ^C
> > --- localhost ping statistics ---
> > 5 packets transmitted, 5 received, 0% packet loss, time 4084ms
> > rtt min/avg/max/mdev = 0.059/0.064/0.078/0.007 ms
> > [root@prod04 John]#
> > [root@prod04 John]# ping ::1
> > PING ::1(::1) 56 data bytes
> > 64 bytes from ::1: icmp_seq=1 ttl=64 time=0.074 ms
> > 64 bytes from ::1: icmp_seq=2 ttl=64 time=0.059 ms
> > 64 bytes from ::1: icmp_seq=3 ttl=64 time=0.059 ms
> > 64 bytes from ::1: icmp_seq=4 ttl=64 time=0.060 ms
> > 64 bytes from ::1: icmp_seq=5 ttl=64 time=0.059 ms
> > ^C
> > --- ::1 ping statistics ---
> > 5 packets transmitted, 5 received, 0% packet loss, time 4108ms
> > rtt min/avg/max/mdev = 0.059/0.062/0.074/0.007 ms
> > [root@prod04 John]#
> > [root@prod04 John]#
> > [root@prod04 John]# ping 127.0.0.1
> > PING 127.0.0.1 (127.0.0.1) 56(84) bytes of data.
> > 64 bytes from 127.0.0.1: icmp_seq=1 ttl=64 time=0.057 ms
> > 64 bytes from 127.0.0.1: icmp_seq=2 ttl=64 time=0.045 ms
> > 64 bytes from 127.0.0.1: icmp_seq=3 ttl=64 time=0.046 ms
> > 64 bytes from 127.0.0.1: icmp_seq=4 ttl=64 time=0.049 ms
> > ^C
> > --- 127.0.0.1 ping statistics ---
> > 4 packets transmitted, 4 received, 0% packet loss, time 3066ms
> > rtt min/avg/max/mdev = 0.045/0.049/0.057/0.006 ms
> > [root@prod04 John]# ping 192.168.1.8   <--- current 

Re: [GENERAL] Unable to start postgresql

[John Iliffe]

On Wednesday 08 March 2017 17:11:16 Adrian Klaver wrote:
> On 03/08/2017 02:01 PM, John Iliffe wrote:
> 
> Please use Reply All so others get your posts.
> Ccing list
> 
> > On Wednesday 08 March 2017 16:40:03 you wrote:
> >> On 03/08/2017 01:28 PM, John Iliffe wrote:
> >>> ---
> >>> [root@prod04 John]# su postgres
> >>> [postgres@prod04 John]$ pg_ctl start -D /usr/pgsql_tablespaces
> >>> could not change directory to "/home/John": Permission denied
> >>> server starting
> >> 
> >> This is somewhat suspicious.
> 
> For the list the below is from a previous message which I forgot to Cc.
> 
> >> What if you shutdown the Postgres server and then su - postgres to
> >> and
> > 
> >> run?:
> > I was starting postgres as user postgres; but it was still in my home
> > directory. Everything did start but I changed to /usr/pgsql_dataspaces
> > and the message went away.  Since there is no requirement to actually
> > cd to anything the message is a bit of a red herring.
> > 
> > That said, I'm not sure how it will be handled by the system start up
> > task. I'll have to check when I add it.  These are unusual manual
> > (re)starts.
> > 
> >> pg_ctl start -D /usr/pgsql_tablespaces
> >> 
> >> Along that line what user 'owns' /usr/pgsql_tablespaces?
> > 
> > /usr/pgsql_tablespaces is owned by postgres, group postgres, with
> > permissions drwe.. on the directory and rw... on all the
> > files. This would seem correct to me.
> > 
> >> Well this indicates the socket is working.
> >> 
> >> What if you do?:
> >> 
> >> psql -U postgres -h ::1
> > 
> > [root@prod04 John]# psql -U postgres -h ::1
> > psql (9.6.2)
> > Type "help" for help.
> 
> So Postgres is binding to IPv6.
> 
> >> That is set in pg_hba.conf. The default is:
> >> 
> >> # "local" is for Unix domain socket connections only
> >> local   all all trust
> > 
> > Yes, I recall major changes to that file that I did on the current
> > server. But that was at least 5 years ago :-(
> > 
> > I have that one fairly tightly locked down since this is a web server.
> > 
> > lsof says that there is nothing assigned to postmaster at this
> > time.
> 
> Given that you can connect I have to believe lsof would show something,
> so what options are you using with lsof and what user are you running it
> as?

[root@prod04 John]# lsof | grep postmaster
[root@prod04 John]# 

No parameters so I get the whole list.  As noted postmaster doesn't find 
anything whereas I think that I said that the hits on postgres are for the 
table space, the libraries and such.  I did find the following just now for 
postgres:

postgres  1991  postgres5u unix 0x9e53a16edc00   0t0  
   63085 /tmp/.s.PGSQL.5432 type=STREAM

That said, the start up error still shows up, including on the run that I 
extracted that from.

--
[postgres@prod04 John]$ cd /usr/pgsql_tablespaces
[postgres@prod04 pgsql_tablespaces]$ pg_ctl start -D /usr/pgsql_tablespaces
server starting
[postgres@prod04 pgsql_tablespaces]$ LOG:  could not bind IPv4 socket:
Cannot assign requested address
HINT:  Is another postmaster already running on port 5432? If not, wait a
few seconds and retry.
LOG:  database system was shut down at 2017-03-08 23:05:14 EST
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

[postgres@prod04 pgsql_tablespaces]$ exit
exit
[root@prod04 John]# lsof -n | grep postmaster
[root@prod04 John]# lsof -n | grep postgres | grep 5432
postgres  2760  postgres5u unix 0x9e5390b5b800   0t0 
 69422 /tmp/.s.PGSQL.5432 type=STREAM
[root@prod04 John]# 
--

This is a domain socket I think, in which case it still isn't getting the 
IP socket for some reason.

Regards,

John




-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Seq scan X Index scan

[David G. Johnston]

On Wed, Mar 8, 2017 at 3:32 PM, Patrick B  wrote:

> Why is SEQ SCAN faster than index scan?
>

Same number of evaluated record and less effort-per-record​.  You only win
with an index if you can evaluate fewer records to make up for the extra
effort per record that querying an index involves compared to just reading
the actual data.

> This is an environment t
> ​​
> est but i'm running the same test on a production environment and also seq
> scan is cheaper than index.
>
>
Define (or show) production...

David J.
​

[GENERAL] Seq scan X Index scan

[Patrick B]

Hi all.

I'm testing GIN indexes on a wildcard search.

Basically I've created this on my test environment:

create table test_gin_index (
> name_first CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING,
> name_last CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING
> );

insert into test_gin_index VALUES ('jhon','backer');
> insert into test_gin_index VALUES ('paul','min');
> insert into test_gin_index VALUES ('emily','foo');

CREATE EXTENSION pg_trgm;
> create index on test_gin_index using gin (name_first gin_trgm_ops);

analyze  test_gin_index;


*Explain analyze with SEQ scans:*

explain analyze select * from test_gin_index where name_first ILIKE '%on%';

   QUERY PLAN


-

 Seq Scan on test_gin_index  (cost=0.00..1.04 rows=1 width=19) (actual
time=0.009..0.012 rows=1 loops=1)

   Filter: ((name_first)::text ~~* '%on%'::text)

   Rows Removed by Filter: 2

 Planning time: 0.075 ms

 Execution time: 0.027 ms

(5 rows)


*Explain analyze with INDEX scan:*

explain analyze select * from test_gin_index where name_first ILIKE '%on%';

  QUERY PLAN


---

 Bitmap Heap Scan on test_gin_index  (cost=92.00..96.02 rows=1 width=19)
(actual time=0.020..0.022 rows=1 loops=1)

   Recheck Cond: ((name_first)::text ~~* '%on%'::text)

   Rows Removed by Index Recheck: 2

   Heap Blocks: exact=1

   ->  Bitmap Index Scan on test_gin_index_name_first_idx
(cost=0.00..92.00 rows=1 width=0) (actual time=0.010..0.010 rows=3 loops=1)

 Index Cond: ((name_first)::text ~~* '%on%'::text)

 Planning time: 0.122 ms

 Execution time: 0.042 ms

(8 rows)


Why is SEQ SCAN faster than index scan? This is an environment test but i'm
running the same test on a production environment and also seq scan is
cheaper than index.


Thanks

Patrick

Re: [GENERAL] Unable to start postgresql

[Adrian Klaver]

On 03/08/2017 01:48 PM, John Iliffe wrote:

On Wednesday 08 March 2017 15:13:29 Tom Lane wrote:

John Iliffe  writes:

I tried psql but it won't work, as expected, because socket 5432 is
not available.


Actually, that's not all that expected.  psql by default would try to
connect via a Unix socket, so it wouldn't matter whether or not the
postmaster had been able to open an IPv4 port.  The most likely reason
for failing to connect via Unix socket is looking in the wrong directory
for the socket, viz "/tmp" vs "/var/run/postgresql", which is why Adrian
is pressing you about other Postgres installations on the machine.  If
your psql session is using a Red Hat-supplied libpq.so then it will
likely look in /var/run/postgresql, whereas this stock-sources
postmaster is going to have put it in /tmp by default.  (You could
adjust the
unix_socket_directories parameter to fix that.)  It would also help to
pay close attention to the error message psql gives when it fails to
connect.

Even if you are doing "psql -h localhost", I'm pretty sure "localhost"
will resolve as IPv6 not IPv4 (ie ::1 not 127.0.0.1) on Fedora 25 --- it
does on mine.  So if the postmaster successfully opened an IPv6 port,
which I think it would do by default, then it still wouldn't matter that
the IPv4 port wasn't there; the issue should still be masked.

FWIW, this is what I see for network sockets when lsof'ing a stock
postmaster on current Fedora 25:

...
postmaste 20082  tgl3u  IPv6  37256  0t0 TCP
localhost:postgres (LISTEN) postmaste 20082  tgl4u  IPv4
  37257  0t0 TCP localhost:postgres (LISTEN) postmaste 20082
tgl5u  unix 0x9eb3435cfc00  0t0   37259 /tmp/.s.PGSQL.5432
type=STREAM ...

or with -n it looks like

postmaste 20082  tgl3u  IPv6  37256  0t0 TCP
[::1]:postgres (LISTEN) postmaste 20082  tgl4u  IPv4
37257  0t0 TCP 127.0.0.1:postgres (LISTEN) postmaste 20082  tgl
   5u  unix 0x9eb3435cfc00  0t0   37259 /tmp/.s.PGSQL.5432
type=STREAM

What I am suspicious of at this point is that the root of the problem is
networking misconfiguration on your machine, such that IPv4 doesn't work
at all; given the platform's bias towards IPv6 for loopback, you might
not have noticed otherwise.  You might check what results you get from
"ping ::1" vs "ping 127.0.0.1" vs "ping localhost".

regards, tom lane


Hi Tom:

By now you have probably seen that I did get PSQL to run.  I had assumed
that meant that everything was at least running if not as expected, at
least running.  But I did an lsof and I don't get anything at all for
postmaster.  postgres has a lot of open files but they are libraries and so
forth so not applicable to this situation.

I did the pings you suggest with the following results:

---
[root@prod04 John]# ping localhost
PING localhost(localhost (::1)) 56 data bytes
64 bytes from localhost (::1): icmp_seq=1 ttl=64 time=0.078 ms
64 bytes from localhost (::1): icmp_seq=2 ttl=64 time=0.060 ms
64 bytes from localhost (::1): icmp_seq=3 ttl=64 time=0.059 ms
64 bytes from localhost (::1): icmp_seq=4 ttl=64 time=0.064 ms
64 bytes from localhost (::1): icmp_seq=5 ttl=64 time=0.059 ms
^C
--- localhost ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4084ms
rtt min/avg/max/mdev = 0.059/0.064/0.078/0.007 ms
[root@prod04 John]#
[root@prod04 John]# ping ::1
PING ::1(::1) 56 data bytes
64 bytes from ::1: icmp_seq=1 ttl=64 time=0.074 ms
64 bytes from ::1: icmp_seq=2 ttl=64 time=0.059 ms
64 bytes from ::1: icmp_seq=3 ttl=64 time=0.059 ms
64 bytes from ::1: icmp_seq=4 ttl=64 time=0.060 ms
64 bytes from ::1: icmp_seq=5 ttl=64 time=0.059 ms
^C
--- ::1 ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4108ms
rtt min/avg/max/mdev = 0.059/0.062/0.074/0.007 ms
[root@prod04 John]#
[root@prod04 John]#
[root@prod04 John]# ping 127.0.0.1
PING 127.0.0.1 (127.0.0.1) 56(84) bytes of data.
64 bytes from 127.0.0.1: icmp_seq=1 ttl=64 time=0.057 ms
64 bytes from 127.0.0.1: icmp_seq=2 ttl=64 time=0.045 ms
64 bytes from 127.0.0.1: icmp_seq=3 ttl=64 time=0.046 ms
64 bytes from 127.0.0.1: icmp_seq=4 ttl=64 time=0.049 ms
^C
--- 127.0.0.1 ping statistics ---
4 packets transmitted, 4 received, 0% packet loss, time 3066ms
rtt min/avg/max/mdev = 0.045/0.049/0.057/0.006 ms
[root@prod04 John]# ping 192.168.1.8   <--- current server as a network
test

PING 192.168.1.8 (192.168.1.8) 56(84) bytes of data.
64 bytes from 192.168.1.8: icmp_seq=1 ttl=64 time=1.77 ms
64 bytes from 192.168.1.8: icmp_seq=2 ttl=64 time=0.627 ms
From 192.168.1.1: icmp_seq=2 Redirect Host(New nexthop: 192.168.1.8)
64 bytes from 192.168.1.8: icmp_seq=3 ttl=64 time=0.274 ms
^C
--- 192.168.1.8 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2003ms
rtt min/avg/max/mdev = 0.274/0.890/1.771/0.639 ms




So what 

Re: [GENERAL] Unable to start postgresql

[Adrian Klaver]

On 03/08/2017 02:01 PM, John Iliffe wrote:

Please use Reply All so others get your posts.
Ccing list


On Wednesday 08 March 2017 16:40:03 you wrote:

On 03/08/2017 01:28 PM, John Iliffe wrote:


---
[root@prod04 John]# su postgres
[postgres@prod04 John]$ pg_ctl start -D /usr/pgsql_tablespaces
could not change directory to "/home/John": Permission denied
server starting


This is somewhat suspicious.



For the list the below is from a previous message which I forgot to Cc.


What if you shutdown the Postgres server and then su - postgres to and
run?:

I was starting postgres as user postgres; but it was still in my home
directory. Everything did start but I changed to /usr/pgsql_dataspaces and
the message went away.  Since there is no requirement to actually cd to
anything the message is a bit of a red herring.

That said, I'm not sure how it will be handled by the system start up task.
I'll have to check when I add it.  These are unusual manual (re)starts.


pg_ctl start -D /usr/pgsql_tablespaces

Along that line what user 'owns' /usr/pgsql_tablespaces?


/usr/pgsql_tablespaces is owned by postgres, group postgres, with
permissions drwe.. on the directory and rw... on all the files.
This would seem correct to me.




Well this indicates the socket is working.

What if you do?:

psql -U postgres -h ::1


[root@prod04 John]# psql -U postgres -h ::1
psql (9.6.2)
Type "help" for help.


So Postgres is binding to IPv6.



That is set in pg_hba.conf. The default is:

# "local" is for Unix domain socket connections only
local   all all trust




Yes, I recall major changes to that file that I did on the current server.
But that was at least 5 years ago :-(

I have that one fairly tightly locked down since this is a web server.





lsof says that there is nothing assigned to postmaster at this time.



Given that you can connect I have to believe lsof would show something, 
so what options are you using with lsof and what user are you running it as?


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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unable to start postgresql

[John Iliffe]

On Wednesday 08 March 2017 15:13:29 Tom Lane wrote:
> John Iliffe  writes:
> > I tried psql but it won't work, as expected, because socket 5432 is
> > not available.
> 
> Actually, that's not all that expected.  psql by default would try to
> connect via a Unix socket, so it wouldn't matter whether or not the
> postmaster had been able to open an IPv4 port.  The most likely reason
> for failing to connect via Unix socket is looking in the wrong directory
> for the socket, viz "/tmp" vs "/var/run/postgresql", which is why Adrian
> is pressing you about other Postgres installations on the machine.  If
> your psql session is using a Red Hat-supplied libpq.so then it will
> likely look in /var/run/postgresql, whereas this stock-sources
> postmaster is going to have put it in /tmp by default.  (You could
> adjust the
> unix_socket_directories parameter to fix that.)  It would also help to
> pay close attention to the error message psql gives when it fails to
> connect.
> 
> Even if you are doing "psql -h localhost", I'm pretty sure "localhost"
> will resolve as IPv6 not IPv4 (ie ::1 not 127.0.0.1) on Fedora 25 --- it
> does on mine.  So if the postmaster successfully opened an IPv6 port,
> which I think it would do by default, then it still wouldn't matter that
> the IPv4 port wasn't there; the issue should still be masked.
> 
> FWIW, this is what I see for network sockets when lsof'ing a stock
> postmaster on current Fedora 25:
> 
> ...
> postmaste 20082  tgl3u  IPv6  37256  0t0 TCP
> localhost:postgres (LISTEN) postmaste 20082  tgl4u  IPv4   
>   37257  0t0 TCP localhost:postgres (LISTEN) postmaste 20082 
> tgl5u  unix 0x9eb3435cfc00  0t0   37259 /tmp/.s.PGSQL.5432
> type=STREAM ...
> 
> or with -n it looks like
> 
> postmaste 20082  tgl3u  IPv6  37256  0t0 TCP
> [::1]:postgres (LISTEN) postmaste 20082  tgl4u  IPv4 
> 37257  0t0 TCP 127.0.0.1:postgres (LISTEN) postmaste 20082  tgl
>5u  unix 0x9eb3435cfc00  0t0   37259 /tmp/.s.PGSQL.5432
> type=STREAM
> 
> What I am suspicious of at this point is that the root of the problem is
> networking misconfiguration on your machine, such that IPv4 doesn't work
> at all; given the platform's bias towards IPv6 for loopback, you might
> not have noticed otherwise.  You might check what results you get from
> "ping ::1" vs "ping 127.0.0.1" vs "ping localhost".
> 
>   regards, tom lane

Hi Tom:

By now you have probably seen that I did get PSQL to run.  I had assumed 
that meant that everything was at least running if not as expected, at 
least running.  But I did an lsof and I don't get anything at all for 
postmaster.  postgres has a lot of open files but they are libraries and so 
forth so not applicable to this situation.

I did the pings you suggest with the following results:

---
[root@prod04 John]# ping localhost
PING localhost(localhost (::1)) 56 data bytes
64 bytes from localhost (::1): icmp_seq=1 ttl=64 time=0.078 ms
64 bytes from localhost (::1): icmp_seq=2 ttl=64 time=0.060 ms
64 bytes from localhost (::1): icmp_seq=3 ttl=64 time=0.059 ms
64 bytes from localhost (::1): icmp_seq=4 ttl=64 time=0.064 ms
64 bytes from localhost (::1): icmp_seq=5 ttl=64 time=0.059 ms
^C
--- localhost ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4084ms
rtt min/avg/max/mdev = 0.059/0.064/0.078/0.007 ms
[root@prod04 John]# 
[root@prod04 John]# ping ::1
PING ::1(::1) 56 data bytes
64 bytes from ::1: icmp_seq=1 ttl=64 time=0.074 ms
64 bytes from ::1: icmp_seq=2 ttl=64 time=0.059 ms
64 bytes from ::1: icmp_seq=3 ttl=64 time=0.059 ms
64 bytes from ::1: icmp_seq=4 ttl=64 time=0.060 ms
64 bytes from ::1: icmp_seq=5 ttl=64 time=0.059 ms
^C
--- ::1 ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 4108ms
rtt min/avg/max/mdev = 0.059/0.062/0.074/0.007 ms
[root@prod04 John]# 
[root@prod04 John]# 
[root@prod04 John]# ping 127.0.0.1
PING 127.0.0.1 (127.0.0.1) 56(84) bytes of data.
64 bytes from 127.0.0.1: icmp_seq=1 ttl=64 time=0.057 ms
64 bytes from 127.0.0.1: icmp_seq=2 ttl=64 time=0.045 ms
64 bytes from 127.0.0.1: icmp_seq=3 ttl=64 time=0.046 ms
64 bytes from 127.0.0.1: icmp_seq=4 ttl=64 time=0.049 ms
^C
--- 127.0.0.1 ping statistics ---
4 packets transmitted, 4 received, 0% packet loss, time 3066ms
rtt min/avg/max/mdev = 0.045/0.049/0.057/0.006 ms
[root@prod04 John]# ping 192.168.1.8   <--- current server as a network 
test

PING 192.168.1.8 (192.168.1.8) 56(84) bytes of data.
64 bytes from 192.168.1.8: icmp_seq=1 ttl=64 time=1.77 ms
64 bytes from 192.168.1.8: icmp_seq=2 ttl=64 time=0.627 ms
From 192.168.1.1: icmp_seq=2 Redirect Host(New nexthop: 192.168.1.8)
64 bytes from 192.168.1.8: icmp_seq=3 ttl=64 time=0.274 ms
^C
--- 192.168.1.8 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2003ms
rtt min/avg/max/mdev = 

Re: [GENERAL] Unable to start postgresql

[Adrian Klaver]

On 03/08/2017 01:28 PM, John Iliffe wrote:

On Wednesday 08 March 2017 11:18:59 Adrian Klaver wrote:

On 03/08/2017 07:37 AM, John Iliffe wrote:

On Wednesday 08 March 2017 00:01:32 Tom Lane wrote:

John Iliffe  writes:

Now, running as user postgres I try and start as stated in the
manual postgres -D /usr/pgsql_tablespaces

The result is:
[postgres@prod04 postgresql-9.6.2]$ postgres -D
/usr/pgsql_tablespaces LOG:  could not bind IPv4 socket: Cannot
assign requested address HINT:  Is another postmaster already
running on port 5432? If not, wait a few seconds and retry.
LOG:  database system was shut down at 2017-03-07 22:22:57 EST
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started


To clarify: the postmaster *is* starting here.  It failed to bind to
the IPv4 port 5432, but it must have succeeded in binding to at
least one other port (IPv6 and/or a Unix socket), else it would have
stopped and you'd have not seen the last four log lines.

It might be helpful to check with lsof to see what the postmaster
process has open after you do this.


I noticed that when I deleted the postmaster.pid file as suggested by
another answer and restarted that process issued a lot more messages
before crashing  :-(   Still couldn't connect to port 5432 though.


Have you tried the firewall setup from here:

https://fedoraproject.org/wiki/PostgreSQL
  Firewall

PostgreSQL operates on port 5432 (or whatever else you set in your
postgresql.conf). In firewalld you can open it like this:

$ # make it last after reboot
$ firewall-cmd --permanent --add-port=5432/tcp
$ # change runtime configuration
$ firewall-cmd --add-port=5432/tcp



OK, I tried this, along with some suggestions from other responses.  I also
rebooted to get a completely clean environment again, and have the
following results:

1.  the firewall now has port 5432 added permanently.  This seems to me to
be a security exposure since the socket connection that I need is an
INTERNAL (ie on the same machine) connection, not an incoming connection
from another machine.  Does anyone have any comments on that?


I don't use Fedora so all I can do is point you at:
https://fedoraproject.org/wiki/Firewalld?rd=FirewallD

It does have a the concept of an internal zone:

https://fedoraproject.org/wiki/Firewalld?rd=FirewallD#Which_zones_are_available.3F

Not sure if that applies here though. I am beginning to suspect the 
firewall is not the issue here though, so once we iron what is you could 
probably undo the open port.




2.  The start up messages (still on the screen for convenience) are:

---
[root@prod04 John]# su postgres
[postgres@prod04 John]$ pg_ctl start -D /usr/pgsql_tablespaces
could not change directory to "/home/John": Permission denied
server starting


This is somewhat suspicious.

What if you shutdown the Postgres server and then su - postgres to and run?:

pg_ctl start -D /usr/pgsql_tablespaces

Along that line what user 'owns' /usr/pgsql_tablespaces?


[postgres@prod04 John]$ LOG:  could not bind IPv4 socket: Cannot assign
requested address
HINT:  Is another postmaster already running on port 5432? If not, wait a
few seconds and retry.
LOG:  database system was shut down at 2017-03-08 10:40:27 EST
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started


3.   I found the config file (in the tablespace) and changed socket file
to /var/run/.  That caused a failure of the database since user postgres
does not have write authority on /var/run.  That isn't the problem at the
moment so I'll file it to think about later!

There is a socket and a lock file for PGSQL in the /tmp directory.

srwxrwxrwx.  1 postgres postgres   0 Mar  8 15:32 .s.PGSQL.5432
-rw---.  1 postgres postgres  49 Mar  8 15:32 .s.PGSQL.5432.lock

4.  I did a shut down of postmaster to be sure these weren't abandoned files
and they disappeared.  So I conclude that socket #5432 was, in fact,
connected at start up (???) despite what the log says.  The pid file also
disappeared as expected.


Not so sure that this not actually indicating what Tom suggested that 
there is an IPv4 config issue.




5.  Restarted,  Same messages as before.  The message says fairly
specifically that it can't bind an IPv4 socket.  Is there a chance that
there is an IPv6 socket involved here somewhere that I'm not seeing?

6  Because:


psql -U postgres
psql (9.6.2)
Type "help" for help.


Well this indicates the socket is working.

What if you do?:

psql -U postgres -h ::1



postgres=# \l
  List of databases
   Name|  Owner   | Encoding |   Collate   |Ctype|   Access
privileges

Re: [GENERAL] Unable to start postgresql

[John Iliffe]

On Wednesday 08 March 2017 11:18:59 Adrian Klaver wrote:
> On 03/08/2017 07:37 AM, John Iliffe wrote:
> > On Wednesday 08 March 2017 00:01:32 Tom Lane wrote:
> >> John Iliffe  writes:
> >>> Now, running as user postgres I try and start as stated in the
> >>> manual postgres -D /usr/pgsql_tablespaces
> >>> 
> >>> The result is:
> >>> [postgres@prod04 postgresql-9.6.2]$ postgres -D
> >>> /usr/pgsql_tablespaces LOG:  could not bind IPv4 socket: Cannot
> >>> assign requested address HINT:  Is another postmaster already
> >>> running on port 5432? If not, wait a few seconds and retry.
> >>> LOG:  database system was shut down at 2017-03-07 22:22:57 EST
> >>> LOG:  MultiXact member wraparound protections are now enabled
> >>> LOG:  database system is ready to accept connections
> >>> LOG:  autovacuum launcher started
> >> 
> >> To clarify: the postmaster *is* starting here.  It failed to bind to
> >> the IPv4 port 5432, but it must have succeeded in binding to at
> >> least one other port (IPv6 and/or a Unix socket), else it would have
> >> stopped and you'd have not seen the last four log lines.
> >> 
> >> It might be helpful to check with lsof to see what the postmaster
> >> process has open after you do this.
> > 
> > I noticed that when I deleted the postmaster.pid file as suggested by
> > another answer and restarted that process issued a lot more messages
> > before crashing  :-(   Still couldn't connect to port 5432 though.
> 
> Have you tried the firewall setup from here:
> 
> https://fedoraproject.org/wiki/PostgreSQL
>   Firewall
> 
> PostgreSQL operates on port 5432 (or whatever else you set in your
> postgresql.conf). In firewalld you can open it like this:
> 
> $ # make it last after reboot
> $ firewall-cmd --permanent --add-port=5432/tcp
> $ # change runtime configuration
> $ firewall-cmd --add-port=5432/tcp
> 

OK, I tried this, along with some suggestions from other responses.  I also 
rebooted to get a completely clean environment again, and have the 
following results:

1.  the firewall now has port 5432 added permanently.  This seems to me to 
be a security exposure since the socket connection that I need is an 
INTERNAL (ie on the same machine) connection, not an incoming connection 
from another machine.  Does anyone have any comments on that?

2.  The start up messages (still on the screen for convenience) are:

---
[root@prod04 John]# su postgres
[postgres@prod04 John]$ pg_ctl start -D /usr/pgsql_tablespaces
could not change directory to "/home/John": Permission denied
server starting
[postgres@prod04 John]$ LOG:  could not bind IPv4 socket: Cannot assign 
requested address
HINT:  Is another postmaster already running on port 5432? If not, wait a 
few seconds and retry.
LOG:  database system was shut down at 2017-03-08 10:40:27 EST
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started


3.   I found the config file (in the tablespace) and changed socket file 
to /var/run/.  That caused a failure of the database since user postgres 
does not have write authority on /var/run.  That isn't the problem at the 
moment so I'll file it to think about later!

There is a socket and a lock file for PGSQL in the /tmp directory.

srwxrwxrwx.  1 postgres postgres   0 Mar  8 15:32 .s.PGSQL.5432
-rw---.  1 postgres postgres  49 Mar  8 15:32 .s.PGSQL.5432.lock

4.  I did a shut down of postmaster to be sure these weren't abandoned files 
and they disappeared.  So I conclude that socket #5432 was, in fact, 
connected at start up (???) despite what the log says.  The pid file also 
disappeared as expected.

5.  Restarted,  Same messages as before.  The message says fairly 
specifically that it can't bind an IPv4 socket.  Is there a chance that 
there is an IPv6 socket involved here somewhere that I'm not seeing?  

6  Because:


psql -U postgres
psql (9.6.2)
Type "help" for help.

postgres=# \l
  List of databases
   Name|  Owner   | Encoding |   Collate   |Ctype|   Access 
privileges   
---+--+--+-+-+---
 postgres  | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | 
 template0 | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres  

+
   |  |  | | | 
postgres=CTc/postgres
 template1 | postgres | UTF8 | en_CA.UTF-8 | en_CA.UTF-8 | =c/postgres  

+
   |  |  | | | 
postgres=CTc/postgres
(3 rows)

postgres=# 
---

So, to the extent I can test at the moment, it looks like I have a working 
database with a lot of strange messages.  Odd that user postgres can log on 
without a password but I 

Re: [GENERAL] Unable to start postgresql

[Tom Lane]

John Iliffe  writes:
> I tried psql but it won't work, as expected, because socket 5432 is not 
> available.

Actually, that's not all that expected.  psql by default would try to
connect via a Unix socket, so it wouldn't matter whether or not the
postmaster had been able to open an IPv4 port.  The most likely reason
for failing to connect via Unix socket is looking in the wrong directory
for the socket, viz "/tmp" vs "/var/run/postgresql", which is why Adrian
is pressing you about other Postgres installations on the machine.  If
your psql session is using a Red Hat-supplied libpq.so then it will likely
look in /var/run/postgresql, whereas this stock-sources postmaster is going
to have put it in /tmp by default.  (You could adjust the
unix_socket_directories parameter to fix that.)  It would also help to
pay close attention to the error message psql gives when it fails to
connect.

Even if you are doing "psql -h localhost", I'm pretty sure "localhost"
will resolve as IPv6 not IPv4 (ie ::1 not 127.0.0.1) on Fedora 25 --- it
does on mine.  So if the postmaster successfully opened an IPv6 port,
which I think it would do by default, then it still wouldn't matter that
the IPv4 port wasn't there; the issue should still be masked.

FWIW, this is what I see for network sockets when lsof'ing a stock
postmaster on current Fedora 25:

...
postmaste 20082  tgl3u  IPv6  37256  0t0 TCP 
localhost:postgres (LISTEN)
postmaste 20082  tgl4u  IPv4  37257  0t0 TCP 
localhost:postgres (LISTEN)
postmaste 20082  tgl5u  unix 0x9eb3435cfc00  0t0   37259 
/tmp/.s.PGSQL.5432 type=STREAM
...

or with -n it looks like

postmaste 20082  tgl3u  IPv6  37256  0t0 TCP 
[::1]:postgres (LISTEN)
postmaste 20082  tgl4u  IPv4  37257  0t0 TCP 
127.0.0.1:postgres (LISTEN)
postmaste 20082  tgl5u  unix 0x9eb3435cfc00  0t0   37259 
/tmp/.s.PGSQL.5432 type=STREAM

What I am suspicious of at this point is that the root of the problem is
networking misconfiguration on your machine, such that IPv4 doesn't work
at all; given the platform's bias towards IPv6 for loopback, you might
not have noticed otherwise.  You might check what results you get from
"ping ::1" vs "ping 127.0.0.1" vs "ping localhost".

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] too may LWLocks

[Julien Rouhaud]

On Wed, Mar 08, 2017 at 03:34:56PM +0530, hariprasath nallasamy wrote:
> Hi all
> I am building an extension using shared memory hash table and for locking
> hash table i am using LWLocks, but the thing was when i try to run some 1k
> queries one after other, for each query i am getting one LWLock but on
> executing 200th query i am getting the error *ERROR:  too many LWLocks
> taken*.
> 
> But in each query i acquire and release that block. So that lock has to be
> flushed after executing query, but why am i getting this error.?
> 
> Is this due to *held_lwlocks *in LWLock.c is fixed only to some number 200
> here.
> Or am i missing something here.?

The most likely reason is that you have some code path in your extension where
you don't release the LWLock.  Without access to the code we can't do much more
to help you I'm afraid.  You could also try on a postgres build having
LWLOCK_STATS defined.

--
Julien Rouhaud
http://dalibo.com - http://dalibo.org


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unable to start postgresql

[Adrian Klaver]

On 03/08/2017 10:25 AM, John Iliffe wrote:

On Wednesday 08 March 2017 11:37:27 Adrian Klaver wrote:

On 03/08/2017 07:37 AM, John Iliffe wrote:

On Wednesday 08 March 2017 00:01:32 Tom Lane wrote:

John Iliffe  writes:

Now, running as user postgres I try and start as stated in the
manual postgres -D /usr/pgsql_tablespaces

The result is:
[postgres@prod04 postgresql-9.6.2]$ postgres -D
/usr/pgsql_tablespaces LOG:  could not bind IPv4 socket: Cannot
assign requested address HINT:  Is another postmaster already
running on port 5432? If not, wait a few seconds and retry.
LOG:  database system was shut down at 2017-03-07 22:22:57 EST
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started


To clarify: the postmaster *is* starting here.  It failed to bind to
the IPv4 port 5432, but it must have succeeded in binding to at
least one other port (IPv6 and/or a Unix socket), else it would have
stopped and you'd have not seen the last four log lines.

It might be helpful to check with lsof to see what the postmaster
process has open after you do this.


I noticed that when I deleted the postmaster.pid file as suggested by
another answer and restarted that process issued a lot more messages
before crashing  :-(   Still couldn't connect to port 5432 though.


Meant to ask before, can you show the command you are using to connect?


Yes:

[postgres@prod04 John]$ pg_ctl start -D /usr/pgsql_tablespaces


This is the command to start Postgres.


I tried psql but it won't work, as expected, because socket 5432 is not
available.


So what is the psql connection command you are using and what is the 
error message you are getting?




John








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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unable to start postgresql

[Adrian Klaver]

On 03/08/2017 10:12 AM, John Iliffe wrote:

See embedded.


Ccing list



will not be able to use it.


Yes, but if that happens at least I will be able to back out because I can
go back and start the old postgresql since it is on a different /usr
partition.  That's the reason for the install directory being named after
the release version.  After I am satisfied that everything is working
properly I usually delete the old install directory.  Usually a few months
of operation.




So did you specify a different --prefix previously or just used the
defaults?


No, actually I used the same name for the install directory as this one but
I deleted the old one to completely get rid of the bad code.






No, I was unable to start the original postgresql installation so it never
got used or initialized.



To follow up on what Tom suggested upstream do you have package 
installed version of libpq?


It would probably show up in the package manager as something along the 
lines of postgresql-client.




Thanks again.

John




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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unable to start postgresql

[John Iliffe]

On Wednesday 08 March 2017 11:37:27 Adrian Klaver wrote:
> On 03/08/2017 07:37 AM, John Iliffe wrote:
> > On Wednesday 08 March 2017 00:01:32 Tom Lane wrote:
> >> John Iliffe  writes:
> >>> Now, running as user postgres I try and start as stated in the
> >>> manual postgres -D /usr/pgsql_tablespaces
> >>> 
> >>> The result is:
> >>> [postgres@prod04 postgresql-9.6.2]$ postgres -D
> >>> /usr/pgsql_tablespaces LOG:  could not bind IPv4 socket: Cannot
> >>> assign requested address HINT:  Is another postmaster already
> >>> running on port 5432? If not, wait a few seconds and retry.
> >>> LOG:  database system was shut down at 2017-03-07 22:22:57 EST
> >>> LOG:  MultiXact member wraparound protections are now enabled
> >>> LOG:  database system is ready to accept connections
> >>> LOG:  autovacuum launcher started
> >> 
> >> To clarify: the postmaster *is* starting here.  It failed to bind to
> >> the IPv4 port 5432, but it must have succeeded in binding to at
> >> least one other port (IPv6 and/or a Unix socket), else it would have
> >> stopped and you'd have not seen the last four log lines.
> >> 
> >> It might be helpful to check with lsof to see what the postmaster
> >> process has open after you do this.
> > 
> > I noticed that when I deleted the postmaster.pid file as suggested by
> > another answer and restarted that process issued a lot more messages
> > before crashing  :-(   Still couldn't connect to port 5432 though.
> 
> Meant to ask before, can you show the command you are using to connect?

Yes:

[postgres@prod04 John]$ pg_ctl start -D /usr/pgsql_tablespaces

I tried psql but it won't work, as expected, because socket 5432 is not 
available.

John





-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Request to confirm which command is use for exclusive operation

[Albe Laurenz]

Yogesh Sharma wrote:
> I observed there is some problem in REINDEX operation in older PostgreSQL 
> versions.
> That why i want to add explicitly lock.

Which problem?

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] import CSV file to a table

[Karl Czajkowski]

On Mar 08, Rob Sargent modulated:

> Yes Karl, I agree.  I admitted as much.  But if it's clean, as in
> free of quoted commas, life is much more simple.  I've lost site of
> whether or not the OP knows his situation w.r.t. to this.  The awk
> line will tell him and for a one-off load this can make a world of
> difference in complexity - two bash lines and a COPY.
> 

Maybe I didn't understand your awk? I thought it was counting commas
in lines.  This isn't the same as counting commas in records.

this,is,record,one
"this,,","is
,,record","two
,,,"

this has three commas on each line and definitely is not suitable
for naive CSV handling.


Karl


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] import CSV file to a table

[Rob Sargent]

On 03/08/2017 09:52 AM, Karl Czajkowski wrote:

On Mar 08, Rob Sargent modulated:


Yes Karl, I agree.  I admitted as much.  But if it's clean, as in
free of quoted commas, life is much more simple.  I've lost site of
whether or not the OP knows his situation w.r.t. to this.  The awk
line will tell him and for a one-off load this can make a world of
difference in complexity - two bash lines and a COPY.


Maybe I didn't understand your awk? I thought it was counting commas
in lines.  This isn't the same as counting commas in records.

 this,is,record,one
 "this,,","is
 ,,record","two
 ,,,"

this has three commas on each line and definitely is not suitable
for naive CSV handling.


Karl
In essence it does count commas but plus one :).  $NF is number of 
fields defined by commas so one more field than number of commas. If you 
think/hope the file is simple and well formatted, this is a pretty quick 
check. But if you're looking for a general solution, you need a real csv 
parser.  I recall being quite surprised and amused to learn there is an 
actual standard for csv format. (Naturally if you have one to hand, you 
don't need the awk line.)



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] import CSV file to a table

[Bret Stern]

I'll throw in.

If tab delimited is available, perhaps that option will work
better...or..
use Access to find the violations of the quote comma delimited
assumptions, then
export from Access an import

Bret


On Wed, 2017-03-08 at 08:36 -0800, Karl Czajkowski wrote:

> I believe that in its fully glory, you cannot reliably locate CSV
> record boundaries except by parsing each field in order including
> quote processing.  Individual records may have arbitrary numbers of
> field and record separator characters within the values.
> 
> Karl
> 
> 
> On Mar 08, Rob Sargent modulated:
> > Since bash has been bandied about in this thread I presume awk is
> > available.  Here's how I would check just how 'csv'ish the incoming
> > file is.
> > ...
> 
> 

Re: [GENERAL] Unable to start postgresql

[Rob Sargent]

Meant to ask before, can you show the command you are using to connect?


My memory says OP didn't use --host, which often leads to trying the 
socket.  Do we know that's enabled in pg_hba?



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] import CSV file to a table

[Rob Sargent]

On 03/08/2017 09:36 AM, Karl Czajkowski wrote:

I believe that in its fully glory, you cannot reliably locate CSV
record boundaries except by parsing each field in order including
quote processing.  Individual records may have arbitrary numbers of
field and record separator characters within the values.

Karl


On Mar 08, Rob Sargent modulated:

Since bash has been bandied about in this thread I presume awk is
available.  Here's how I would check just how 'csv'ish the incoming
file is.
...
Yes Karl, I agree.  I admitted as much.  But if it's clean, as in free 
of quoted commas, life is much more simple.  I've lost site of whether 
or not the OP knows his situation w.r.t. to this.  The awk line will 
tell him and for a one-off load this can make a world of difference in 
complexity - two bash lines and a COPY.




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unable to start postgresql

[Adrian Klaver]

On 03/08/2017 07:37 AM, John Iliffe wrote:

On Wednesday 08 March 2017 00:01:32 Tom Lane wrote:

John Iliffe  writes:

Now, running as user postgres I try and start as stated in the manual
postgres -D /usr/pgsql_tablespaces

The result is:
[postgres@prod04 postgresql-9.6.2]$ postgres -D /usr/pgsql_tablespaces
LOG:  could not bind IPv4 socket: Cannot assign requested address
HINT:  Is another postmaster already running on port 5432? If not,
wait a few seconds and retry.
LOG:  database system was shut down at 2017-03-07 22:22:57 EST
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started


To clarify: the postmaster *is* starting here.  It failed to bind to the
IPv4 port 5432, but it must have succeeded in binding to at least one
other port (IPv6 and/or a Unix socket), else it would have stopped and
you'd have not seen the last four log lines.

It might be helpful to check with lsof to see what the postmaster
process has open after you do this.


I noticed that when I deleted the postmaster.pid file as suggested by
another answer and restarted that process issued a lot more messages before
crashing  :-(   Still couldn't connect to port 5432 though.


Meant to ask before, can you show the command you are using to connect?


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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] import CSV file to a table

[Karl Czajkowski]

I believe that in its fully glory, you cannot reliably locate CSV
record boundaries except by parsing each field in order including
quote processing.  Individual records may have arbitrary numbers of
field and record separator characters within the values.

Karl


On Mar 08, Rob Sargent modulated:
> Since bash has been bandied about in this thread I presume awk is
> available.  Here's how I would check just how 'csv'ish the incoming
> file is.
> ...


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] import CSV file to a table

[Rob Sargent]

Since bash has been bandied about in this thread I presume awk is 
available.  Here's how I would check just how 'csv'ish the incoming file is.


   awk -F"," '{a[$NF]++}END{for(i in a){printf "%d lines have %d
   fields(columns)\n", a[i], i}}' csvfilename

If this doesn't produce one line you have to suspect quoted values 
including commas (or what ever char you choose). then you need a real 
csv parser.


If just one line, I'ld use cut to get rid of unwanted columns, then let 
COPY do it's thing



On 03/08/2017 09:13 AM, Karl Czajkowski wrote:

On Mar 08, John McKown modulated:
...

​I agree. I went with a "pure BASH" approach because it is what the
user asked for & I wasn't sure what language she might be comfortable
with. I use PERL a lot. Or maybe I should say that I abuse PERL a lot.
Such as a PERL script with writes out another PERL script, based on
some input files & parameters, then runs the just written PERL script,
which does the load into a PostgreSQL database (multiple tables). Ya, a
bit perverted.​


Well, you could follow a similar meta-programming/code-generating
pattern to have the BASH script output a single SQL file to run with
psql. You could even generate PL/pgsql code to defer more data
processing to the database itself.

I think the only robust "pure BASH" approach is to use a temporary
table, so you aren't trying to parse CSV content in BASH. Using csvkit
sounds good if you can introduce these third-party dependencies.

With the temporary table, you can use SQL for most validation or data
interrogation, but you need to know at least enough schema information
in advance to form the COPY statement. Parsing the CSV header row to
plan your work puts you right back to requiring a robust CSV parser
unless you can constrain your input scenarios to only handle very
trivial headers.

If you play games with a defaulting serial column and fixed column
names like "id, c1, c2, ..., cN" for the temporary table, you might
use the id column as a DB-assigned "row number" during COPY and
validation. In this case, you could even tell Postgres there is no
header, and then let it parse the header as another data record so you
can use SQL statements to determine the actual header names and
ordering in the input.  But this still requires knowing the column
count in advance of the COPY.

I also think using something like Python with structured data
processing would be wiser, unless you know enough about the schema in
advance to avoid any CSV parsing on the client side.


Karl

Re: [GENERAL] import CSV file to a table

[David G. Johnston]

On Wed, Mar 8, 2017 at 9:13 AM, Karl Czajkowski  wrote:

>
> With the temporary table, you can use SQL for most validation or data
> interrogation, but you need to know at least enough schema information
> in advance to form the COPY statement. Parsing the CSV header row to
> plan your work puts you right back to requiring a robust CSV parser
> unless you can constrain your input scenarios to only handle very
> trivial headers.
>

​You can write the entire contents of the CSV into a psql variable and
process the text blob from there using intermediate arrays.

David J.

Re: [GENERAL] Unable to start postgresql

[Adrian Klaver]

On 03/08/2017 07:37 AM, John Iliffe wrote:

On Wednesday 08 March 2017 00:01:32 Tom Lane wrote:

John Iliffe  writes:

Now, running as user postgres I try and start as stated in the manual
postgres -D /usr/pgsql_tablespaces

The result is:
[postgres@prod04 postgresql-9.6.2]$ postgres -D /usr/pgsql_tablespaces
LOG:  could not bind IPv4 socket: Cannot assign requested address
HINT:  Is another postmaster already running on port 5432? If not,
wait a few seconds and retry.
LOG:  database system was shut down at 2017-03-07 22:22:57 EST
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started


To clarify: the postmaster *is* starting here.  It failed to bind to the
IPv4 port 5432, but it must have succeeded in binding to at least one
other port (IPv6 and/or a Unix socket), else it would have stopped and
you'd have not seen the last four log lines.

It might be helpful to check with lsof to see what the postmaster
process has open after you do this.


I noticed that when I deleted the postmaster.pid file as suggested by
another answer and restarted that process issued a lot more messages before
crashing  :-(   Still couldn't connect to port 5432 though.


Have you tried the firewall setup from here:

https://fedoraproject.org/wiki/PostgreSQL
 Firewall

PostgreSQL operates on port 5432 (or whatever else you set in your 
postgresql.conf). In firewalld you can open it like this:


$ # make it last after reboot
$ firewall-cmd --permanent --add-port=5432/tcp
$ # change runtime configuration
$ firewall-cmd --add-port=5432/tcp

More comments below.



---
[postgres@prod04 John]$ pg_ctl start -D /usr/pgsql_tablespaces
could not change directory to "/home/John": Permission denied
server starting
[postgres@prod04 John]$ LOG:  could not bind IPv4 socket: Cannot assign
requested address
HINT:  Is another postmaster already running on port 5432? If not, wait a
few seconds and retry.
LOG:  database system was interrupted; last known up at 2017-03-08 09:42:16
EST
LOG:  database system was not properly shut down; automatic recovery in
progress
LOG:  invalid record length at 0/1561138: wanted 24, got 0
LOG:  redo is not required
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started
-

so I corrected the initial error by changing to the bin directory and
starting again, after removing the postmaster.pid file.  Same result.

lsof says that there is nothing assigned to postmaster at this time.

I did manage to get a clean stop this time; no remaining pid file.


I checked with lsof and there is no process bound to socket 5432.
There is no entry in /var/run for a socket related to postgresql.


With the default configure options you used, the postmaster would have
put its Unix socket file into /tmp, not /var/run.  I wonder whether
your problem is that you're trying to connect to it with
distro-supplied psql+libpq that expects to find the Unix socket in
/var/run.


Yes.  socket file and also lock file were there.  I'll fix that in config, BUT
in the original case they weren't there.


So what is the below?



srwxrwxrwx.  1 postgres postgres0 Mar  8 10:10 .s.PGSQL.5432
-rw---.  1 postgres postgres   49 Mar  8 10:10 .s.PGSQL.5432.lock

Still, the first lines of the log are the same; can't connect to socket
5432.

The following processes show up in ps

root  1149  1136  0 10:18 pts/100:00:00 su postgres
postgres  1150  1149  0 10:18 pts/100:00:00 bash
postgres  1230 1  0 10:26 pts/100:00:00
/usr/postgres-9.6.2/bin/postgres -D /usr/pgsql_tablespaces
postgres  1232  1230  0 10:26 ?00:00:00 postgres: checkpointer
 process
postgres  1233  1230  0 10:26 ?00:00:00 postgres: writer process
postgres  1234  1230  0 10:26 ?00:00:00 postgres: wal writer
 process
postgres  1235  1230  0 10:26 ?00:00:00 postgres: autovacuum
 launcher process
postgres  1236  1230  0 10:26 ?00:00:00 postgres: stats collector
 process




So Postgres is running.




One thing that I haven't been able to find any the log files.  Where
are they normally stored?


Where you configure them:

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



They seem to be showing up on postmaster's stderr (ie, your terminal)
which again is the vanilla-configuration default if you didn't do
anything to edit the postgresql.conf settings.

I suspect that you're used to the behavior of a vendor-configured
postgres package and have not taken the steps needed to make a build
from source behave the same way.  Recommend looking into what patches
the vendor package applies and what configure options are used.



No, actually I have been using postgresql since about 2007, always from

Re: [GENERAL] import CSV file to a table

[Karl Czajkowski]

On Mar 08, John McKown modulated:
...
> ​I agree. I went with a "pure BASH" approach because it is what the
> user asked for & I wasn't sure what language she might be comfortable
> with. I use PERL a lot. Or maybe I should say that I abuse PERL a lot.
> Such as a PERL script with writes out another PERL script, based on
> some input files & parameters, then runs the just written PERL script,
> which does the load into a PostgreSQL database (multiple tables). Ya, a
> bit perverted.​
> 

Well, you could follow a similar meta-programming/code-generating
pattern to have the BASH script output a single SQL file to run with
psql. You could even generate PL/pgsql code to defer more data
processing to the database itself.

I think the only robust "pure BASH" approach is to use a temporary
table, so you aren't trying to parse CSV content in BASH. Using csvkit
sounds good if you can introduce these third-party dependencies.

With the temporary table, you can use SQL for most validation or data
interrogation, but you need to know at least enough schema information
in advance to form the COPY statement. Parsing the CSV header row to
plan your work puts you right back to requiring a robust CSV parser
unless you can constrain your input scenarios to only handle very
trivial headers.

If you play games with a defaulting serial column and fixed column
names like "id, c1, c2, ..., cN" for the temporary table, you might
use the id column as a DB-assigned "row number" during COPY and
validation. In this case, you could even tell Postgres there is no
header, and then let it parse the header as another data record so you
can use SQL statements to determine the actual header names and
ordering in the input.  But this still requires knowing the column
count in advance of the COPY.

I also think using something like Python with structured data
processing would be wiser, unless you know enough about the schema in
advance to avoid any CSV parsing on the client side.


Karl


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unable to start postgresql

[Adrian Klaver]

On 03/08/2017 07:44 AM, John Iliffe wrote:

See embedded.




Just to be clear you installed in:

/usr/postgres-9.6.2


yes, and the expected directories /usr/postgres-9.6.2/bin, /include, /lib,
and /share are all there.


but created the data directory in:


/usr/pgsql_tablespaces

Yes, I did that to separate the data from the software in case I decide to
update postgresql in the future.  That way I can always revert if
necessary.


FYI, not that a separate data directory is a problem, but if you are 
updating from one major version to another that is not going to work as 
the new version will not be able to use the old data directory and if 
you do convert it using something like pg_upgrade then the old version 
will not be able to use it.





Also above you say:

"I was unable to get postgres started so I did a very basic
compile/install to test it. "



Yes, with no changes except the --prefix in the config file I was assured that
I wasn't causing this problem with a badly chosen parameter.


So did you specify a different --prefix previously or just used the 
defaults?





To me that implies there is another instance of Postgres on the system,
is that the case?


No other instances of Postgres on the system.  Since it wasn't working I
deleted the original installation by deleting the install directory.


How about the data directory?




If not could you explain what you meant?





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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Request to confirm which command is use for exclusive operation

[David G. Johnston]

On Wed, Mar 8, 2017 at 3:42 AM, Yogesh Sharma  wrote:

> I observed there is some problem in REINDEX operation in older PostgreSQL
> versions.
> That why i want to add explicitly lock.
>
>
​You should probably define "current" and "older" in your personal context
- what version(s) are you targeting?

David J.​

Re: [GENERAL] Request to confirm which command is use for exclusive operation

[David G. Johnston]

On Tue, Mar 7, 2017 at 11:21 PM, Yogesh Sharma 
wrote:

> Dear David,
>
>  I want to apply explicitly lock mechanism once inset operation is in
> progress then REINDEX will wait.
> And vice versa.
> So, please let me know this type of handling is possible.
>
>
​You want to exclusively lock a table during every insert just because
something might run a concurrent reindex?

If you want to write a higher-level locking scheme for your system and not
use what is provided natively by PostgreSQL that's your choice.  Its not
worth volunteering my time to help do that, though.  The docs have
considerable info regarding explicit locking using the LOCK command.  You
should start there.

I see in a subsequent response that you have doubts that REINDEX is
actually working properly.  If you are able to demonstrate that maybe the
underlying problem could be addressed.

David J.
​

Re: [GENERAL] Unable to start postgresql

[John Iliffe]

See embedded.

On Wednesday 08 March 2017 00:09:56 Adrian Klaver wrote:
> On 03/07/2017 08:17 PM, John Iliffe wrote:
> > I was unable to get postgres started so I did a very basic
> > compile/install to test it.  The configuration line was:
> > 
> > ./configure --prefix=/usr/postgres-9.6.2
> > 
> > 
> > Then I ran make which completed as expected:
> > 
> > make[1]: Leaving directory '/tmp/postgresql-9.6.2/config'
> > All of PostgreSQL successfully made. Ready to install.
> > 
> > --
> > Then make check
> > 
> > make check
> > 
> > ===
> > 
> >  All 167 tests passed.
> > 
> > ===
> > 
> > make[1]: Leaving directory '/tmp/postgresql-9.6.2/src/test/regress'
> > 
> > 
> > Then make install as user root because of directory permissions.  I
> > have the expected files in /usr/postgres_9.6.2
> > 
> > Now, running as user postgres I try and start as stated in the manual
> > 
> > postgres -D /usr/pgsql_tablespaces
> > 
> > initdb has already been run and the directory pgsql_tablespaces has a
> > number of files as expected.  As yet no database has been defined
> > because psql won't start.
> 
> Just to be clear you installed in:
> 
> /usr/postgres-9.6.2

yes, and the expected directories /usr/postgres-9.6.2/bin, /include, /lib, 
and /share are all there.
> 
> but created the data directory in:
> 
/usr/pgsql_tablespaces

Yes, I did that to separate the data from the software in case I decide to 
update postgresql in the future.  That way I can always revert if 
necessary.

> Also above you say:
> 
> "I was unable to get postgres started so I did a very basic
> compile/install to test it. "
> 

Yes, with no changes except the --prefix in the config file I was assured that 
I wasn't causing this problem with a badly chosen parameter.

> To me that implies there is another instance of Postgres on the system,
> is that the case?
> 
No other instances of Postgres on the system.  Since it wasn't working I 
deleted the original installation by deleting the install directory.

> If not could you explain what you meant?
> 
> > The result is:
> > [postgres@prod04 postgresql-9.6.2]$ postgres -D /usr/pgsql_tablespaces
> > LOG:  could not bind IPv4 socket: Cannot assign requested address
> > HINT:  Is another postmaster already running on port 5432? If not,
> > wait a few seconds and retry.
> > LOG:  database system was shut down at 2017-03-07 22:22:57 EST
> > LOG:  MultiXact member wraparound protections are now enabled
> > LOG:  database system is ready to accept connections
> > LOG:  autovacuum launcher started
> > 
> > Same results if I use pg_ctl to start the process.
> > 
> > I checked with lsof and there is no process bound to socket 5432. 
> > There is no entry in /var/run for a socket related to postgresql.
> > 
> > I thought it might be a security issue so I put SELinux in permissive
> > mode but the result is the same.  The SELinux journal does not show
> > any warnings on this process.
> > 
> > [root@prod04 postgresql-9.6.2]# sestatus
> > SELinux status: enabled
> > SELinuxfs mount:/sys/fs/selinux
> > SELinux root directory: /etc/selinux
> > Loaded policy name: targeted
> > Current mode:   permissive  <--permissive mode**
> > Mode from config file:  permissive
> > Policy MLS status:  enabled
> > Policy deny_unknown status: allowed
> > Max kernel policy version:  30
> > -
> > 
> > One thing that I haven't been able to find any the log files.  Where
> > are they normally stored?
> > 
> > So, any ideas as to where to go next to debug this would be
> > appreciated! This is a brand new server that I am trying to configure
> > so I have a fair amount of security clearance to chase things.
> > 
> > Thanks in advance.
> > 
> > John
> > =


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Unable to start postgresql

[John Iliffe]

On Wednesday 08 March 2017 00:01:32 Tom Lane wrote:
> John Iliffe  writes:
> > Now, running as user postgres I try and start as stated in the manual
> > postgres -D /usr/pgsql_tablespaces
> > 
> > The result is:
> > [postgres@prod04 postgresql-9.6.2]$ postgres -D /usr/pgsql_tablespaces
> > LOG:  could not bind IPv4 socket: Cannot assign requested address
> > HINT:  Is another postmaster already running on port 5432? If not,
> > wait a few seconds and retry.
> > LOG:  database system was shut down at 2017-03-07 22:22:57 EST
> > LOG:  MultiXact member wraparound protections are now enabled
> > LOG:  database system is ready to accept connections
> > LOG:  autovacuum launcher started
> 
> To clarify: the postmaster *is* starting here.  It failed to bind to the
> IPv4 port 5432, but it must have succeeded in binding to at least one
> other port (IPv6 and/or a Unix socket), else it would have stopped and
> you'd have not seen the last four log lines.
> 
> It might be helpful to check with lsof to see what the postmaster
> process has open after you do this.
> 
I noticed that when I deleted the postmaster.pid file as suggested by 
another answer and restarted that process issued a lot more messages before 
crashing  :-(   Still couldn't connect to port 5432 though.  

---
[postgres@prod04 John]$ pg_ctl start -D /usr/pgsql_tablespaces
could not change directory to "/home/John": Permission denied
server starting
[postgres@prod04 John]$ LOG:  could not bind IPv4 socket: Cannot assign 
requested address
HINT:  Is another postmaster already running on port 5432? If not, wait a 
few seconds and retry.
LOG:  database system was interrupted; last known up at 2017-03-08 09:42:16 
EST
LOG:  database system was not properly shut down; automatic recovery in 
progress
LOG:  invalid record length at 0/1561138: wanted 24, got 0
LOG:  redo is not required
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started
-

so I corrected the initial error by changing to the bin directory and 
starting again, after removing the postmaster.pid file.  Same result.  

lsof says that there is nothing assigned to postmaster at this time.  

I did manage to get a clean stop this time; no remaining pid file.

> > I checked with lsof and there is no process bound to socket 5432. 
> > There is no entry in /var/run for a socket related to postgresql.
> 
> With the default configure options you used, the postmaster would have
> put its Unix socket file into /tmp, not /var/run.  I wonder whether
> your problem is that you're trying to connect to it with
> distro-supplied psql+libpq that expects to find the Unix socket in
> /var/run.
> 
Yes.  socket file and also lock file were there.  I'll fix that in config, BUT 
in the original case they weren't there.

srwxrwxrwx.  1 postgres postgres0 Mar  8 10:10 .s.PGSQL.5432
-rw---.  1 postgres postgres   49 Mar  8 10:10 .s.PGSQL.5432.lock

Still, the first lines of the log are the same; can't connect to socket 
5432.

The following processes show up in ps

root  1149  1136  0 10:18 pts/100:00:00 su postgres
postgres  1150  1149  0 10:18 pts/100:00:00 bash
postgres  1230 1  0 10:26 pts/100:00:00  
/usr/postgres-9.6.2/bin/postgres -D /usr/pgsql_tablespaces
postgres  1232  1230  0 10:26 ?00:00:00 postgres: checkpointer
 process   
postgres  1233  1230  0 10:26 ?00:00:00 postgres: writer process   
postgres  1234  1230  0 10:26 ?00:00:00 postgres: wal writer
 process   
postgres  1235  1230  0 10:26 ?00:00:00 postgres: autovacuum
 launcher process   
postgres  1236  1230  0 10:26 ?00:00:00 postgres: stats collector
 process  



> > One thing that I haven't been able to find any the log files.  Where
> > are they normally stored?
> 
> They seem to be showing up on postmaster's stderr (ie, your terminal)
> which again is the vanilla-configuration default if you didn't do
> anything to edit the postgresql.conf settings.
> 
> I suspect that you're used to the behavior of a vendor-configured
> postgres package and have not taken the steps needed to make a build
> from source behave the same way.  Recommend looking into what patches
> the vendor package applies and what configure options are used.
> 

No, actually I have been using postgresql since about 2007, always from 
manual installs.  Just never needed to ask a question before!  I run a 
small publishing business specializing an Amateur Radio training and we 
rely on these databases to run just about everything from sales to user 
support to accounting.  At the moment the old server is on pgsql 9.2.1 
which is about 5 years old.

I almost never use the distro supplied application software because I've 
had problems with automatic updates making everything fail and no 

Re: [GENERAL] Unable to start postgresql

[Melvin Davidson]

On Wed, Mar 8, 2017 at 10:00 AM, Tom Lane  wrote:

> John Iliffe  writes:
> > When the programme exited it left another postmaster.pid file so I
> deleted
> > that one too.
>
> You haven't shown us the program actually exiting, and basically the only
> way to get the postmaster to exit without removing its pid file is to
> kill -9 it.  Now I am suspicious that you in fact haven't killed any
> postmasters, but only removed their pidfiles out from under them, which is
> an incredibly dangerous thing to do.  Check "ps ax" output to see if any
> postgres processes are lurking in background.
>
> regards, tom lane
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Hey, looking closer, I see this in your original log

LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

That means Postgres WAS started, just that the postgres port was unable to
be opened.
So if you do a pg_ctl stop, change the port in postgresql.conf to 5433 (or
5434) and then attempt
to restart, is your problem resolved?

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Unable to start postgresql

[Tom Lane]

John Iliffe  writes:
> When the programme exited it left another postmaster.pid file so I deleted 
> that one too.

You haven't shown us the program actually exiting, and basically the only
way to get the postmaster to exit without removing its pid file is to
kill -9 it.  Now I am suspicious that you in fact haven't killed any
postmasters, but only removed their pidfiles out from under them, which is
an incredibly dangerous thing to do.  Check "ps ax" output to see if any
postgres processes are lurking in background.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] import CSV file to a table

[John McKown]

On Wed, Mar 8, 2017 at 8:45 AM, vinny  wrote:

> On 2017-03-08 10:13, Günce Kaya wrote:
>
>> Hi all,
>>
>> I want to import content of CSV file to a table via bash script
>> without creating temporary table and I also want to skip some columns
>> in CSV file (for instance, CSV file has 12 column and main table has
>> only 2 column, If possible I would use only 2 column in CSV file) Is
>> there any way to do it?
>>
>> Regards,
>>
>> --
>>
>> Gunce Kaya
>>
>
> This is more a programming question than a database question, and there
> are many possible solutions.
> Do *not*, whatever you do, try to write your own piece of code to read the
> CSV. There are lots of unexpected
> ways that the CSV file can be slightly different from what you expect, and
> figuring all those out is a waste of time.
> The example of embedded comma's is just one way, there could also be
> newlines, linebreaks, utf8-escape characters etc.
>
> Personally I'd go the python route because it's simple and
> straightforward, but anything you are comfortable with will do.
> If you are going to install additional software to do this then remember
> that you'll need that same software again if
> you need to do this again, or when you need to move this code to a
> different server.


​I agree. I went with a "pure BASH" approach because it is what the user
asked for & I wasn't sure what language she might be comfortable with. I
use PERL a lot. Or maybe I should say that I abuse PERL a lot. Such as a
PERL script with writes out another PERL script, based on some input files
& parameters, then runs the just written PERL script, which does the load
into a PostgreSQL database (multiple tables). Ya, a bit perverted.​

-- 
"Irrigation of the land with seawater desalinated by fusion power is
ancient. It's called 'rain'." -- Michael McClary, in alt.fusion

Maranatha! <><
John McKown

Re: [GENERAL] Unable to start postgresql

[John Iliffe]

Yes, that file was present, so I deleted it.  Attempted to restart and got 
the same result:

LOG:  could not bind IPv4 socket: Cannot assign requested address
HINT:  Is another postmaster already running on port 5432? If not, wait a 
few seconds and retry.
LOG:  database system was interrupted; last known up at 2017-03-07 22:48:24 
EST
LOG:  database system was not properly shut down; automatic recovery in 
progress
LOG:  invalid record length at 0/15610C8: wanted 24, got 0
LOG:  redo is not required
LOG:  MultiXact member wraparound protections are now enabled
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

When the programme exited it left another postmaster.pid file so I deleted 
that one too.

John
===
On Tuesday 07 March 2017 23:26:40 Melvin Davidson wrote:
> On Tue, Mar 7, 2017 at 11:17 PM, John Iliffe  wrote:
> > I was unable to get postgres started so I did a very basic
> > compile/install to test it.  The configuration line was:
> > 
> > ./configure --prefix=/usr/postgres-9.6.2
> > 
> > 
> > Then I ran make which completed as expected:
> > 
> > make[1]: Leaving directory '/tmp/postgresql-9.6.2/config'
> > All of PostgreSQL successfully made. Ready to install.
> > 
> > --
> > Then make check
> > 
> > make check
> > 
> > ===
> > 
> >  All 167 tests passed.
> > 
> > ===
> > 
> > make[1]: Leaving directory '/tmp/postgresql-9.6.2/src/test/regress'
> > 
> > 
> > Then make install as user root because of directory permissions.  I
> > have the expected files in /usr/postgres_9.6.2
> > 
> > Now, running as user postgres I try and start as stated in the manual
> > 
> > postgres -D /usr/pgsql_tablespaces
> > 
> > initdb has already been run and the directory pgsql_tablespaces has a
> > number of files as expected.  As yet no database has been defined
> > because psql won't start.
> > 
> > The result is:
> > [postgres@prod04 postgresql-9.6.2]$ postgres -D /usr/pgsql_tablespaces
> > LOG:  could not bind IPv4 socket: Cannot assign requested address
> > HINT:  Is another postmaster already running on port 5432? If not,
> > wait a few seconds and retry.
> > LOG:  database system was shut down at 2017-03-07 22:22:57 EST
> > LOG:  MultiXact member wraparound protections are now enabled
> > LOG:  database system is ready to accept connections
> > LOG:  autovacuum launcher started
> > 
> > Same results if I use pg_ctl to start the process.
> > 
> > I checked with lsof and there is no process bound to socket 5432. 
> > There is no entry in /var/run for a socket related to postgresql.
> > 
> > I thought it might be a security issue so I put SELinux in permissive
> > mode but the result is the same.  The SELinux journal does not show
> > any warnings on this process.
> > 
> > [root@prod04 postgresql-9.6.2]# sestatus
> > SELinux status: enabled
> > SELinuxfs mount:/sys/fs/selinux
> > SELinux root directory: /etc/selinux
> > Loaded policy name: targeted
> > Current mode:   permissive  <--permissive mode**
> > Mode from config file:  permissive
> > Policy MLS status:  enabled
> > Policy deny_unknown status: allowed
> > Max kernel policy version:  30
> > -
> > 
> > One thing that I haven't been able to find any the log files.  Where
> > are they
> > normally stored?
> > 
> > So, any ideas as to where to go next to debug this would be
> > appreciated! This is a brand new server that I am trying to configure
> > so I have a fair amount of security clearance to chase things.
> > 
> > Thanks in advance.
> > 
> > John
> > =
> > 
> > 
> > 
> > 
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> 
> This is very suspicicious.
> 
> >LOG:  could not bind IPv4 socket: Cannot assign requested address
> >HINT:  Is another postmaster already running on port 5432? If not, wait
> >a
> 
> f>ew seconds and retry.
> 
> So check to see if the file "postmaster.pid" exists.
> If it does, and postgres is NOT running, just delet or rename it and try
> to start.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] import CSV file to a table

[vinny]

On 2017-03-08 10:13, Günce Kaya wrote:

Hi all,

I want to import content of CSV file to a table via bash script
without creating temporary table and I also want to skip some columns
in CSV file (for instance, CSV file has 12 column and main table has
only 2 column, If possible I would use only 2 column in CSV file) Is
there any way to do it?

Regards,

--

Gunce Kaya


This is more a programming question than a database question, and there 
are many possible solutions.
Do *not*, whatever you do, try to write your own piece of code to read 
the CSV. There are lots of unexpected
ways that the CSV file can be slightly different from what you expect, 
and figuring all those out is a waste of time.
The example of embedded comma's is just one way, there could also be 
newlines, linebreaks, utf8-escape characters etc.


Personally I'd go the python route because it's simple and 
straightforward, but anything you are comfortable with will do.
If you are going to install additional software to do this then remember 
that you'll need that same software again if
you need to do this again, or when you need to move this code to a 
different server.



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] import CSV file to a table

[Martijn Tonies (Upscene Productions)]

Hi,

If this is a one-time thing, you can use the Import Data tool in Database 
Workbench, see
http://www.upscene.com/documentation/dbw5/tools_dataimport.htm

Hope this helps.

With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Database Workbench - developer tool for Oracle, MS SQL Server, PostgreSQL,
SQL Anywhere, MySQL, InterBase, NexusDB and Firebird.

From: Günce Kaya 
Sent: Wednesday, March 08, 2017 10:13 AM
To: pgsql-general@postgresql.org 
Subject: [GENERAL] import CSV file to a table

Hi all, 

I want to import content of CSV file to a table via bash script without 
creating temporary table and I also want to skip some columns in CSV file (for 
instance, CSV file has 12 column and main table has only 2 column, If possible 
I would use only 2 column in CSV file) Is there any way to do it? 

Regards,


-- 

Gunce Kaya

Re: [GENERAL] import CSV file to a table

[John McKown]

On Wed, Mar 8, 2017 at 3:13 AM, Günce Kaya  wrote:

> Hi all,
>
> I want to import content of CSV file to a table via bash script without
> creating temporary table and I also want to skip some columns in CSV file
> (for instance, CSV file has 12 column and main table has only 2 column, If
> possible I would use only 2 column in CSV file) Is there any way to do it?
>
> Regards,
>
> --
> Gunce Kaya
>

​Not too difficult, but "unusual". However, there is a restriction that the
data cannot have an embedded comma. That is, you can't have something like:
"a,b",c and want two columns with a,b and c as the values.

[tsh009@it-johnmckown-linux junk]$ cat ptest.csv
a,b,c,d,e,f,g,h,i
1,2,3,4,5,6,7,8,9
z,y,x,w,v,u,t,s,r,q

[tsh009@it-johnmckown-linux junk]$ cat ptest.sh
#!/bin/bash
printf "%s\n" 'COPY schema1.table1(column1from5, column2from7) FROM stdin;'
export IFS=','
while read i;do # read in the records until EOF
test -n "${i}" && { # ignore blank lines!
set ${i} # set shell variables $1, $2, ...
printf "%s\t%s\n" $5 $7 #write out columns 5 & 7
}
done
printf "%s\n" '\.' # write EOF delimiter for COPY

[tsh009@it-johnmckown-linux junk]$ ./ptest.sh <
John McKown

Re: [GENERAL] import CSV file to a table

[Vick Khera]

Since you're using bash, I will assume you are not averse to using a
slightly complicated pipeline. First, install this:
https://github.com/wireservice/csvkit

Then use that to cut out the columns, you want and pipe the result into
psql with an appropriate \copy command.

On Wed, Mar 8, 2017 at 4:13 AM, Günce Kaya  wrote:

> Hi all,
>
> I want to import content of CSV file to a table via bash script without
> creating temporary table and I also want to skip some columns in CSV file
> (for instance, CSV file has 12 column and main table has only 2 column, If
> possible I would use only 2 column in CSV file) Is there any way to do it?
>
> Regards,
>
> --
> Gunce Kaya
>

Re: [GENERAL] Request to confirm which command is use for exclusive operation

[Yogesh Sharma]

Dear Albe,

Thanks for your support.

I observed there is some problem in REINDEX operation in older PostgreSQL
versions.
That why i want to add explicitly lock.

Regards,
Yogesh

On Wednesday, March 8, 2017, Albe Laurenz  wrote:

> Yogesh Sharma wrote:
> >  I want to apply explicitly lock mechanism once inset operation is in
> progress then REINDEX will wait.
> > And vice versa.
> > So, please let me know this type of handling is possible.
>
> Maybe I misunderstand something, but you don't need to do that because it
> happens automatically.
>
> If you run REINDEX, it will take an ACCESS EXCLUSIVE lock on the index
> before it starts its work.
>
> That means that it has to wait until all earlier INSERTing transactions are
> ended, and all INSERTs that come after the REINDEX will have to wait until
> the REINDEX is done.
>
> The database takes care that the data are consistent, so why would you
> want to do that explicitly?
>
> Yours,
> Laurenz Albe
>

[GENERAL] too may LWLocks

[hariprasath nallasamy]

Hi all
I am building an extension using shared memory hash table and for locking
hash table i am using LWLocks, but the thing was when i try to run some 1k
queries one after other, for each query i am getting one LWLock but on
executing 200th query i am getting the error *ERROR:  too many LWLocks
taken*.

But in each query i acquire and release that block. So that lock has to be
flushed after executing query, but why am i getting this error.?

Is this due to *held_lwlocks *in LWLock.c is fixed only to some number 200
here.
Or am i missing something here.?


thanks
harry

Re: [GENERAL] Postgres, apps, special characters and UTF-8 encoding

[Albe Laurenz]

Ken Tanzer wrote:
> Hi.  I've got a recurring problem with character encoding for a 
> Postgres-based web PHP app, and am
> hoping someone can clue me in or at least point me in the right direction.  
> I'll confess upfront my
> understanding of encoding issues is extremely limited.  Here goes.
> 
> The app uses a Postgres database, UTF-8 encoded.  Through their browsers, 
> users can add and edit
> records often including text.  Most of the time this works fine.  Though 
> sometimes this will fail with
> Postgres complaining, for example, "Could query with ... , The error text 
> was: ERROR: invalid byte
> sequence for encoding "UTF8": 0xe9 0x20 0x67"
> 
> So this generally happens when people copy and paste things out of their word 
> documents and such.
> 
> As I understand it, those are likely encoded in something non-UTF-8, like 
> WIN-1251 or something.  And
> that one way or another, the encoding needs to be translated before it can be 
> placed into the
> database.  I'm not clear how this is supposed to happen though.  
> Automatically by the browser?  Done
> in the app?  Some other way?  And if in the app, how is one supposed to know 
> what the incoming
> encoding is?
> 
> Thanks in advance for any help or pointers.

The byte sequence 0xe9 0x20 0x67 means "é g" in ISO-8859-1 and WINDOWS-1252,
so I think that your setup is as follows:

- The PHP application gets data encoded in ISO-8859-1 or WINDOWS-1252
  and tries to store it in a database.
- The PHP application has a database connection with client_encoding
  set to UTF8.

Then the database thinks it gets UTF-8 and will choke if it gets something
different.

The solution:

- Make sure that your web application gets data in only one encoding.
- Set client_encoding to that encoding.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Request to confirm which command is use for exclusive operation

[Albe Laurenz]

Yogesh Sharma wrote:
>  I want to apply explicitly lock mechanism once inset operation is in 
> progress then REINDEX will wait.
> And vice versa.
> So, please let me know this type of handling is possible.

Maybe I misunderstand something, but you don't need to do that because it
happens automatically.

If you run REINDEX, it will take an ACCESS EXCLUSIVE lock on the index
before it starts its work.

That means that it has to wait until all earlier INSERTing transactions are
ended, and all INSERTs that come after the REINDEX will have to wait until
the REINDEX is done.

The database takes care that the data are consistent, so why would you
want to do that explicitly?

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] import CSV file to a table

[Günce Kaya]

Hi all,

I want to import content of CSV file to a table via bash script without
creating temporary table and I also want to skip some columns in CSV file
(for instance, CSV file has 12 column and main table has only 2 column, If
possible I would use only 2 column in CSV file) Is there any way to do it?

Regards,

-- 
Gunce Kaya

Re: [GENERAL] Request to confirm which command is use for exclusive operation

[Yogesh Sharma]

Dear Scott,

How to handle table with token?
How to implement this?

Regards,
Yogesh

On Wednesday, March 8, 2017, Scott Marlowe  wrote:

> On Tue, Mar 7, 2017 at 11:55 PM, Scott Marlowe  > wrote:
> > On Tue, Mar 7, 2017 at 11:21 PM, Yogesh Sharma  > wrote:
> >> Dear David,
> >>
> >>  I want to apply explicitly lock mechanism once inset operation is in
> >> progress then REINDEX will wait.
> >> And vice versa.
> >> So, please let me know this type of handling is possible.
> >> Regrds,
> >> Yogesh
> >
> > Create two roles grant / revoke permissions as needed. maybe
> > pg_stat_activity for locks etc
>
> Also you could have a table with a simple token in it etc. active
> process gets token, all other processes wait on it.
>